4、数据质量审计：空值率、重复率、一致性检查、异常值检测

数据迁移最怕什么？我最怕的是——数据搬过去了，系统跑不起来。

为什么？因为源系统的数据质量，往往比你想象的更糟糕。我在一个制造企业的ERP迁移项目中，就遇到过这样的情况：客户拍着胸脯说“我们的数据很干净”，结果一跑审计脚本，空值率超过40%的字段有十几个。嗯，后来那个项目光数据清洗就多花了两周。

所以，在正式迁移之前，我们必须做一次全面的数据质量审计。说白了，就是给源数据做一次“体检”。

这一章，我带你写一套SQL审计脚本。它能帮你快速摸清数据的底细：空值多不多、重复严不严重、字段之间有没有矛盾、有没有离谱的异常值。

4.1 空值率检查：别让NULL坑了你

空值率，是数据质量最基础的指标。一个字段如果空值率太高，迁移过去要么没法用，要么需要做默认值映射。

我个人习惯，先对核心业务表做全字段的空值率扫描。脚本长这样：

-- 空值率审计脚本（通用版）
SELECT
    'CUSTOMER' AS table_name,
    'CUST_NAME' AS column_name,
    COUNT(*) AS total_rows,
    SUM(CASE WHEN CUST_NAME IS NULL OR TRIM(CUST_NAME) = '' THEN 1 ELSE 0 END) AS null_count,
    ROUND(
        SUM(CASE WHEN CUST_NAME IS NULL OR TRIM(CUST_NAME) = '' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2
    ) AS null_rate_pct
FROM CUSTOMER

UNION ALL

SELECT
    'CUSTOMER',
    'TAX_ID',
    COUNT(*),
    SUM(CASE WHEN TAX_ID IS NULL OR TRIM(TAX_ID) = '' THEN 1 ELSE 0 END),
    ROUND(
        SUM(CASE WHEN TAX_ID IS NULL OR TRIM(TAX_ID) = '' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2
    )
FROM CUSTOMER

-- 可以继续追加其他字段...
ORDER BY null_rate_pct DESC;
我的经验：空值率超过30%的字段，要特别警惕。我曾经在一个客户的主数据表里，发现“联系人手机号”字段空值率高达65%。后来一查，是业务系统里根本没强制录入。这种字段迁移后，要么补录，要么在目标系统里设为非必填。

如果你有几十个字段要检查，一个个写UNION ALL太累了。可以用动态SQL或者系统视图来批量生成。比如在Oracle里，可以查ALL_TAB_COLUMNS：

-- Oracle 批量生成空值率检查SQL（动态拼接思路）
SELECT
    'SELECT ''' || TABLE_NAME || ''' AS table_name, ''' || COLUMN_NAME || ''' AS column_name, ' ||
    'COUNT(*) AS total_rows, ' ||
    'SUM(CASE WHEN ' || COLUMN_NAME || ' IS NULL OR TRIM(' || COLUMN_NAME || ') = '''' THEN 1 ELSE 0 END) AS null_count, ' ||
    'ROUND(SUM(CASE WHEN ' || COLUMN_NAME || ' IS NULL OR TRIM(' || COLUMN_NAME || ') = '''' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS null_rate_pct ' ||
    'FROM ' || TABLE_NAME || ';' AS audit_sql
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'CUSTOMER'
  AND DATA_TYPE IN ('VARCHAR2', 'CHAR', 'NVARCHAR2');

把生成的SQL复制出来跑一遍，结果一目了然。

4.2 重复率检查：别让数据“双胞胎”捣乱

重复数据，是ERP迁移的另一个大坑。我记得有一次，客户说“我们的客户表没有重复”，结果我跑了个简单的GROUP BY，发现同一个税号出现了3次。你想想看，这种数据迁移过去，财务模块对账能对吗？

重复率检查，我一般分两个维度：

  • 单字段重复率：比如税号、身份证号、订单号
  • 多字段组合重复率：比如“姓名+生日+地址”组合
-- 单字段重复率检查（以税号为例）
SELECT
    'TAX_ID' AS check_field,
    COUNT(*) AS total_rows,
    COUNT(DISTINCT TAX_ID) AS distinct_count,
    ROUND((COUNT(*) - COUNT(DISTINCT TAX_ID)) * 100.0 / COUNT(*), 2) AS duplicate_rate_pct
FROM CUSTOMER
WHERE TAX_ID IS NOT NULL;

-- 多字段组合重复检查（找出具体重复记录）
SELECT
    CUST_NAME,
    TAX_ID,
    CONTACT_PHONE,
    COUNT(*) AS dup_count
FROM CUSTOMER
GROUP BY CUST_NAME, TAX_ID, CONTACT_PHONE
HAVING COUNT(*) > 1
ORDER BY dup_count DESC;
注意：重复率检查一定要排除空值。我曾经犯过这个错——把NULL也当成一个“值”去统计，结果重复率虚高。实际上，多个NULL记录并不算真正的重复，它们只是都没填而已。

4.3 一致性检查：字段之间的“逻辑对账”

一致性检查，说白了就是看字段之间的关系是否合理。比如：

  • 订单的“下单日期”不能晚于“发货日期”
  • 客户的“注册日期”不能早于“出生日期”
  • 金额字段：明细金额之和应该等于总金额

我在一个零售ERP项目里，就发现过“发货日期比下单日期还早两天”的奇葩数据。后来查出来，是门店系统的时间戳没同步。这种数据不修，迁移过去报表全是错的。

-- 一致性检查：订单日期逻辑
SELECT
    ORDER_ID,
    ORDER_DATE,
    SHIP_DATE,
    CASE
        WHEN SHIP_DATE < ORDER_DATE THEN '异常：发货早于下单'
        WHEN SHIP_DATE = ORDER_DATE THEN '正常：当天发货'
        ELSE '正常'
    END AS date_logic_status
FROM SALES_ORDER
WHERE ORDER_DATE IS NOT NULL AND SHIP_DATE IS NOT NULL
  AND SHIP_DATE < ORDER_DATE;  -- 只查出异常记录

-- 一致性检查：金额汇总核对
SELECT
    O.ORDER_ID,
    O.TOTAL_AMOUNT,
    SUM(L.LINE_AMOUNT) AS sum_line_amount,
    CASE
        WHEN ABS(O.TOTAL_AMOUNT - SUM(L.LINE_AMOUNT)) > 0.01 THEN '不一致'
        ELSE '一致'
    END AS amount_match_status
FROM SALES_ORDER O
JOIN SALES_ORDER_LINE L ON O.ORDER_ID = L.ORDER_ID
GROUP BY O.ORDER_ID, O.TOTAL_AMOUNT
HAVING ABS(O.TOTAL_AMOUNT - SUM(L.LINE_AMOUNT)) > 0.01;
核心思路：一致性检查的本质是“业务规则校验”。你不需要检查所有字段组合，只检查那些有明确业务逻辑关系的字段。比如“折扣率不能大于100%”、“库存数量不能为负数”等。

4.4 异常值检测：揪出那些“离谱”的数据

异常值检测，我通常用两种方法：

  1. 基于业务规则：比如年龄不能超过150岁，单价不能为负数
  2. 基于统计方法：比如用四分位距（IQR）检测离群点

业务规则法简单直接，适合大多数场景。统计方法适合那些没有明确上下限的数值字段，比如“订单金额”。

-- 业务规则法：年龄异常
SELECT
    CUST_ID,
    CUST_NAME,
    BIRTH_DATE,
    FLOOR(MONTHS_BETWEEN(SYSDATE, BIRTH_DATE) / 12) AS age
FROM CUSTOMER
WHERE BIRTH_DATE IS NOT NULL
  AND (BIRTH_DATE > SYSDATE OR FLOOR(MONTHS_BETWEEN(SYSDATE, BIRTH_DATE) / 12) > 120);

-- 统计方法：用IQR检测订单金额异常值
WITH stats AS (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY TOTAL_AMOUNT) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY TOTAL_AMOUNT) AS q3
    FROM SALES_ORDER
    WHERE TOTAL_AMOUNT IS NOT NULL
)
SELECT
    O.ORDER_ID,
    O.TOTAL_AMOUNT,
    S.q1,
    S.q3,
    (S.q3 - S.q1) AS iqr,
    CASE
        WHEN O.TOTAL_AMOUNT < (S.q1 - 1.5 * (S.q3 - S.q1)) THEN '下界异常'
        WHEN O.TOTAL_AMOUNT > (S.q3 + 1.5 * (S.q3 - S.q1)) THEN '上界异常'
        ELSE '正常'
    END AS outlier_flag
FROM SALES_ORDER O, stats S
WHERE O.TOTAL_AMOUNT IS NOT NULL
  AND (O.TOTAL_AMOUNT < (S.q1 - 1.5 * (S.q3 - S.q1))
       OR O.TOTAL_AMOUNT > (S.q3 + 1.5 * (S.q3 - S.q1)));
避坑指南：我曾经用IQR检测一个电商系统的订单金额，结果发现大量“上界异常”。后来一分析，原来是双十一大促期间，订单金额确实比平时高很多。所以，异常值检测的结果一定要结合业务场景判断，不能一刀切。

4.5 审计结果汇总：一张表看清全局

最后，我建议把所有审计结果汇总到一张表里。这样汇报给项目组时，一目了然。

检查类型 表名 字段/规则 问题记录数 问题率 严重程度
空值率 CUSTOMER CONTACT_PHONE 1,250 65.2% 高
重复率 CUSTOMER TAX_ID 23 1.2% 中
一致性 SALES_ORDER 日期逻辑 15 0.8% 高
异常值 SALES_ORDER TOTAL_AMOUNT 87 4.5% 中

有了这张表，你就可以跟业务方说：“看，这些数据需要清洗，这些字段需要补录，这些规则需要确认。” 而不是等到迁移完了，系统跑出问题再回头查。

嗯，数据质量审计这一步，虽然枯燥，但绝对值得花时间。你想想看，如果源数据本身就有问题，再好的ERP系统也救不了。下一章，我会讲如何根据审计结果，制定数据清洗策略。到时候，这些脚本的结果就是你的“作战地图”。

← 上一章 📖 返回目录 下一章 →