数据标准化和去重听起来是两个很朴素的词但真正在业务里跑过一遍的人都知道这两件事做不好后面所有报表、分析、模型全是空中楼阁。我自己接过不少数据清洗的活儿最深的感受是你花在整理脏数据上的时间往往比写核心业务SQL的时间还长。这篇文章就把我踩过的坑和沉淀下来的套路完整写出来从标准化到去重再到两者组合实战每一段都是可以直接抄作业的SQL。1. 为什么数据标准化与去重必须放在一起做1.1 数据质量问题的真实成本很多人以为数据清洗就是把重复行删掉其实远没那么简单。我见过一张客户表同一个客户“张伟”出现了五次五次的名字写法完全一样但手机号分别是138开头的、186开头的还有一个干脆是空值。如果只按名字去重你会把五个不同的人都当成一个人如果只按手机号去重那空值那条记录就永远沉底。更麻烦的是还有一条记录的姓名是“张 伟”中间多了个空格肉眼看着是同一个人但SQL比一比完全不等。这就是数据标准化和去重必须联动的原因标准化是去重的前置动作。不把空格、大小写、日期格式、枚举值这些乱七八糟的差异抹平去重就只能在错误的基础上做判断结果自然是既漏掉重复、又误删有效数据。我个人的习惯是任何清洗项目启动前先花半天摸清数据现状搞清楚有哪些脏数据类型再动手写SQL。盲目开干的结果往往是越洗越脏。1.2 标准化与去重的执行顺序先标准化、再去重这个顺序基本是固定的但很多人会忽略一个细节标准化本身也可能制造新的重复。举个例子两张表合并之前一张表里性别字段存的是“男”另一张表里存的是“M”标准化时你把“M”都转成了“男”然后去重结果发现两个原本不同的记录因为性别字段被统一后触发了去重规则被合并成了一条。这个场景在某些业务下是对的但如果你去重键里包含性别字段就要想清楚这种合并是否符合业务预期。所以我通常把流程定成四步摸清数据分布列出所有脏数据模式。设计标准化规则明确每个字段的清洗口径。执行标准化生成新列或新表保留原始数据留底。基于标准化后的字段设计去重键执行去重。这个流程看起来简单但每一步都有坑下面我分章节详细拆解。2. 数据标准化把脏数据理清楚的SQL手法2.1 字符串类字段的标准化字符串脏数据大概有几种前后空格、全角半角混用、大小写不一致、隐含换行符、字符乱码。其中最常见的就是空格问题。先看一个典型场景原始数据张三 张三 张 三 张三 如果你直接用WHERE name 张三去查只能查出第一条。正确做法是先用TRIM去掉前后空格再考虑内部空格。不同数据库的写法略有差异-- SQL Server SELECT TRIM(name) AS clean_name FROM customers; -- PostgreSQL SELECT TRIM(name), BTRIM(name), REGEXP_REPLACE(name, \s, , g) FROM customers; -- MySQL SELECT TRIM(name), REPLACE(REPLACE(name, CHAR(13), ), CHAR(10), ) FROM customers;内部空格的清洗要谨慎像“张 三”这种如果确定姓名里不应该有空格可以直接REPLACE去掉但如果是地址字段内部空格可能是有效的就不能一刀切。我的经验是字符串清洗规则必须按字段语义定制不能拿一套规则套所有列。大小写问题同样常见英文名、邮箱、城市名都可能出现大小写不一致。比如Shanghai和shanghai业务上明显是同一个地方SQL默认比较却是区分大小写的。统一大小写的标准写法UPDATE customers SET city UPPER(city); -- 统一转大写 -- 或者 LOWER(city)看业务偏好这里有个细节UPPER和LOWER在不同数据库的排序规则下表现不同。SQL Server 里和数据库的COLLATE设置有关MySQL 里则和表的COLLATION有关。所以统一大小写之前最好确认一下字段的collation配置否则可能出现转完还是“看起来一样但比较不等”的情况。2.2 日期、数字和空值处理日期格式化是大头。业务系统里常见的日期脏数据包括2024/1/5、2024-01-05、01/05/2024、2024.1.5甚至还有20240105这种纯数字串。标准化的核心思路是把所有格式统一成数据库原生日期类型而不是统一成某种字符串。因为只有变成真正的日期类型后续才能做排序、计算和范围查询。以2024/1/5为例-- PostgreSQL SELECT TO_DATE(2024/1/5, YYYY/MM/DD); -- MySQL SELECT STR_TO_DATE(2024/1/5, %Y/%m/%d); -- SQL Server SELECT CONVERT(DATE, 2024/1/5, 101);转换的时候最怕遇到非法日期比如2024-13-45直接转换会报错。稳妥的做法是先做一次合法性校验把转换不了的记录单独拎出来人工处理-- PostgreSQL SELECT original_value FROM raw_table WHERE TO_DATE(original_value, YYYY/MM/DD) IS NULL;空值处理也是标准化的一部分。ETL时最常见的坑是空字符串和NULL被当成两种值。业务上它们往往都表示“没有”但排序、聚合、去重的行为完全不一样。统一空值的常用写法UPDATE customers SET phone NULLIF(TRIM(phone), ) WHERE phone OR phone IS NULL;NULLIF的作用是把空字符串转为NULL这是我很喜欢的一个函数简洁且语义清晰。但要注意字段一旦变成NULL索引的效率会下降WHERE phone NULL也永远查不到东西必须用IS NULL这是新手最容易踩的坑之一。2.3 枚举值与其他业务字段的标准化枚举值的标准化工具有典型的业务特征。拿性别举例不同的录入渠道会写入男、男性、M、male、1等不同的值。标准化的本质是建立一张映射表UPDATE customers SET gender CASE WHEN gender IN (男, 男性, M, male, 1) THEN M WHEN gender IN (女, 女性, F, female, 0) THEN F ELSE U END;这里ELSE U是处理未知值的兜底非常关键。如果不写ELSE那些没命中规则的值会被置成NULL等于把脏数据变成了缺失数据反而丢失了信息。我一般建议在标准化后跑一条分类统计确认每条规则命中了多少行有没有意想不到的值被归到U里SELECT gender, COUNT(*) FROM customers GROUP BY gender;如果U的占比超过预期说明映射规则没覆盖到所有情况需要补充。3. SQL去重的几种正确姿势3.1 简单场景DISTINCT 与 GROUP BY 怎么选全字段重复的场景最简单一行SELECT DISTINCT就完事了SELECT DISTINCT customer_id, name, phone FROM customers;但DISTINCT有两个隐含问题。第一它比较的是所有查询列的完整组合哪怕只有一列有细微差别两条记录也不会被合并第二DISTINCT的语义是全列去重无法做到“按某几列去重、保留另一列的特定值”。这时候就需要GROUP BY出马了。比如我想按customer_id去重同时保留最新的注册时间可以这样写SELECT customer_id, MAX(register_date) AS latest_register_date FROM customers GROUP BY customer_id;GROUP BY的本质是先分组再聚合它能配合聚合函数实现“分组去重取特征值”的组合效果比DISTINCT灵活得多。但GROUP BY也不是万能的如果我想保留整行原始记录比如保留phone、address这些非聚合字段就必须用窗口函数。3.2 进阶方案ROW_NUMBER() 窗口函数的精确保重窗口函数是去重场景里实用性最高的工具。它的核心思路是先按业务去重键分区再在分区内按某个优先级排序最后利用行号保留每组的第一条。比如客户表里有重复的手机号我想保留每条手机号对应的最新一条记录WITH ranked AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY phone ORDER BY register_date DESC, id DESC ) AS rn FROM customers ) SELECT * FROM ranked WHERE rn 1;拆开看这段SQLPARTITION BY phone是按手机号分组同号的记录会被分到同一个窗口ORDER BY register_date DESC决定保留哪一条最新注册的排在第1位rn 1就是每组的第一条。这个写法最大的优势是查询结果保留的是完整的原始行所有非去重键的字段都在你可以任意挑选保留哪一条。这是DISTINCT和GROUP BY都做不到的。如果业务上想保留的不是最新一条而是某个状态下的一条比如“用手机号去重保留状态为‘已激活’的记录优先”可以这样ROW_NUMBER() OVER ( PARTITION BY phone ORDER BY CASE WHEN status active THEN 0 ELSE 1 END, register_date DESC ) AS rnCASE在ORDER BY里的作用是自定义优先级这个技巧在处理业务去重时非常常见。3.3 删除重复数据的标准SQL模板查询去重只是第一步实际清洗往往要直接把重复数据删掉只留一条。不同数据库的删除语法差异很大我分别列出来。SQL Server 和 PostgreSQL 可以用CTE配合删除WITH ranked AS ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY phone ORDER BY register_date DESC ) AS rn FROM customers ) DELETE FROM customers WHERE id IN (SELECT id FROM ranked WHERE rn 1);MySQL 8.0 也支持CTE但老版本的MySQL只能靠多表关联或者临时表DELETE c1 FROM customers c1 INNER JOIN customers c2 WHERE c1.phone c2.phone AND c1.register_date c2.register_date;这段SQL的意思很直白如果两条记录手机号相同而且 c1 的注册时间早于 c2就删掉 c1。最后剩下的就是每个手机号里注册时间最晚的那条。删除之前一定要先跑一遍SELECT确认要删哪些行、保留哪些行把DELETE换成SELECT预览一下结果再执行。我见过太多人写完DELETE直接执行删完才发现误删又没法回滚只能用备份恢复非常痛苦。3.4 基于业务规则的分组去重有些去重需求不是简单按某个字段相等来判断而是按业务规则判断。比如“同一公司、同一天注册、且姓名拼音相同的用户视为同一人保留一条”。这种需求用纯SQL也能做思路是先把业务规则计算成标准化字段再加入去重键WITH normalized AS ( SELECT *, LOWER(REPLACE(name, , )) AS name_key, company_id, DATE(register_date) AS register_day FROM customers ), ranked AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY name_key, company_id, register_day ORDER BY id ) AS rn FROM normalized ) SELECT * FROM ranked WHERE rn 1;去重键不再是一个字段而是一组经过标准化处理的字段组合。这个思路几乎可以覆盖所有复杂的业务去重场景。4. 标准化与去重组合实战一个完整案例4.1 业务背景与脏数据样例光讲概念不够直观我把一个真实的清洗场景完整走一遍。假设有一张user_import表是从三个不同渠道导入的用户数据合并来的需要清洗后写入正式表dim_user。原始数据大概长这样iduser_namephoneemailregister_timechannelgender1张三13812345678zhangsantest.com2024/1/5 09:30app男2张 三138-1234-5678ZhangSantest.com2024-01-05 09:45webM3李四(null)lisitest.com2024/02/05h5女4李四lisitest.com2024-02-05h5女5王五13912345678(null)2024-02-06app未知6王五13912345678wangwutest.com2024-02-06appU这里的脏数据问题一眼能看出来id 为 1 和 2 的记录很可能是同一个人但名字里有空格、手机号里有横线、邮箱大小写不一致。id 为 3 和 4 的记录手机号一个为NULL、一个为空字符串但邮箱完全相同也是同一个人。id 为 5 和 6 的记录手机号相同邮箱一个有值一个为空也是重复数据。注册时间格式有三种渠道字段还算干净但性别字段混入了不同写法。4.2 清洗流程设计我设计了三层清洗逻辑第一层字段标准化。去掉名字和邮箱的首尾空格把邮箱统一转小写手机号去掉横线、空格等非数字字符注册时间统一转成标准日期格式空字符串统一转NULL性别字段映射成统一编码。第二层生成去重键。以“标准化手机号”为主去重键手机号为空的记录用“标准化邮箱”作为辅助去重键。这里的关键设计是不能只用一个键否则手机号为空的那批用户永远无法去重。第三层执行去重并保留可追溯的原始记录。每组重复数据中保留在正式库里最先出现的一条即原始id最小的一条。4.3 分步SQL实现已知不同数据库语法有差异我用通用写法加注释说明的方式呈现方便你迁移到自己的环境。第一步先建一个标准化临时表CREATE TABLE user_norm AS SELECT id, user_name, TRIM(phone) AS phone, LOWER(TRIM(email)) AS email, register_time, CASE WHEN gender IN (男, 男性, M, male, 1) THEN M WHEN gender IN (女, 女性, F, female, 0) THEN F ELSE U END AS gender FROM user_import;第二步处理手机号里的横线和空格用一个正则把非数字字符去掉。PostgreSQL 写法如下其他数据库需要换成对应的函数UPDATE user_norm SET phone REGEXP_REPLACE(phone, [^0-9], , g) WHERE phone IS NOT NULL;同时把空字符串转成NULLUPDATE user_norm SET phone NULLIF(TRIM(phone), ) WHERE phone ;第三步处理注册时间。这个字段在不同行里有不同的分隔符用正则提取出年月日部分再拼接成标准格式UPDATE user_norm SET register_time TO_DATE( REGEXP_REPLACE(register_time, [/.], -), YYYY-MM-DD HH24:MI );第四步生成去重键UPDATE user_norm SET dedup_key COALESCE(phone, email);这一步有个潜在风险如果两条记录里一条手机号是NULL、一条是空字符串COALESCE 会分别落到邮箱上但此时手机号已经被标准化成NULL两条记录的去重键就都是邮箱能正确识别为重复。如果手机号有价值优先用手机号邮箱作为兜底。第五步用窗口函数标记每组内的行号SELECT *, ROW_NUMBER() OVER ( PARTITION BY dedup_key ORDER BY id ) AS rn FROM user_norm;第六步把rn 1的记录插入正式表INSERT INTO dim_user (id, user_name, phone, email, register_time, gender) SELECT id, user_name, phone, email, register_time, gender FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY dedup_key ORDER BY id ) AS rn FROM user_norm ) ranked WHERE rn 1;实际跑下来id 2、4、6 被正确识别为重复数据排除掉最终表里剩下 1、3、5 三条记录每条都是标准化之后、无重复的完整数据。4.4 效果验证与验收标准清洗完成后一定要做两个验证动作。第一个动作是重复率验证。清洗后跑一遍去重查询确认dedup_key没有重复值SELECT dedup_key, COUNT(*) FROM dim_user GROUP BY dedup_key HAVING COUNT(*) 1;如果查询结果为空说明去重生效。如果不为空就要查原因可能是键生成逻辑有漏洞。第二个动作是数据完整性对比。清洗前的总行数减去清洗后的总行数等于被删除的重复行数这个数字应该有合理的解释。我还会抽查几条被删除的记录确认它们确实和被保留的记录是同一实体而不是误删。5. 性能优化与常见问题排查5.1 为什么我的去重查询这么慢数据量一大去重查询动不动就全表扫描几百万行跑几分钟是常有的事。去重慢的常见原因有两个去重键上没有索引、窗口函数的排序列没有索引。先说索引设计。如果按phone字段去重应该给这个字段建索引CREATE INDEX idx_user_phone ON user_norm(phone);如果去重键是COALESCE(phone, email)这种表达式普通索引可能用不上需要建表达式索引CREATE INDEX idx_user_dedup ON user_norm((COALESCE(phone, email)));再说SQL的写法优化。ROW_NUMBER() OVER (PARTITION BY phone ORDER BY register_date DESC)这种窗口函数在几百万行的表上跑排序开销很大。如果业务允许可以先在子查询里过滤掉明显不可能保留的行把参与排序的数据量降下来。我经历过一个真实的优化案例一张800万行的订单表每月按订单号去重保留最新状态原始写法跑了4分钟。建了分区键索引后时间降到40秒再加一句预先过滤“只保留最近3个月的数据”最终只需要6秒。去重SQL的瓶颈通常不在SQL本身而在数据量和索引设计。5.2 常见坑NULL、隐式类型转换与排序不稳定NULL 可以说是SQL清洗里最阴险的坑。NULL NULL的结果不是TRUE而是NULL所以两条记录如果去重键都是NULL用JOIN ... ON a.key b.key是永远匹配不上的。这也是为什么我在实战里总会用COALESCE把NULL转换成实际可比较的值。隐式类型转换是另一个隐蔽的坑。如果phone字段在表里是字符串类型但存的是数字而另一张表里是数字类型两个字段直接JOIN数据库会做隐式转换。转换过程中如果遇到无法转换的内容轻则查不出数据重则直接报错。排序不稳定也值得注意。ORDER BY register_date DESC时如果 register_date 有大量相同的值数据库返回的排序结果是不可预期的每次执行可能保留不同的记录。解决方法是排序条件里加上主键等唯一字段确保结果确定ORDER BY register_date DESC, id DESC我见过一个线上数据事故就是排序条件不够唯一导致每天跑出来的“保留记录”都不一样业务方后面核对数据时发现同一批客户在两天内被分配了不同的保留记录排查了很久才发现是排序稳定性问题。5.3 不同数据库的语法与细节差异同样一个去重需求在三大主流数据库里的写法差别不小我列一个速查表操作PostgreSQLMySQLSQL Server去除首尾空格TRIMTRIMTRIM正则替换REGEXP_REPLACEREGEXP_REPLACE8.0没有原生正则用嵌套REPLACE字符串转日期TO_DATESTR_TO_DATECONVERT窗口函数支持完整窗口函数8.0才支持窗口函数2012支持完整窗口函数DELETE配合CTE支持8.0支持支持MySQL 8.0 以下的老版本不支持窗口函数去重只能靠GROUP BY加临时表的方式实现。我平时用的比较多的是 PostgreSQL但也会遇到必须兼容老MySQL的场景这时候就得老老实实分两步走先把要保留的id查出来SELECT MAX(id) AS keep_id FROM user_norm GROUP BY dedup_key;再删除不在保留列表里的记录DELETE FROM user_norm WHERE id NOT IN ( SELECT MAX(id) FROM user_norm GROUP BY dedup_key );注意这里如果dedup_key里有NULLNOT IN会出问题子查询里要加WHERE dedup_key IS NOT NULL过滤掉。这是老MySQL环境里的一个隐蔽陷阱我踩过一次之后每次都会记得带上这个条件。6. 清洗脚本的可复用设计6.1 从一次性脚本到通用清洗工具很多人做数据清洗都是临时写一段SQL跑完就丢下次遇到类似需求又从头写一遍。时间久了你会发现标准化的套路是高度重复的去空格、转大小写、格式化日期、映射枚举、去重复。完全可以把这些规则抽象成可复用的模板。我自己的做法是维护一个“清洗规则清单”每处理一个字段类型就记录一段标准SQL遇到新项目先把清单过一遍匹配的规则直接复制再根据业务调整参数。效果很明显我上一次接手一个类似的清洗需求过去需要大半天这次两个小时就完成了。一个简单的做法是把清洗逻辑封装成视图或者存储过程下次直接调用。比如用CTE把标准化的核心逻辑保留在一个文件里业务表变了就改表名其余的不用动。6.2 留下审计痕迹清洗最怕的是没有审计痕迹。你删了哪些数据、改了什么字段、为什么这么改都要有据可查。我通常在清洗前会建一张操作日志表记录每次清洗的类型、影响行数、执行时间。做完清洗后再把被删除的记录和修改前的记录归档到历史表防止后续业务方问“这条记录去哪了”的时候你答不上来。举个例子删除重复数据前先把要删除的行备份CREATE TABLE user_dedup_backup AS SELECT * FROM user_norm WHERE rn 1;归档和清洗是一个事务先备份再删除就算业务方后来发现删错了也能从备份表里恢复。这个习惯帮我挡过好几次“数据事故”的锅。6.3 定时任务里的注意事项如果清洗是每天跑的任务还有个细节容易被忽略幂等性。同一个数据源如果被清洗脚本重复跑两次结果应该是一致的而不是把数据越洗越少。幂等性的关键在去重步骤。如果正式表里已经写入了一条记录第二天的清洗脚本又处理了同一个源数据没有判断“这条记录是否已存在”就会再插入一条重复数据。所以在插入前要加一个存在性判断INSERT INTO dim_user (id, user_name, phone, email, register_time, gender) SELECT id, user_name, phone, email, register_time, gender FROM user_norm WHERE NOT EXISTS ( SELECT 1 FROM dim_user d WHERE d.phone user_norm.phone OR d.email user_norm.email );这个写法在数据量小时没问题数据量大时NOT EXISTS配合索引也能接受。总之定时任务里一定要写幂等判断否则整个清洗链路就是一台随时可能出故障的机器。最后分享一个我个人的技巧。这段SQL看起来简单但价值非常高——当你面对一个完全陌生的脏数据表时先跑一遍各字段的COUNT(DISTINCT ...)和COUNT(*)用行数差异快速定位哪些字段的高重复率问题哪几个字段组合起来能唯一标识一条记录。这比直接拍脑袋写清洗脚本靠谱得多我每次接新表都是从这个动作开始的。数据标准化和去重这件事说到底是理解你的数据、理解你的业务然后用SQL把规则固化下来。规则越清晰SQL越简单数据质量就越稳定。