前段时间接手了一个活儿客户丢过来一张3万多行的会员表要求先做数据清洗再跑后续分析。表里什么脏数据都有——手机号有带86的、带横杠的邮箱大小写乱成一团同一个人注册了两三次还有几十条记录姓名和电话完全对不上。我第一反应不是打开Python写脚本而是直接开了个SQL编辑器。有人可能觉得数据清洗应该用Pandas这类工具更顺手但在这个场景下SQL反而是效率最高、最不容易出错的选择。这篇文章就把这次清洗过程中用到的数据标准化手法和去重方案完整拆一遍内容包括字段清洗的详细SQL写法、五种去重思路的演进逻辑、一个完整的实战案例以及我在过程中踩过的坑。1. 为什么数据清洗这活儿我坚持用SQL干1.1 一次数据质量事故给我的教训早些年我还是个拿到脏数据就先开Python的人。有一次要处理一份几千行的订单导出数据我在Jupyter里写了小一百行Pandas代码处理完正要跑模型老板突然说数据源表又加了五千行你重跑一下。我当场愣住——因为那份Pandas脚本依赖我在本地手工修改过的一份中间文件重新导入后格式略微变化脚本直接报错。折腾了半小时最后还是一股脑把数据导回数据库写了几条SQL重新清洗。那一次的教训是只要数据还在库里清洗逻辑就应该尽量留在库里。SQL自带集合思维一条UPDATE能改一万行一条DELETE能删一万条重复整个过程可重复、可审计、可回溯。而脚本方式从导出、修改、再导回每一步都有数据损坏或丢失的风险。1.2 SQL清洗的边界什么时候该上Python什么时候SQL更合适也不是说SQL能包办所有清洗。我自己有个经验法则数据量在百万行以内、清洗规则以字段加工为主优先用SQL快且稳。需要模糊匹配、字符串相似度计算比如地址是否重复用SQL会比较痛苦这类逻辑适合Python或专门的数据质量工具。清洗规则需要频繁交互式探索、可视化验证脚本更灵活。清洗规则要固化、要重复跑比如每天清洗增量数据SQL优势非常明显存成存储过程或视图就能持续复用。市面上很多语言写的数据清洗代码放到生产环境最大的问题就是没人敢改逻辑不透明。但SQL的每个操作都直观可见业务方也能看懂一部分这在实际交付中太重要了。下面进入正题先讲字段标准化。2. 字段级标准化先把长得不一样的数据变成长得一样2.1 空格和大小写最容易忽略的隐形脏数据很多重复数据查不出来的根本原因不是数值不一样而是**看起来一样实际编码不一样**。最常见的就是空格。我建议清洗的第一步永远是对所有字符型字段统一去除首尾空格、压缩连续空格、统一大小写。具体写法如下-- 去除首尾空格并把中间多个连续空格压缩成一个 UPDATE customer SET customer_name REGEXP_REPLACE(TRIM(customer_name), {2,}, ); -- 邮箱统一小写 UPDATE customer SET email LOWER(TRIM(email)); -- 姓名类字段统一首字母大写PostgreSQL风格示例 UPDATE customer SET customer_name INITCAP(TRIM(customer_name));注意不同数据库的函数名差异MySQL用TRIM()、REPLACE()PostgreSQL有INITCAP()和REGEXP_REPLACE()SQL Server 可以用LTRIM()、RTRIM()加嵌套REPLACE()处理多空格。为了兼容我在生产环境经常用这种写法清理字段-- SQL Server / 通用写法先把全角空格替换为普通空格再处理连续空格 UPDATE customer SET customer_name LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(customer_name, , ), , ), , )));提示全角空格是很多系统导出的特产肉眼看不出来但WHERE customer_name 张三就是匹配不到。排查时可以用十六进制查看SELECT HEX(customer_name) FROM customer LIMIT 5;全角空格的十六进制是E38080正常空格是20。大小写问题在邮箱、用户名、城市名这类字段上尤其致命。同一个ZhangSanExample.com和zhangsanexample.com在业务上就是同一个人但数据库默认排序规则往往把大小写视为不同值。所以清洗规则里小写化通常是必做操作。2.2 日期和数值格式统一是硬需求日期字段的混乱程度远超想象。我见过同一张表里同时存在2024-01-15、2024/1/15、15-JAN-24、20240115四种格式。建议所有日期统一成YYYY-MM-DD字符串或标准DATE类型。-- MySQL把各种格式统一成 DATE 类型 UPDATE orders SET order_date STR_TO_DATE(order_date, %Y-%m-%d) WHERE order_date REGEXP ^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}; -- SQL Server字符串日期转标准格式 UPDATE orders SET order_date CONVERT(DATE, order_date, 23); -- 23 表示 yyyy-mm-dd -- PostgreSQL直接用类型转换 UPDATE orders SET order_date order_date::DATE;我踩过的坑是日期清洗一定要先用探查SQL看全所有的奇怪格式再写UPDATE规则不要想当然。比如你可能以为只有横杠和斜杠两种结果数据里有Excel导出的2024.1.15、有文本类型的15-Jan-2024、还有几位数44444Excel序列日期。所以正确流程是用SELECT DISTINCT order_date FROM orders ORDER BY order_date;看全部值。按模式分组哪些是纯数字、哪些带横杠、哪些带斜杠、哪些带月份缩写。对每一种模式写对应的转换规则分批UPDATE。数值字段的标准化重点是去除千分位逗号、货币符号、多余小数点。比如12,345.00要变成12345¥3,200要变成3200。通用思路是把非数字字符剔除-- MySQL剔除所有非数字字符 UPDATE orders SET amount CAST(REGEXP_REPLACE(amount, [^0-9.], ) AS DECIMAL(12,2));不过这种暴力剔除有风险——如果数值里有负号、百分号或科学计数法规则需要更精细。所以我建议在更新前先查一遍异常值例如SELECT amount FROM orders WHERE amount REGEXP [A-Za-z%] -- 含字母或百分号 OR amount NOT REGEXP ^[0-9.]$; -- 含其他非数字符号2.3 编码与同义值映射乱码和别名怎么治中文数据里另一个常见问题就是编码混乱。有些老系统导出的是GBK导入到UTF-8的库后就变成锟斤拷或之类。这类问题在SQL里可以用转换函数处理比如MySQL的CONVERT(column USING utf8mb4)但更稳妥的做法是确认源头编码重新导入。SQL在这里能做的是事后发现和拦截-- 找出疑似乱码的记录 SELECT customer_name FROM customer WHERE customer_name REGEXP 锟||Ã|æ OR customer_name REGEXP [\\x00-\\x1F]; -- 控制字符另一种看起来不同、实际上是同一个人的情况是同义值比如省份写着北京市和北京、性别写着男和M和1、状态写着已付款支付成功PAID。这属于典型的需要建映射表的场景。我在项目里通常维护一张dictionary_mapping表或者直接在UPDATE里用CASE WHEN映射UPDATE orders SET order_status CASE order_status WHEN 已付款 THEN paid WHEN 支付成功 THEN paid WHEN PAID THEN paid WHEN 待付款 THEN pending WHEN 未支付 THEN pending ELSE unknown END;用映射表的好处是业务方后续要加新的同义值时不用改代码直接插一条映射记录即可。如果你的清洗是临时性的CASE WHEN完全够用。2.4 NULL与默认值清洗数据的最后一公里标准化还包括NULL的处理。很多去重和统计结果被NULL坑惨就是因为统计函数不计算NULL、GROUP BY把NULL单独分一组、WHERE条件里NULL NULL永远不成立。我的建议是在清洗阶段明确每个字段的NULL语义。业务上不知道和不适用和还没填是三种不同的情况全部混成一个NULL会让后续分析崩溃。-- 手机号为空时用一个显式默认值占位方便后续去重 UPDATE customer SET phone COALESCE(NULLIF(TRIM(phone), ), UNKNOWN_PHONE) WHERE phone IS NULL OR TRIM(phone) ;但这里有个权衡如果只是临时分析把NULL替换成UNKNOWN_PHONE可能影响统计口径我常用的做法是保留原字段不动新增一个phone_clean清洗字段NULL仍然保留但在去重逻辑里用COALESCE处理。避免为了一时的去重把原始数据改得面目全非。3. 去重的五种写法从新手到专家的距离3.1 SELECT DISTINCT只适合看一眼的快速检查DISTINCT是最基础的去重方式。它好用但坑也不少。第一它会对所有选择的列做完全匹配去重只要有一列值不同比如注册时间不同就被当成两条不同记录第二它只输出去重后的结果不能直接告诉你重复的是哪些ID应该保留哪一条。-- 快速看有多少不同的手机号 SELECT COUNT(*), COUNT(DISTINCT phone) FROM customer;这个语句我几乎每次清洗都要跑一遍用来评估脏数据严重程度。但真正执行去重我会用下面几种更可控的方案。3.2 GROUP BY MIN/MAX保留下某一行的机智写法当你想按某个业务键比如手机号去重并且确定保留注册时间最早或金额最大的那一条时GROUP BY MIN/MAX很好用-- 每个手机号保留最新一条的ID SELECT MAX(id) AS keep_id FROM customer GROUP BY phone;然后再把其他行删掉或用JOIN筛选。但这种写法的局限很明显它只能保留聚合字段的最大/最小值不能按某个字段的优先级保留整条记录。比如你想保留手机号相同的人里姓名非空、更新时间最新、且地址完整的整行MIN/MAX就做不到了。3.3 ROW_NUMBER()窗口函数最可靠的去重方案这才是生产环境里最常用的去重方案。-- 按手机号分组组内按更新时间倒序编号编号为 1 的保留 WITH ranked AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY phone ORDER BY updated_at DESC ) AS rn FROM customer ) -- 查看重复情况 SELECT * FROM ranked WHERE rn 1;当rn 1就是每组保留的那条记录rn 1就是应该删除的重复记录。这个方案的好处是可以看到完整的重复记录内容确认该不该删。支持复杂的排序规则多字段优先级。配合DELETE或UPDATE都可以精确控制。实际删除的写法WITH ranked AS ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY phone ORDER BY updated_at DESC, id DESC ) AS rn FROM customer ) DELETE FROM customer WHERE id IN (SELECT id FROM ranked WHERE rn 1);提示在生产库执行DELETE前先把DELETE换成SELECT *跑一遍确认删的就是你想删的。这不是一句空话我见过同事在正式表上写错PARTITION列一次删掉了2万多条有效数据的现场当时整个团队脸都绿了。3.4 带业务规则的去重按优先级保留记录实际业务里去重往往不是简单的每个手机号留一条而是有明确业务规则的。举个例子同一手机号出现两次其中一条有身份证号另一条没有还有一条姓名是空字符串另一条姓名完整。显然应该优先保留信息更完整的那条。WITH ranked AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY phone ORDER BY CASE WHEN id_card IS NOT NULL AND id_card ! THEN 1 ELSE 0 END DESC, CASE WHEN email IS NOT NULL AND email ! THEN 1 ELSE 0 END DESC, updated_at DESC ) AS rn FROM customer ) SELECT * FROM ranked WHERE rn 1;这里的关键是ORDER BY 里可以写任意表达式这就是窗口函数去重比GROUP BY灵活百倍的地方。你甚至可以用综合完整度评分排序ORDER BY (CASE WHEN name IS NOT NULL THEN 1 ELSE 0 END CASE WHEN phone IS NOT NULL THEN 1 ELSE 0 END CASE WHEN id_card IS NOT NULL THEN 1 ELSE 0 END CASE WHEN email IS NOT NULL THEN 1 ELSE 0 END) DESC, updated_at DESC有时候去重键也不是单一字段而是多个字段的组合比如(first_name, last_name, phone)。这时PARTITION BY里写多个列即可ROW_NUMBER() OVER (PARTITION BY first_name, last_name, phone ORDER BY updated_at DESC)4. 实战一张3万行的会员表重复率从17.6%降到04.1 第一步先摸清数据家底编写探查SQL回到开头说的那张会员表。我先跑了几个探查语句把脏数据的情况摸清楚-- 总行数、唯一手机号数、唯一邮箱数 SELECT COUNT(*) AS total_rows, COUNT(DISTINCT phone) AS distinct_phones, COUNT(DISTINCT email) AS distinct_emails FROM member; -- 手机号格式分布 SELECT phone, COUNT(*) AS cnt FROM member GROUP BY phone HAVING COUNT(*) 1 ORDER BY cnt DESC LIMIT 20; -- 邮箱域名是否五花八门 SELECT SUBSTRING_INDEX(email, , -1) AS email_domain, COUNT(*) AS cnt FROM member GROUP BY email_domain ORDER BY cnt DESC LIMIT 10;探查结果比我预想的还惨3.1万行里只有2.6万个唯一手机号重复率17.6%更诡异的是有一部分重复的手机号在原始数据里居然带着不同的横杠和空格——13800138000、138-0013-8000、86 13800138000同时存在。这就引出了清洗的必要性。4.2 第二步标准化清洗分字段逐一处理我的清洗顺序是从依赖关系出发先清手机号再清邮箱最后清姓名和地址。因为手机号是这次去重的主键必须最先搞定。-- 手机号先去掉所有空格、横杠、括号再加前缀处理 UPDATE member SET phone REGEXP_REPLACE(phone, [^0-9], ); -- 去掉中国区号前缀 UPDATE member SET phone CASE WHEN phone LIKE 86% AND LENGTH(phone) 13 THEN SUBSTRING(phone, 3) WHEN phone LIKE 86% THEN REPLACE(phone, 86, ) ELSE phone END; -- 去掉历史遗留的 0 开头固话格式针对手机号类型 UPDATE member SET phone CASE WHEN phone LIKE 01% AND LENGTH(phone) 12 THEN SUBSTRING(phone, 2) ELSE phone END;邮箱的清洗更直接统一小写、去空格即可UPDATE member SET email LOWER(TRIM(email));这里有一个细节清洗前一定要先备份原始字段。我习惯在表里加三个辅助列phone_raw、email_raw、name_raw把所有原值存进去万一清洗规则写错了还能随时对比恢复。4.3 第三步设计去重键用窗口函数精准去重标准化完成后我再探查一次重复率手机号的唯一数从2.6万变成了2.9万——因为很多看起来不同的手机号实际是同号。但不要急着删我先确定了去重规则同一手机号保留一条记录优先级依次是身份证号非空 邮箱非空 姓名最完整 注册时间最新 ID最大。对应SQL如下WITH ranked AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY phone ORDER BY CASE WHEN id_card IS NOT NULL AND id_card ! THEN 1 ELSE 0 END DESC, CASE WHEN email IS NOT NULL AND email ! THEN 1 ELSE 0 END DESC, CASE WHEN name IS NOT NULL AND name ! THEN LENGTH(name) ELSE 0 END DESC, created_at DESC, id DESC ) AS rn FROM member ) SELECT phone, COUNT(*) AS dup_count FROM ranked WHERE rn 1 GROUP BY phone HAVING COUNT(*) 1;先跑这条SELECT确认没有漏网之鱼确认无误后再执行DELETEWITH ranked AS ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY phone ORDER BY CASE WHEN id_card IS NOT NULL AND id_card ! THEN 1 ELSE 0 END DESC, CASE WHEN email IS NOT NULL AND email ! THEN 1 ELSE 0 END DESC, CASE WHEN name IS NOT NULL AND name ! THEN LENGTH(name) ELSE 0 END DESC, created_at DESC, id DESC ) AS rn FROM member ) DELETE FROM member WHERE id IN (SELECT id FROM ranked WHERE rn 1);4.4 第四步写入结果前做一次完整的数据质量校验删除之后不能直接说完事了至少要做三层校验第一层总量校验。清洗后的行数应该等于去重后的唯一手机号数。SELECT COUNT(*) AS total_after FROM member; SELECT COUNT(*) AS distinct_phones FROM (SELECT phone FROM member GROUP BY phone) t;第二层重复校验。按原表和清洗后表分别探查确认重复为0。SELECT phone, COUNT(*) FROM member GROUP BY phone HAVING COUNT(*) 1;如果这个查询结果为空说明按手机号维度已经无重复。但要注意如果你的业务上还存在同一人多个手机号的情况还需要再设计一层更宽泛的去重逻辑比如身份证号维度。实战中这张表还做了一步身份证号维度去重——有128条记录的身份证号重复但手机号不同这显然是同一个人换了手机号。处理逻辑和手机号一样只是PARTITION BY改成id_card但这里的业务规则要谨慎身份证号维度去重不能盲目执行必须人工抽查样本后再决定因为可能出现身份证号录入错误导致误并。第三层关键字段完整性校验。抽查去重后保留的记录看关键字段缺失率是否符合要求。SELECT SUM(CASE WHEN email IS NULL OR email THEN 1 ELSE 0 END) AS email_missing, SUM(CASE WHEN id_card IS NULL OR id_card THEN 1 ELSE 0 END) AS idcard_missing, COUNT(*) AS total FROM member;三层校验全部通过这次清洗才算完成。最终这张表从3.1万行降到2.55万行重复率降为0关键字段完整度比原始数据提升了一截因为保留规则会优先选信息完整的记录。5. 清洗过程中我踩过的坑以及如何规避5.1 NULL参与的去重明明重复却没查出来去重键字段为NULL时GROUP BY phone会把所有NULL分到一组。比如100条记录里phone都是NULL按上面写法会保留其中一条——这可能是好事也可能误杀。反过来如果两条记录的phone都是NULL但id_card不同按phone去重会错误合并如果两条记录phone都为空、其他字段也不同那我们本来就不该按phone去重。我的建议在去重前专门探查一次去重键的NULL值数量。SELECT SUM(CASE WHEN phone IS NULL OR phone THEN 1 ELSE 0 END) AS null_phone_cnt, COUNT(*) AS total FROM member;如果NULL占比很高就说明这个字段不适合当唯一去重键需要组合其他字段。另外一个容易踩的坑是COUNT(DISTINCT column)不统计NULL。清洗前后对比可能会让你误判必须用上面这种CASE WHEN写法把NULL也算进去。5.2 排序规则(COLLATION)不一致同一套SQL结果不一样同样是去重在不同数据库实例上跑出来的结果可能完全不同根本原因之一就是排序规则Collation。比如SQL Server的Latin1_General_CI_AS对大小写不敏感sql_latin1_general_cp1_cs_as对大小写敏感。MySQL的utf8mb4_general_ci对大小写不敏感utf8mb4_bin是二进制比较、大小写敏感。实际影响是如果邮箱字段在大小写不敏感的库里去重ZhangExample.com和zhangexample.com会被合并在大小写敏感的库里则不会被合并。所以标准化清洗统一小写必须在去重之前完成这样无论数据库Collation如何结果都一致。另外相同数据的ASCII和Unicode排序在不同版本里也可能有差异建议在项目文档里记下用的什么Collation、为什么用这个Collation。5.3 DELETE前先看SELECT事务包裹救命这个坑我在前面提过一次但它值得单独再强调。一次正确的删除流程应该是先写SELECT版确认要删除的行数。把整个删除逻辑放进事务如果数据库支持。执行DELETE后先查看事务影响的行数。确认无误再COMMIT有问题立刻ROLLBACK。以SQL Server为例BEGIN TRAN; WITH ranked AS ( SELECT id, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY updated_at DESC) AS rn FROM member ) DELETE FROM member WHERE id IN (SELECT id FROM ranked WHERE rn 1); SELECT ROWCOUNT AS deleted_rows; -- 手动确认后 -- COMMIT; -- ROLLBACK;MySQL里BEGIN/COMMIT/ROLLBACK对应同理。很多人习惯直接在可视化工具里点执行DELETE删完发现不对已经没有后悔药了。事务不会增加多少工作量但能救命。5.4 大数据量下的性能窗口函数并不万能窗口函数去重很灵活但在千万行级大表上ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)要先对全部分组键排序内存和临时表开销非常大。有几次我在几千万行的日志表上跑类似逻辑直接跑了几十分钟没出结果。这时候有几个优化思路先缩小数据范围。如果只需要清洗最近3个月的数据就先加WHERE条件。给去重键加索引。PARTITION BY字段建立索引可以大幅减少排序代价。分批处理。比如按ID范围或时间范围分批次每批跑完COMMIT一次。但注意分批处理要在业务低峰期做避免锁长时间持有。考虑用临时表替代直接删原表。新表写入清洗后的数据然后原子切换表名比在原表上DELETE更可控。大型生产表上我一般用建新表切换的方式-- 1. 创建清洗后的新表 CREATE TABLE member_clean AS WITH ranked AS (...) SELECT ... FROM member WHERE rn 1; -- 2. 确认数据无误后再原子替换 -- RENAME TABLE member TO member_old, member_clean TO member;这种方式的另一个好处是原表还在万一新表数据有问题随时可以切回来。最后再分享一个实操技巧。数据标准化和去重看起来是两个步骤但在实际项目里一定不要机械地先全部标准化、再去重。我现在的习惯是每标准化一个关键字段就立刻跑一次对应的去重探查看看这个字段的清洗把哪些重复记录暴露出来了。比如把手机号去横杠后重复率从8%跳到14%把邮箱小写后又多出3%的重复。这种清洗一步、查重一次的节奏能让你在第一时间发现清洗规则的问题而不是等到最后一步才面对一堆奇怪的合并结果。数据清洗不是一道工序而是一次和脏数据的反复博弈SQL只是那个最趁手的工具。