1. 为什么我说“先标准化再去重”才是正确顺序先聊一个很多人踩过的坑拿到一份脏数据第一反应就是写个SELECT DISTINCT或者GROUP BY把重复行干掉。结果去完重一查发现“重复”根本没少多少——因为同一家客户在表里一行叫“北京华信科技有限公司”另一行叫“华信科技北京有限公司”还有一行叫“Beijing Huaxin Tech”。字符串不同DISTINCT 认为它们是三条不同记录但明眼人一看就知道这是同一家公司。我早期处理数据时也这么干过浪费了大量时间。后来总结出一个铁律去重之前必须先做标准化。标准化把“同一个东西的不同写法”归一成“同一种写法”让重复数据从“看起来不一样”变成“看起来一样”去重才有意义。如果你跳过标准化直接去重等于让数据库帮你做“肉眼判断”结果可想而知。还有一个反向的坑有些人喜欢先去重再标准化理由是“先减少数据量处理更快”。但这样做的风险在于去重时留下的那条记录未必是质量最高的那条而且后续标准化可能把两条原本不同的记录改成相同的导致新的重复产生。所以正确顺序一定是先标准化再去重最后按业务规则挑出最优记录。这篇文章就围绕这条主线展开讲清楚 SQL 里做数据标准化和去重的完整思路、实操步骤、常见坑以及我实际项目中踩过的雷。适合正在做数据清洗、数据迁移、报表开发或者准备数据仓库的同学参考。2. 数据标准化到底标准化了什么2.1 标准化的本质把“同一个东西”写成“同一种样子”数据标准化的本质不是“改数据”而是“统一表示”。同一个业务实体在不同时间、不同系统、不同录入人员手里会留下多种多样的写法。比如日期“2024/1/5”“2024-01-05”“20240105”“2024年1月5日”其实是同一天电话号码“138-1234-5678”“13812345678”“86 13812345678”其实是同一个号码性别“男”“M”“male”“1”其实都代表男性金额“1,200”“1200.00”“1200”其实是同一个数值。数据库里这些记录如果不做标准化后续做关联、汇总、统计都会出问题。最典型的场景是两张表 JOIN左边表客户名称是“阿里巴巴”右边表是“阿里集团”JOIN 条件写死都关联不上最后只能靠人工去补。标准化的核心目标可以概括成四个字同物同形。同一个业务对象在任何地方出现都应该是同一种写法。2.2 我需要标准化的字段优先级不是所有字段都需要标准化也不是所有字段都值得花同样精力。我一般按优先级排序先处理影响最大的字段主键类字段客户编号、订单号、身份证号、手机号。这类字段是关联和去重的关键格式不统一会导致主数据混乱。优先级最高。名称类字段客户名称、产品名称、部门名称。这类字段自由文本成分多最容易出现同物异名。必须重点清洗。分类类字段性别、状态、地区、渠道。这类字段通常是枚举值标准化方法是建立映射表。数值与日期字段金额、数量、时间。这类字段问题通常是类型不一致、单位不统一、精度不一致。优先级排序的逻辑很简单字段越靠近主数据、越影响关联和去重越要先处理。名称类字段和主键类字段通常是标准化的大头也是这篇文章重点讲的场景。2.3 标准化的三个层次格式层、内容层、语义层我习惯把标准化拆成三个层次每层解决的问题不一样格式层标准化解决“写法不一样”的问题。比如统一日期格式、统一大小写、去掉空格和特殊符号、统一编码。这层最机械也最容易用 SQL 函数实现。内容层标准化解决“同一个意思不同表达”的问题。比如“北京市”和“北京”、“有限责任公司”和“有限公司”、“中国移动”和“中国移动通信集团”。这层需要做内容替换或映射。语义层标准化解决“外表完全不同但其实是同一个对象”的问题。比如“IBM”和“国际商业机器公司”、“清华大学”和“清华”。这层最难往往需要业务知识支撑有时还要引入外部数据。大部分 SQL 实战能覆盖前两层第三层能做多少取决于业务场景和数据质量。如果第三层做不了至少把前两层做扎实去重的准确率就能提升一大截。3. 去重的核心逻辑DISTINCT 只是开胃菜3.1 DISTINCT、GROUP BY、ROW_NUMBER 到底怎么选很多人一提到去重就想到SELECT DISTINCT但 DISTINCT 有个天然缺陷它只能去掉“整行完全相同”的记录不能处理“某几个关键字段重复”的情况。比如订单表里同一订单号出现三条记录但备注字段不同DISTINCT 会保留三条因为整行不完全相同。实际业务里去重通常要解决的是“关键字段重复”而不是“整行重复”。这时候有三个工具可以用SELECT DISTINCT仅适用于整行重复简单但能力有限GROUP BY适用于按分组字段去重且能顺便做聚合统计ROW_NUMBER() OVER(PARTITION BY ...)适用于“保留每组中最优的一条记录”场景最灵活。我自己的选择标准是整行重复直接 DISTINCT需要按字段去重且不关心保留哪条GROUP BY 够用需要按业务规则挑一条比如保留最新一条、保留金额最大一条、保留状态最完整一条必须上 ROW_NUMBER。3.2 为什么 GROUP BY 去重会“误伤”数据GROUP BY 去重有个隐性问题分组后 SELECT 的列必须要么是分组列要么是聚合函数。不少新手写这种语句时随意挑一列出来结果取到的不一定是想要的那条记录。举个实际例子客户表里有重复客户我想按客户名称去重保留“最近一次更新时间”最新的一条。如果无脑写SELECT 客户名称, 客户ID, MAX(更新时间) FROM 客户表 GROUP BY 客户名称;这个查询在多数数据库里跑不通因为“客户ID”既不在 GROUP BY 里也不是聚合列。就算某些数据库允许比如 MySQL 的 ONLY_FULL_GROUP_BY 关闭时取到的客户ID 往往是随机的不是最新那条。这种“看似成功实则随机”的去重比报错还危险——因为结果看起来正常实际上数据已经错了。GROUP BY 只适合“按分组维度做聚合统计”的场景。真正要“按规则保留一条记录”老老实实用窗函数。3.3 用 ROW_NUMBER 实现“按规则保留最优记录”这是我最常用的去重方案也是数据清洗里最标准的写法。先给每组数据编号再筛出编号为 1 的记录WITH ranked AS ( SELECT *, ROW_NUMBER() OVER( PARTITION BY 客户名称 ORDER BY 更新时间 DESC, 录入时间 DESC ) AS rn FROM 客户表 ) SELECT * FROM ranked WHERE rn 1;这里的PARTITION BY指定“按什么字段判断重复”ORDER BY指定“保留哪一条”。优先级最高的排在最前rn1 就是该组里业务上最想要的那条。这个写法的好处是逻辑清晰后期维护也容易。想换保留规则改ORDER BY就行不需要动整体结构。实务中我还会把“保留哪条”的规则明确写成注释方便后来接手的人理解当初的业务判断。4. 实操案例从脏数据到干净数据的完整清洗过程下面用一套完整案例串起整个流程从原始导入表开始经过字段标准化、去重、保留最优记录最后得到一张干净的主数据表。这是我在数据迁移项目里经常要干的活整个套路可以复用。4.1 先看一眼原始数据的“脏样子”假设有一张客户导入表customer_raw结构如下字段说明id主键cust_name客户名称自由文本脏phone联系电话格式乱region地区枚举值有别名created_at导入时间表里的数据大概是这个画风SELECT id, cust_name, phone, region, created_at FROM customer_raw LIMIT 10;结果通常是这样的“北京华信科技有限公司”和“华信科技北京有限公司”同时存在手机号有的带“86”有的带横线有的 138 后面少一位region 字段里“北京”“北京市”“BJ”混着出现同一客户在同一批次里导入了多条id 不同但业务上其实是同一家。这种表不能直接用必须一步步清洗。4.2 第一步清理格式层的脏字符格式层标准化优先处理三类问题空白字符、全半角符号、大小写不一致。这一步本身不改变业务内容纯粹是把写法“捋顺”。UPDATE customer_raw SET cust_name TRIM( REPLACE(REPLACE(REPLACE(cust_name,,(),,)), , ) );这段 SQL 做了三件事去掉头尾空格、把中文括号替换成英文括号、去掉字符串中间的空格。实际场景里可能还要处理不间断空格CHAR(160)、Tab 符等可以串更多REPLACE。手机号的格式清洗我一般这样处理UPDATE customer_raw SET phone REGEXP_REPLACE(phone, [^0-9], , g);这行把非数字字符全部去掉最后得到一个纯数字串。加号、横线、空格一次性清除。不同数据库的正则函数名略有差异MySQL 用REGEXP_REPLACEPostgreSQL 也支持SQL Server 没有原生正则要自己写函数或逐字符处理但思路一致只保留数字字符。做完这步后cust_name变成“北京华信科技有限公司”“华信科技(北京)有限公司”“phone”变成“13812345678”格式层面的混乱被清掉了大半。4.3 第二步用映射表解决同义不同名格式层解决不了的就是内容层的问题。“北京市”和“北京”、“有限公司”和“有限责任公司”这些属于业务别名得靠映射表。我通常会建一张normalize_map表专门存放“原始写法”和“标准写法”的对应关系原始写法标准写法字段类型北京市北京region北京北京regionBJ北京region中国移动通信集团中国移动cust_name中国移动有限公司中国移动cust_name有了映射表清洗逻辑就变成了“查表替换”UPDATE customer_raw AS r SET region m.标准写法 FROM normalize_map AS m WHERE r.region m.原始写法 AND m.字段类型 region;这个方式的好处是清洗规则和清洗代码分离。业务上新增一个别名映射只需要往表里插一行不需要改 SQL。数据量大的时候这个设计能省出大量沟通和排障时间。对于客户名称里的“有限公司/有限责任公司”这类固定后缀差异也可以建立简化映射把公司类型后缀先归一UPDATE customer_raw SET cust_name REPLACE(cust_name, 有限责任公司, 有限公司);这种替换有风险要确认业务上是否允许把“有限责任公司”简写成“有限公司”。如果公司全称涉及法律或合同不建议简化但如果是内部 CRM 做客户去重和关联简化一般能接受。做之前一定要跟业务方确认边界。4.4 第三步真正的去重与最优记录保留格式和内容都标准化后重复数据开始“现出原形”。这时候再用窗函数去重效果就非常明显。按cust_name分组每个分组保留一条记录WITH ranked AS ( SELECT *, ROW_NUMBER() OVER( PARTITION BY cust_name, phone ORDER BY created_at DESC ) AS rn FROM customer_raw ) DELETE FROM customer_raw WHERE id IN (SELECT id FROM ranked WHERE rn 1);这里PARTITION BY用了两个字段cust_name和phone。意思是“客户名称和手机号都一样才算重复”双字段判断比单字段更严谨避免两个恰好同名的不同客户被误杀。ORDER BY created_at DESC意味着保留导入时间最新的一条。实际项目中“保留哪条”的规则经常变比如有的要保留首次导入那条、有的要保留状态字段最完整那条、有的要保留金额最大那条。这个逻辑就写在ORDER BY里改动成本很低。删除前我强烈建议先做一次“预演”也就是先跑SELECT看看rn 1有多少条、分布如何。看到实际行数后你才能判断清洗规则是否符合预期而不是闷头执行DELETE。4.5 第四步为后续去重建立“匹配键”有时光靠标准化后的字段还不能完美覆盖所有重复场景。比如一个客户先后改过名字旧系统里叫“北京华信”新系统里叫“华信科技”两个名字标准化后依然不像。这时候我干脆单独造一列“匹配键”用来辅助去重。ALTER TABLE customer_raw ADD COLUMN match_key VARCHAR(100); UPDATE customer_raw SET match_key CONCAT( COALESCE(NULLIF(REPLACE(cust_name, 有限公司, ), ), 未知), |, RIGHT(phone, 8) );这段代码拼了一个“简化名称 手机号后 8 位”的键。手机号后 8 位需要至少等于固定长度否则精确匹配会失败。这样就算名称写得不完全一样只要简化后相同、手机号尾号一致辅助去重也能把它们归到一起。match_key这种字段只在清洗阶段存在清洗完可以删除。好处是它是纯 SQL 驱动的不需要额外系统适合快速落地。缺点是匹配键的选取依赖业务经验和数据分布可能要试几轮才能找到合适的组合。5. 常用 SQL 标准化函数用熟这几招就够了下面这些函数是我在清洗数据时最常用的按用途分类整理每一类掌握两三个就够应付绝大多数场景。5.1 字符串清洗三件套TRIM、REPLACE、UPPER/LOWERTRIM()去掉字符串首尾空格配合LTRIM()、RTRIM()可精确控制去左侧还是右侧REPLACE()字符串替换用于去掉空格、统一括号、替换公司后缀UPPER()/LOWER()统一大小写处理英文字段或代码字段。我自己的经验是REPLACE 是最容易被低估的函数。它可以串联使用处理多层次的脏字符。比如清理电话号码里的空格、横线、括号SELECT REPLACE(REPLACE(REPLACE(phone, , ), -, ), (, ) FROM customer_raw;5.2 正则函数REGEXP_REPLACE 是“格式清洗天花板”正则函数比 REPLACE 强在“按模式匹配替换”。你不需要枚举所有脏字符只需要把模式写对。-- 只保留数字 SELECT REGEXP_REPLACE(phone, [^0-9], , g); -- 去掉所有非中文字符 SELECT REGEXP_REPLACE(cust_name, [^一-龥], , g);这类做法在身份证号、手机号、银行卡号清洗中非常实用。只要字段里只该有数字直接用[^0-9]把所有非数字字符清洗掉。5.3 处理 NULL 和空字符串COALESCE、NULLIFNULL和空字符串在 SQL 里不是一回事但业务上往往都得按“缺失”处理。我用一对函数组合-- 空字符串转成 NULL统一缺失表示 UPDATE customer_raw SET region NULLIF(TRIM(region), );把空字符串统一成NULL的好处是后续COUNT、JOIN、去重逻辑都可以统一按NULL判断不会出现“有些缺失是 NULL 有些缺失是空串”的混乱局面。5.4 格式化函数日期和金额的标准统一日期标准化我一般用CAST或TO_DATE配合标准格式模板最保险的做法是先把所有日期字符串转成 DATE 类型再按统一格式输出SELECT TO_DATE(2024/1/5, YYYY/MM/DD) AS std_date;金额字段的常见问题是带了千分位逗号或货币符号比如“$1,200.00”。同样先用正则清掉非数字和符号再CAST成数值型SELECT CAST(REGEXP_REPLACE($1,200.00, [^0-9.], , g) AS NUMERIC) AS std_amount;6. 实战中的坑我踩过的那些雷标准化和去重看似简单实际操作中坑非常多。挑几个印象深刻的说一下。6.1 坑一手机号去重时把“不同人”误判成“同一个人”手机号清洗只保留数字后出现了一个问题同一张表里两个人留了同一部座机电话清洗后号码完全相同去重时直接把第二个人删了。后来我加了规则手机号必须满足 11 位且以 1 开头才参与去重否则归入“待人工确认”列表。这个逻辑用 SQL 表达就是CASE WHEN phone ~ ^1[0-9]{10}$ THEN phone ELSE NULL END AS mobile_std这个经验很重要不要对所有字段无差别做标准化和去重要按字段业务属性来。座机、手机、传真混在一个字段里时先拆分类型再处理。6.2 坑二名称后缀替换把“公司类型”改没了有一次我把“有限责任公司”直接替换成“有限公司”结果把“北京某某投资有限责任公司”和“北京某某投资有限公司”合并成了一个。当时觉得没问题后来法务部门找过来说这两家公司在工商登记里是不同主体。从合同和发票的角度看这个简写是不能接受的。从此我总结了一条规则涉及法律主体、合同、发票的数据公司名称后缀不能随意简写。如果只是内部 CRM 做客户归并可以适度简化但一定要有业务方书面确认。6.3 坑三字符串截断引发重复SQL Server 或 MySQL 里如果VARCHAR列长度不够写入超长字符串会被静默截断SQL Server 有时还会报错。两个不同的长文本被截到同一个长度后就变成了“相同”的值去重时被误删。所以标准化过程里如果涉及超长字段截断必须先确认业务含义是否允许截断。不允许截断就扩容字段或者用哈希值去重而不是直接截断字符串。6.4 坑四COUNT(DISTINCT) 统计出来的数不准如果没做标准化直接用COUNT(DISTINCT cust_name)统计结果会偏大因为“北京华信”和“华信科技”被算成两类。哪怕做了标准化如果只清理了格式没清理语义统计依然可能虚高。实务中我会用“标准化后的字段”做统计并且把统计结果和人工抽查结果对一下。如果差异超过 5%说明还有隐藏的重复语义没处理干净需要继续补充映射表。7. 一批实际可用的去重标准写法模板下面几段 SQL 模板是我这些年反复使用的基本覆盖了常见的去重场景。直接改表名和字段名就能用。7.1 模板一按单字段去重保留最新记录WITH ranked AS ( SELECT *, ROW_NUMBER() OVER( PARTITION BY cust_name ORDER BY updated_at DESC ) AS rn FROM customer_table ) DELETE FROM customer_table WHERE id IN (SELECT id FROM ranked WHERE rn 1);适合客户表、商品表这种“按一个主键字段判重”的场景。7.2 模板二按多字段联合判重保留优先级最高的记录WITH ranked AS ( SELECT *, ROW_NUMBER() OVER( PARTITION BY cust_name, phone, region ORDER BY CASE WHEN status VALID THEN 0 ELSE 1 END, updated_at DESC ) AS rn FROM customer_table ) SELECT * FROM ranked WHERE rn 1;ORDER BY里先用CASE把状态为“有效”的记录排到最前再按更新时间排序。这种写法的实用度极高因为很多业务去重时不是简单“留最新”而是“优先留有效记录再留最新”。7.3 模板三先标记重复再由业务方确认是否删除有些场景不宜直接 DELETE而是要在表里加一列“是否疑似重复”标记。业务方人工确认后再做物理删除。WITH ranked AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY cust_name, phone ORDER BY updated_at DESC) AS rn FROM customer_table ) UPDATE customer_table SET is_duplicate 1 WHERE id IN (SELECT id FROM ranked WHERE rn 1);这个做法的价值在于“可追溯、可撤销”。直接删除记录很难恢复但加标记列处理后业务方可以在界面上一一确认减少误删风险。数据量在几万行以内时这个方案比直接 DELETE 稳妥得多。7.4 模板四标准化和去重一步完成如果不想改动原表也可以直接用子查询或者 CTE 一次性输出“标准化后且去重后的结果”。WITH normalized AS ( SELECT id, TRIM(REPLACE(cust_name, , )) AS cust_name_std, REGEXP_REPLACE(phone, [^0-9], , g) AS phone_std, MAP.标准写法 AS region_std, updated_at FROM customer_raw LEFT JOIN normalize_map MAP ON customer_raw.region MAP.原始写法 ), ranked AS ( SELECT *, ROW_NUMBER() OVER( PARTITION BY cust_name_std, phone_std ORDER BY updated_at DESC ) AS rn FROM normalized ) SELECT * FROM ranked WHERE rn 1;这个 SQL 适合“只想查一遍干净数据、不改底表”的场景比如数据分析师临时拉数用。实际项目中我经常把这个查询包装成一张视图业务方直接查视图就能拿到干净数据。8. 数据量很大时怎么办聊聊性能和索引数据行数上万之后上述写法一般没问题但到了几百万行去重查询可能会变得很慢。这里分享几个提速要点。8.1 为分组字段建索引PARTITION BY背后要做排序和分组如果分组字段没有索引数据库需要全表扫描。可以先给判重字段建立索引CREATE INDEX idx_cust_name ON customer_raw(cust_name); CREATE INDEX idx_cust_name_phone ON customer_raw(cust_name, phone);索引不是越多越好但判重字段上的索引很值得。如果同一张表要做多次去重索引能显著减少排序成本。8.2 先用子查询缩小数据范围如果只需要清洗“最近半年”的数据可以先过滤到小数据集再做去重。全表去重和局部去重完全是两个量级。WITH filtered AS ( SELECT * FROM customer_raw WHERE updated_at 2024-01-01 ), ranked AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY cust_name ORDER BY updated_at DESC) AS rn FROM filtered ) DELETE FROM customer_raw WHERE id IN (SELECT id FROM ranked WHERE rn 1);范围过滤能让排序的数据量大幅下降执行时间会明显缩短。8.3 把“重复标记”做成增量更新如果每天都有新数据进来没必要每次全量重新跑一遍去重。更合理的方案是首次全量清洗之后每天只对新插入的数据跑增量去重。增量去重的口径是“新数据是否和已有数据重复”而不是“全表重新分组”。这个问题展开讲能单独写一篇核心思路是清洗不是一次性的要做成可持续维护的流程。映射表、清洗规则、去重逻辑都应该版本化管理否则三个月后新增的数据又脏了前面全白忙。9. 几个必须补充的注意事项做了这么多年数据清洗有几条听起来像废话但时刻救命的经验单独列在这里。9.1 先备份再动手任何 UPDATE 和 DELETE 之前务必先备份原始表。哪怕只是一个备份表也够用CREATE TABLE customer_raw_bak AS SELECT * FROM customer_raw;数据清洗过程中一旦发现规则误判备份表是唯一后悔药。这个习惯我吃了不少次亏之后才养成现在每次清洗都先建备份。9.2 清洗规则全部写成注释一条 UPDATE 语句背后可能隐藏着业务判断比如“为什么要把有限责任公司改成有限公司”。不写注释的话三个月后你自己都未必记得当初为什么这么干。-- 内部 CRM 客户归并用合同与发票场景不允许此简化 UPDATE customer_raw SET cust_name REPLACE(cust_name, 有限责任公司, 有限公司);9.3 用抽查验证清洗结果清洗完不要急着交付先抽查一批数据。常用做法是按标准字段分组看看分组里行数大于 1 的情况还剩多少随机抽 50 条标准化后的记录人工判断是否合理。SELECT cust_name_std, COUNT(*) FROM customer_clean GROUP BY cust_name_std HAVING COUNT(*) 1;如果同一标准化名称还有多条记录说明语义层标准化没做透需要继续补映射表。9.4 保留清洗过程的审计信息我在清洗表时习惯加两列clean_rule_version和cleaned_at。这样每一条记录是哪一天、用哪个版本的规则清洗的都能查出来。后续如果发现清洗结果有问题可以快速定位是哪批数据、哪条规则出了问题。ALTER TABLE customer_clean ADD COLUMN clean_rule_version VARCHAR(50); ALTER TABLE customer_clean ADD COLUMN cleaned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;10. 我的个人建议先搭一套可复用的清洗模板最后分享一点个人体会。数据标准化和去重不是“写一条 SQL 跑完”就结束的事它更像一套持续迭代的小工程。我的建议是把常用清洗逻辑沉淀成模板建一个标准字段定义表明确每个字段的标准格式、最大长度、可空性、枚举值域建一张映射表维护所有同义不同名的替换关系写一套标准化 SQL把格式清洗、内容映射、语义归并做成可复用脚本写一套去重 SQL把 ROW_NUMBER 的写法做成模板按业务规则传参最后加一道抽查清单每次跑完数据按清单验证结果。这样做的好处是下次拿到任何脏数据你不需要从零开始想逻辑直接把表结构套进来跑一遍就行。我做的第一个清洗项目花了三天后面再做类似项目只需要半天区别就在有没有沉淀模板。如果你刚接触这一块建议从一张小表练手走一遍“备份-标准化-映射-去重-抽查”的完整流程把每个环节的 SQL 都跑熟。踩过几次坑之后你会发现这些看似琐碎的操作最后都会变成你处理数据的基本功而且越用越顺手。