17370845950

SQL批量数据清洗需遵循“识别问题→设计规则→分步修正→验证结果”四步闭环，通过CTE+CASE构建可验证流水线，先备份再更新，确保千万级表ETL安全高效。

SQL批量数据清洗不是靠一条命令搞定，而是通过“识别问题→设计规则→分步修正→验证结果”四步闭环完成。核心在于用可复用的SQL逻辑替代人工逐条处理，尤其适合千万级表的日常ETL维护。

一、快速定位脏数据类型（先看再动）

不查清问题就写UPDATE，等于蒙眼修车。常用诊断SQL要常备：

• 空值/异常空串：SELECT COUNT(*) FROM orders WHERE phone IS NULL OR TRIM(phone) = '';
• 格式错乱（如手机号含字母、日期非法）：SELECT * FROM users WHERE phone REGEXP '[^0-9]' OR LENGTH(phone) != 11;
• 重复主键或业务键：SELECT user_id, COUNT(*) FROM users GROUP BY user_id HAVING COUNT(*) > 1;
• 数值越界或负值异常：SELECT * FROM sales WHERE amount 1000000;

二、分层清洗：用CTE+CASE构建安全流水线

避免直接UPDATE原表，推荐用WITH递进式清洗，每步可单独验证：

WITH clean_phone AS (
  SELECT id,
         CASE 
           WHEN phone REGEXP '^[0-9]{11}$' THEN phone
           WHEN phone REGEXP '^[0-9]{12}$' AND SUBSTR(phone,1,1)='8' THEN SUBSTR(phone,2) -- 去掉开头8
           WHEN phone LIKE '%-%' THEN REPLACE(phone, '-', '') -- 去横线
           ELSE NULL 
         END AS cleaned_phone
  FROM raw_users
),
deduped AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY cleaned_phone ORDER BY updated_at DESC) AS rn
  FROM clean_phone
)
SELECT id, cleaned_phone AS phone
FROM deduped 
WHERE rn = 1 AND cleaned_phone IS NOT NULL;

这样写的好处：逻辑清晰、中间结果可查、出错可回退到任意CTE层调试。

三、批量更新与去重落地（谨慎执行）

确认清洗逻辑无误后，再操作原表。关键原则：先备份，再更新，后校验：

• 备份（必做）：CREATE TABLE users_bak_20250520 AS SELECT * FROM users;
• 更新手机号：UPDATE users u JOIN (SELECT id, cleaned_phone FROM ... ) c ON u.id=c.id SET u.phone = c.cleaned_phone WHERE c.cleaned_phone IS NOT NULL;
• 删除重复记录（保留最新）：DELETE u1 FROM users u1 INNER JOIN users u2 WHERE u1.phone = u2.phone AND u1.updated_at

四、自动化与防复发（让清洗一次生效）

清洗不能只做一次。加两道防线：

• 建清洗视图：CREATE VIEW v_users_clean AS SELECT ..., COALESCE(TRIM(phone), '') AS phone FROM users WHERE status != 'deleted'; 后续报表/接口全走视图，原表只存原始数据。
• 加约束和触发器：ALTER TABLE users ADD CHECK (phone REGEXP '^[0-9]{11}$' OR phone IS NULL); 或用BEFORE INSERT触发器自动标准化。
• 定期巡检脚本：把第一步的诊断SQL写成每日定时任务，结果发钉钉/邮件，异常值超阈值自动告警。

基本上就这些。真正难的不是写SQL，而是把业务规则翻译成可计算的条件——比如“有效手机号”在你们系统里到底指什么？是运营商号段？还是必须能发短信？定义清楚，清洗才不会反复返工。