SQL批量数据清洗核心是用集合操作替代逐行处理，通过内置函数与条件逻辑一次性修正脏数据，关键在准确定位问题类型、匹配SQL能力边界并验证效果。
SQL批量数据清洗核心在于用集合操作代替逐行处理，靠内置函数和条件逻辑一次性修正大量脏数据。关键不是写得多，而是想得准——先定位问题类型，再匹配SQL能力边界，最后验证效果。
识别常见脏数据模式
批量清洗前必须明确要处理什么。典型问题包括：
- 空值与占位符混用：如 NULL、'NULL'、''、'N/A'、'未知' 都表示缺失，但数据库当不同值对待
- 格式不统一：手机号带空格/横线/括号（'138 1234 5678'、'138-1234-5678'）、日期存为字符串（'20230101'、'01/01/2023'）
- 逻辑错误：年龄为负数、注册时间晚于订单时间、状态字段出现不存在的值（如 status='pending_pay' 但枚举只支持 'paid'/'cancelled'）
- 重复与冗余：完全重复行、主键不唯一、同一客户多条记录仅联系方式不同
用UPDATE+CASE批量标准化字段
这是最常用也最高效的清洗手段，尤其适合格式转换、空值归一、状态映射等场景。
例如统一手机号：去掉所有非数字字符，补足11位（假设国内）
UPDATE users
SET phone = REPLACE(REPLACE(REPLACE(REPLACE(phone, ' ', ''), '-', ''), '(', ''), ')', '')
WHERE phone REGEXP '[^0-9]';再统一空值表达：
UPDATE users
SET gender = CASE
WHEN gender IN ('', 'NULL', 'N/A', '未知') THEN NULL
WHEN gender IN ('男', 'M', 'm', '1') THEN 'M'
WHEN gender IN ('女', 'F', 'f', '0') THEN 'F'
ELSE 'O'
END;⚠️ 注意：执行前务必备份或在事务中测试（BEGIN; ... ROLLBACK;）
用CTE+窗口函数处理重复与逻辑冲突
对“同一客户多条记录”或“时间倒置”类问题，单靠UPDATE不够，需先识别再决策。
示例：保留每个user_id最新一条订单，删除其余
WITH ranked_orders AS (
SELECT id, user_id, created_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn
FROM orders
)
DELETE FROM orders
WHERE id IN (
SELECT id FROM ranked_orders WHERE rn > 1
);又如修复注册时间晚于首单时间的问题（取两者较小值作为注册时间）：
UPDATE users u JOIN ( SELECT user_id, MIN(order_time) AS first_order FROM orders GROUP BY user_id ) o ON u.id = o.user_id SET u.register_time = LEAST(u.register_time, o.first_order) WHERE u.register_time > o.first_order;
清洗后必须做的三件事
- 抽样验证：SELECT * FROM table WHERE [清洗字段] IS NULL OR [清洗字段] REGEXP '^[a-zA-Z]' LIMIT 10；看是否还有漏网之鱼
- 统计对比：清洗前后分别执行 COUNT(*), COUNT(字段), COUNT(DISTINCT 字段)，确认数量变化合理（如空值数下降、去重数稳定）
- 加约束兜底：清洗完成后，立刻添加 CHECK、NOT NULL、ENUM 或触发器，防止脏数据再次流入
基本上就这些。SQL清洗不靠炫技，而靠对数据问题的准确判断 + 对函数边界的清晰认知。练熟几类典型模式，比死记一百个函数更有用。
