先把数据质量说清楚

分析结果是否可信，通常不取决于 SQL 写得多复杂，而取决于我们有没有看见重复、空值和口径冲突。这一节先补齐订单明细，再把原始表整理成后面一直使用的清洗视图。

补齐订单明细

每笔订单生成 2 到 3 行商品明细。商品编号、数量、成交价和折扣都由订单编号确定，所以每次重建数据库都会得到相同结果。

sql

WITH RECURSIVE item_slot(slot) AS (
    SELECT 1
    UNION ALL
    SELECT slot + 1 FROM item_slot WHERE slot < 3
)
INSERT INTO order_items_raw
SELECT
    o.order_id,
    ((o.order_id * 7 + item_slot.slot * 5) % 12) + 1,
    CASE
        WHEN o.order_id = 257 AND item_slot.slot = 1 THEN NULL
        ELSE ((o.order_id + item_slot.slot) % 3) + 1
    END,
    ROUND(p.list_price * (1 + ((o.order_id % 5) - 2) * 0.01), 2),
    CASE
        WHEN o.order_id % 11 = 0 THEN 0.15
        WHEN o.order_id % 5 = 0 THEN 0.10
        WHEN o.order_id = 331 AND item_slot.slot = 2 THEN NULL
        ELSE 0.00
    END
FROM orders_raw AS o
CROSS JOIN item_slot
JOIN products AS p
  ON p.product_id = ((o.order_id * 7 + item_slot.slot * 5) % 12) + 1
WHERE o.rowid IN (
    SELECT MIN(rowid) FROM orders_raw GROUP BY order_id
)
AND item_slot.slot <= 2 + (o.order_id % 2);

这里先按 order_id 保留一条订单记录，再生成明细。如果不做这一步，重复的 113 号订单会让它的明细也重复，后面的销售额会被放大。

一次性盘点已知问题

先数问题，再决定怎样处理。下面的查询会返回重复订单、城市空格、空渠道、空数量和空折扣的数量。

sql

SELECT
    COUNT(*) AS order_rows,
    COUNT(DISTINCT order_id) AS distinct_orders,
    COUNT(*) - COUNT(DISTINCT order_id) AS duplicate_rows
FROM orders_raw;
 
SELECT
    SUM(city <> TRIM(city)) AS cities_with_spaces,
    SUM(channel IS NULL) AS missing_channels

检查项	数量
重复订单行	1
城市前后有空格	17
渠道为空	6
数量为空	1
折扣为空	1

空值不能一律填成 0。数量为空时，我们不知道买了几件，这行无法参与销售计算；折扣为空则可以按“没有额外折扣”处理为 0。处理方式来自字段含义，不是来自函数习惯。

建立客户清洗视图

视图不会复制数据，它保存的是一段可重复执行的查询。这样既保留原始记录，又能让后续分析统一使用清洗口径。

sql

CREATE VIEW v_customers_clean AS
SELECT
    customer_id,
    date(signup_date) AS signup_date,
    CASE TRIM(city)
        WHEN '北京市' THEN '北京'
        ELSE TRIM(city)
    END AS city,
    COALESCE(channel, '未归因') AS channel,
    member_level
FROM customers_raw;

这里做了三件事：TRIM 去掉空格，CASE 合并“北京”和“北京市”，COALESCE 把空渠道标记成“未归因”。我们没有把空渠道猜成某个现有渠道，因为源数据没有提供这种依据。

去掉重复订单

ROW_NUMBER 会在每个订单编号内部排序并编号。保留 duplicate_rank = 1，就得到每个订单的一条记录。

sql

CREATE VIEW v_orders_clean AS
WITH ranked AS (
    SELECT
        rowid AS source_rowid,
        *,
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY rowid
        ) AS duplicate_rank
    FROM orders_raw
)
SELECT
    order_id,
    customer_id,
    date(order_date) AS order_date,
    status

如果重复记录的内容不一致，不能直接按 rowid 取第一条，而要先定义“最新导入”“最后更新时间”或“可信来源”等规则。本项目的重复行完全相同，所以保留第一条即可。

清洗订单明细

sql

CREATE VIEW v_order_items_clean AS
SELECT
    order_id,
    product_id,
    quantity,
    unit_price,
    COALESCE(discount_rate, 0) AS discount_rate
FROM order_items_raw
WHERE quantity > 0
  AND unit_price > 0;

数量为空的记录不满足 quantity > 0，会被排除。成交价小于等于 0 也不进入销售分析，因为这类记录通常代表赠品、冲销或录入错误，应该单独定义口径。

验收清洗结果

sql

SELECT COUNT(*) AS clean_orders FROM v_orders_clean;
 
SELECT city, COUNT(*) AS customers
FROM v_customers_clean
GROUP BY city
ORDER BY customers DESC;
 
SELECT
    SUM(quantity IS NULL) AS missing_quantity,
    SUM(discount_rate IS NULL) AS missing_discount

清洗后应有 540 笔订单，城市只剩上海、北京、广州、深圳和杭州五种写法，明细中的数量与折扣不再为空。

先为每类问题写处理理由

数据清洗不是把“不好看”的值改漂亮。每一条规则都要说明字段含义、处理方式和对指标的影响。

问题	处理方式	为什么这样处理	对指标的影响
完全相同的重复订单	每个 `order_id` 保留第一条	两行没有业务差异	避免订单与销售额重复
城市前后空格	`TRIM`	空格不代表新城市	城市分组被合并
“北京市”与“北京”	统一为“北京”	两个值指同一地区	避免城市被拆成两组
渠道为空	标记为“未归因”	不猜测来源	保留客户，不虚构渠道
数量为空	排除该明细	无法计算销售额和成本	销售明细少一行
折扣为空	填 0	本项目约定为空表示无额外折扣	该行仍可计算销售额

数量为空和折扣为空看起来都是 NULL，处理却不同。决定规则的是字段的业务含义，不是我们喜欢用哪个函数。

正式项目应把这张表扩展成数据质量日志，记录规则负责人、确认日期和影响行数。口径变化时，后续分析才知道为什么数字发生了变化。

拆开理解 ROW_NUMBER

去重视图里的窗口函数可以分成三部分：

sql

SELECT
    order_id,
    ROW_NUMBER() OVER (
        PARTITION BY order_id
        ORDER BY rowid
    ) AS duplicate_rank
FROM orders_raw
ORDER BY order_id
LIMIT 10;

PARTITION BY order_id：每个订单编号单独排队。
ORDER BY rowid：同一编号内按导入顺序排序。
ROW_NUMBER()：依次标记为 1、2、3。

先只看重复的 113 号订单：

sql

SELECT
    rowid,
    order_id,
    order_date,
    status,
    ROW_NUMBER() OVER (
        PARTITION BY order_id
        ORDER BY rowid
    ) AS duplicate_rank
FROM orders_raw
WHERE order_id = 113;

你应该看到两行，排名分别是 1 和 2。清洗视图保留排名 1，不是因为第一条天然更可信，而是因为本项目两条内容完全相同。

如果重复行不一致，先用下面的查询找出冲突字段：

sql

SELECT
    order_id,
    COUNT(DISTINCT customer_id) AS customer_versions,
    COUNT(DISTINCT order_date) AS date_versions,
    COUNT(DISTINCT status) AS status_versions
FROM orders_raw
GROUP BY order_id
HAVING COUNT(*) > 1;

任何版本数大于 1，都说明不能直接按 rowid 去重。此时需要更新时间、来源优先级或业务确认。

视图为什么比覆盖原表更合适

CREATE VIEW 保存查询定义，不复制数据。后续查询每次访问视图，都会按同一规则读取原始表。

这带来两个好处。第一，原始表仍能追溯；第二，清洗规则只有一份。若在每个分析查询里各写一遍 TRIM、COALESCE 和去重条件，很容易有一处遗漏。

视图也有边界。原始数据量很大时，复杂视图每次重新计算可能变慢，生产环境可以使用物化表或增量清洗。本项目数据不大，视图更适合学习和复核。

做一张清洗前后对账表

把关键数量放在同一行，比散着运行查询更容易检查：

sql

SELECT
    (SELECT COUNT(*) FROM orders_raw) AS raw_order_rows,
    (SELECT COUNT(*) FROM v_orders_clean) AS clean_orders,
    (SELECT COUNT(*) FROM order_items_raw) AS raw_item_rows,
    (SELECT COUNT(*) FROM v_order_items_clean) AS clean_item_rows,
    (SELECT

订单从 541 行变为 540 笔，是去掉一条重复记录。客户仍是 120 位，说明城市和渠道清洗没有丢客户。明细少一行，是因为空数量无法参与销售计算。

对账时要能解释每一个差异。如果清洗后突然少了几十笔订单，先检查日期过滤、状态过滤和去重条件，不要继续往下计算指标。

验证类别是否真的统一

只看行数还不够。类别字段要检查不同值和频次：

sql

SELECT city, COUNT(*) AS customers
FROM v_customers_clean
GROUP BY city
ORDER BY customers DESC;

结果应只有上海、北京、广州、深圳和杭州。再验证“未归因”客户数：

sql

SELECT channel, COUNT(*) AS customers
FROM v_customers_clean
GROUP BY channel
ORDER BY customers DESC;

“未归因”应有 6 位客户。这个类别必须保留在报表中，否则渠道客户数合计会小于总客户数。

下一节开始查数据时，我们只使用这些清洗视图，不再直接碰原始表。原始表仍然保留，用于解释清洗前后的差异。

先把数据质量说清楚 | 自在学