上周我接到一个挺有意思的需求业务方甩给我一张订单表说了句帮忙去个重然后就去开会了。等我打开表一看当场愣住——这张表里既有多行完全一样的数据又有同一个用户多条不同状态的记录还有用户和商品的组合反复出现。当时我意识到一件事在SQL世界里去重这个词至少藏着四五种完全不同的语义每一种对应不同的写法搞混了轻则结果不对重则直接把线上数据搞出大麻烦。这篇文章就把我这些年处理数据去重和唯一值提取的实战经验完整梳理一遍。从需求拆解、手段选型、高频场景配方到生产环境清理重复数据的完整链路再到性能优化思路都会展开来讲。不管你是刚接触SQL的新人还是经常和数据打交道的分析师、后端开发这篇内容应该都能给你一些可以直接抄作业的写法以及很多文档里不会写的为什么和别踩我踩过的坑。1. 先别急着写SQL拆清重复的四种真实语义很多人一听到去重两个字条件反射就是DISTINCT或者GROUP BY。但根据我的经验拿到需求先别动手第一步永远是搞清楚对方口中的重复到底是指什么。不同语境下的重复处理逻辑天差地别甚至有些看起来像重复的数据根本不应该被去掉。1.1 完全重复行每一列都一模一样这是最简单、最教科书化的场景几行数据的每一列值都完全相同你想把它们压缩成一行。这种重复通常出现在数据导出、多表合并、UNION ALL操作之后。举个具体例子两张分表汇总出以下数据-- 两张分表结构相同UNION ALL后可能产生完全重复行 SELECT user_id, order_no, amount FROM orders_2023 UNION ALL SELECT user_id, order_no, amount FROM orders_2024;如果两条订单在两个表中都出现过UNION ALL的结果里就会出现两行完全一样的数据。此时DISTINCT是最直接的解法SELECT DISTINCT user_id, order_no, amount FROM ( SELECT user_id, order_no, amount FROM orders_2023 UNION ALL SELECT user_id, order_no, amount FROM orders_2024 ) t;但这里藏着一个特别隐蔽的坑如果表结构里带有自增主键id那么任何两行的id都不可能相同DISTINCT永远去不掉所谓重复的整行数据。很多新手在宽表里去重失败就是因为没意识到完全重复行的定义里必须把所有列都算进去。换句话说只有当你确认表里没有唯一标识符或者你明确豁免了某些列DISTINCT才有意义。1.2 业务维度重复主键相同但其他值在变第二种重复在实际业务中更加普遍从业务上看这确实是同一条记录但它对应着多条不同版本的数据比如订单的状态变更记录、账户的登录日志、员工岗位的调整历史。拿订单举例同一笔订单order_no ORD2024001可能出现三行order_nostatusupdated_atORD2024001已支付2024-03-01 10:00:00ORD2024001已发货2024-03-02 14:30:00ORD2024001已签收2024-03-05 09:12:00如果你接到需求说把这个订单表去重一个订单只留一行你需要立刻追问保留哪一行是最新状态的还是最早创建的还是根据某个优先级规则来选这时候DISTINCT根本无能为力——因为这三行没有任何一列完全一样但它们确实共享同一个业务维度订单号。这类问题正确的打开方式是分组排序后取第一条窗口函数ROW_NUMBER()就是为此而生的WITH ranked_orders AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY order_no ORDER BY updated_at DESC) AS rn FROM orders ) SELECT * FROM ranked_orders WHERE rn 1;这段SQL的逻辑是按order_no把数据分组组内按updated_at降序排列然后给每一行编号最后只保留每组编号为1的那行。我后面会专门拆解这类写法这里先记住一个核心思路只要需求里有每组保留一条这种说法大概率就要用窗口函数而不是DISTINCT。1.3 组合重复单看不重合起来重了第三种情况更迷惑人。某个用户的ID单独看不重复某个商品的ID单独看不重复但(user_id, product_id)这个组合却在表里出现了两次甚至多次。常见场景是加购表、收藏表、浏览记录表。用户可以在不同时间反复对同一个商品执行同一操作于是用户商品的组合就产生了多条记录。处理组合重复的出发点和前面完全不同你需要先判断组合的业务语义。如果业务上(user_id, product_id)本身就应该唯一那么这些重复属于脏数据需要清洗如果业务上允许重复那你的任务可能就是查询时把重复组合压平。给一个查询时的压平方案SELECT user_id, product_id, MAX(created_at) AS last_operated_at FROM user_product_actions GROUP BY user_id, product_id;这里用GROUP BY配合聚合函数本质上是把同一组合下的多行聚合成了一个业务维度的汇总行。注意这个写法不像ROW_NUMBER()那样可以保留完整行但它胜在简洁、性能好在很多只需要维度加汇总值的场景里非常实用。1.4 JOIN膨胀结果集重复不是表里真的重复最后一种也是日常排查里最容易背锅的一种源表本身没有一条脏数据但你的查询结果却出现了大量重复行。问题几乎总是出在JOIN上。举个例子员工表和部门表做关联查询SELECT e.emp_id, e.emp_name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.dept_id;如果departments表里有两个技术部或者技术部下面存在部门拆分的历史数据那么一个员工就可能被关联出多行结果。员工表数据没问题部门表数据也没问题但结果集膨胀了。这提醒我们去重之前先搞清楚重复到底发生在哪个环节。纯DISTINCT也许能救急但如果膨胀的原因是业务确实存在一对多关系更好的做法是先聚合子查询再关联或者明确你要的粒度是什么。我见过太多人在一个大JOIN外面套一层DISTINCT结果数据量翻了很多倍拖慢了整个查询其实应该回到源头去修正关联逻辑。2. 从DISTINCT到窗口函数去重手段的边界测试搞清楚了需求语义下一步就是选手段。DISTINCT、GROUP BY、ROW_NUMBER()是SQL去重的三件套但很多人并没有真正理解它们之间的边界经常拿A工具解决B问题然后抱怨SQL怎么这么难用。这节我带你把三种手段的真实边界摸一遍。2.1 DISTINCT简单直接但它没得选DISTINCT的核心逻辑是对查询结果做一次全量去重相同行只出现一次。它的优点是语法简洁、语义直观适用于前面说的完全重复行场景。但它的短板也非常明显你无法控制保留哪一行。一旦整个查询结果里有某几个列不同DISTINCT就认为它们不是重复行于是去重就失效了。还有一个容易被忽略的细节DISTINCT不仅可以用在SELECT后还能配合COUNT用-- 统计有多少个不同的用户下过单 SELECT COUNT(DISTINCT user_id) AS active_user_cnt FROM orders;这是唯一值提取最经典的应用。很多报表里要统计活跃用户数、去重UV都靠这一句。它的好处是不会真的把数据取出来再数执行引擎通常能走更优化的路径。不过若是需要DISTINCT多列组合你要明确知道它在以整行组合为粒度去重SELECT DISTINCT user_id, product_id FROM orders;上面这句的意思是查询所有下过单的用户-商品组合而不是把user_id去重再管product_id。这个微妙区别经常让初学者踩坑——以为结果里所有user_id都唯一其实唯一性作用在组合上。2.2 GROUP BY去重的同时也在聚合GROUP BY本质上是把数据按某些维度分桶。如果只查分桶的维度列它就能实现去重SELECT user_id FROM orders GROUP BY user_id;这和SELECT DISTINCT user_id FROM orders结果一样但执行路径可能不同。GROUP BY真正的价值在于它允许你同时计算每个桶的聚合指标比如每个用户的下单次数、总金额、最近下单时间SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount, MAX(created_at) AS last_order_time FROM orders GROUP BY user_id;理解GROUP BY的关键在于当你把非分组列放进SELECT时SQL引擎其实是在强制做聚合运算。假如你写SELECT user_id, order_no FROM orders GROUP BY user_id在MySQL的某些宽松模式下它会返回一个随机值而在SQL Server、PostgreSQL里则直接报错。很多人觉得GROUP BY去重不顺手多半是因为没搞懂这一层约束。实际项目中我会根据要不要同步算指标来决定选DISTINCT还是GROUP BY。只要逻辑里带统计汇总每个X的YGROUP BY几乎是唯一合理的选择。2.3 ROW_NUMBER()把保留哪一条的控制权拿回来这三件套里ROW_NUMBER()是最灵活、也是最能体现功力的一个。它是窗口函数可以在分组的基础上对组内排序然后赋一个序号。配合外层查询过滤序号为1的记录你就能实现每组保留一条至于是哪一条我自己说了算。先看最基础的语法结构ROW_NUMBER() OVER ( PARTITION BY col_for_group ORDER BY col_for_order DESC ) AS rnPARTITION BY负责分组ORDER BY负责组内排序。排序的第一名就是你要保留的那个唯一值。一个随机应变的设计点在于ORDER BY可以同时排多个字段以便在时间相同的情况下继续用ID倒序兜底ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY created_at DESC, order_id DESC ) AS rn这个写法看起来简单但实际项目里有大量变体需求靠它完成。比如取每个商品的最新价格取每个客户的最近一次投诉取每个班级总分第一的学生——本质上都是同一套模板先分区排序编号再过滤rn 1。我还经常会遇到一个困惑ROW_NUMBER()和RANK()、DENSE_RANK()到底怎么选简单说ROW_NUMBER()保证每条记录序号唯一哪怕排序值相同也会强分1、2、3适合只要有且仅有一条记录的场景RANK()则允许并列如果两个用户的时间完全一样会同时拿到第一DENSE_RANK()也是处理并列的但后续序号不会因并列而跳号。做去重保留一条时我几乎只用ROW_NUMBER()因为它能确保不出现并列导致重复。2.4 一份可以存进收藏夹的选型表把上面讲的整理成一张对照表我每次给团队培训都用它需求描述推荐手段核心理由去掉完全重复的行DISTINCT语义简单直接执行效率高统计不重复值的数量COUNT(DISTINCT ...)一条SQL直接出指标避免冗余数据搬运按维度分组并计算汇总指标GROUP BY 聚合函数去重和统计一次完成逻辑清晰每组保留一条特定记录最新、最早、优先级最高ROW_NUMBER() 外层过滤可精确控制保留哪一行覆盖复杂业务规则多列组合唯一性判断GROUP BY多列 或DISTINCT多列粒度和语义必须与业务诉求保持一致选型时记住一个原则当你的需求只是看看到底有哪些不同的值用最轻的DISTINCT当需求变成每个不同值对应的汇总情况或者每个分组里挑一条就要果断升级手段。3. 高频业务场景的SQL配方从取最新到防重复插入工具只是手术刀真正考验水平的是面对真实需求时能不能快速配出正确的SQL。这一节我会拆解四个出现频率极高的去重/唯一值提取场景每个都给出完整写法并解释每一步的原因。3.1 每组保留一条取每人最新订单这是一个出现频率极高的需求几乎所有带业务系统的公司都会遇到一张订单表里有用户的多个订单你要找出每个用户的最近一次下单记录。先看完整SQLWITH ranked_orders AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY order_date DESC, created_at DESC ) AS rn FROM orders ) SELECT order_id, user_id, order_date, amount FROM ranked_orders WHERE rn 1;拆开来看PARTITION BY user_id把订单按用户分组ORDER BY order_date DESC, created_at DESC保证同一天下的单也能按创建时间排出先后ROW_NUMBER()给每组内的记录编上号外层WHERE rn 1把每组最靠前的那条捞出来。这个写法的好处是你不需要在SELECT里写一堆复杂的聚合原始行的所有字段都可以直接带出来。我经常在数据看板、用户生命周期分析、风控标签里复用这套结构。这里有两个值得注意的细节。第一如果表非常大窗口函数需要把所有分区排序结果算出来才能过滤最好在PARTITION BY和ORDER BY涉及的字段上建立索引否则性能会很难看。第二如果业务上允许同一个人同一个时刻下了多笔单你需要再往ORDER BY里加一列比如订单号降序保证编号唯一否则还是可能拿到多行。3.2 多表关联产生的膨胀去重假设你有一张员工表和一张部门表需求是查出每个员工及其所属部门名称。但部门表里存在历史拆分一个部门ID对应多条部门名称记录直接JOIN后每个员工都出现了好几行。-- 这张写法会产生重复行员工被关联到多条部门记录 SELECT e.emp_id, e.emp_name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.dept_id;有两条路可以走。第一条是在外层加DISTINCT简单粗暴但如果员工还有很多其他信息列DISTINCT会把整行拿来做唯一性判断效率堪忧。第二条路是先收敛再关联即先在子查询里把部门表按部门ID去重只保留一条再和员工表关联SELECT e.emp_id, e.emp_name, d.dept_name FROM employees e LEFT JOIN ( SELECT dept_id, MAX(dept_name) AS dept_name FROM departments GROUP BY dept_id ) d ON e.dept_id d.dept_id;这就是我前面说的先搞清楚重复是因何产生的再决定在哪一层处理。联合查询膨胀的重复优先考虑从源头上把一对多的多给压成一而不是在结果集外面硬套去重。这样不仅结果正确性能也更可控。3.3 插入前的唯一性校验与幂等处理再说一个和唯一值提取密切相关的场景往表里写入数据时怎么防止重复插入方案一在数据库层面建立唯一约束。比如要求(user_id, product_id)组合唯一直接建唯一索引或唯一约束重复插入时数据库会拒绝。这是最好的防线但现实里很多表已经有存量脏数据线上不能直接加约束这时候就需要用SQL做插入前校验。通用写法是-- 检查是否存在相同组合的数据 SELECT COUNT(*) FROM user_product_actions WHERE user_id 1001 AND product_id 8888;如果COUNT(*) 0再执行插入。但这套逻辑在高并发下会出问题两个请求同时查到不存在然后同时插入仍然造成重复。真正稳妥的做法还是要靠数据库唯一约束SQL层面的校验只适合低并发后台操作这一点大家在设计时要心里有数。另一种常见思路是将重复检查融合到插入语句里。MySQL里可以用INSERT IGNORE重复时静默跳过PostgreSQL里可以用ON CONFLICT DO NOTHING或ON CONFLICT DO UPDATESQL Server里可以用MERGE。写法各不相同但核心思想一致让数据库在原子操作里完成存在则不插入不存在则插入避免应用层先查后插的竞态问题。3.4 脏数据清洗视角下的近似去重最后一个场景这些年在数据仓库项目里见得特别多源系统的数据质量太差同一个客户在不同时间段里的姓名可能带空格、前后不一致、大小写不同甚至全角和半角混用。这时候按原始字符串去重根本压不住。常用的做法是先清洗再排重。比如对字符串做标准化后再判断唯一性SELECT UPPER(TRIM(REPLACE(customer_name, , ))) AS normalized_name, COUNT(*) FROM customers GROUP BY UPPER(TRIM(REPLACE(customer_name, , )));TRIM去掉首尾空格REPLACE去掉中间空格UPPER统一大小写这样张 三、张三、zhang san等形态就有机会被归并到一起。SQL Server里还能用LTRIM、RTRIM、LOWER做类似处理。需要提醒的是字符串清洗要克制。清洗过度会误合并一些本不该合并的数据。比如人名里的OBrien和Obrien严格来说可能是两个人清洗规则一宽松就容易出事故。我在实际项目里会先把清洗后的结果放到临时表人工抽样看归并效果确认没问题再用来做唯一键匹配绝不能一上来就直接用清洗结果覆盖原始数据。4. 生产环境删除重复数据的完整链路前面说的都是查询场景接下来要聊一个更敏感的实操真的去把表里的重复数据删掉。这是很多人在生产环境里不敢动、又不得不动的硬骨头。我自己的原则始终是先查后删、备份先行、分批执行。这一节给你一套可以直接套用的完整链路。4.1 删除前的重复情况体检动手之前先回答三个问题哪些列定义了重复表里一共多少组重复每组重复多少条这三个问题不搞清楚后面的DELETE语句很容易误伤。对于按某一组字段判重的场景用一个GROUP BY ... HAVING就能体检SELECT user_id, product_id, COUNT(*) AS cnt FROM user_product_actions GROUP BY user_id, product_id HAVING COUNT(*) 1 ORDER BY cnt DESC;再加上一个汇总统计方便评估影响范围SELECT COUNT(*) AS dup_group_cnt, SUM(cnt - 1) AS extra_rows_to_delete FROM ( SELECT user_id, product_id, COUNT(*) AS cnt FROM user_product_actions GROUP BY user_id, product_id HAVING COUNT(*) 1 ) t;这里extra_rows_to_delete表示除了每组保留一条之外还要删掉多少行。这个数字直接影响你的变更评估——如果一次要删上百万行那就要考虑分批操作而不是一把梭。4.2 保留一条记录的DELETE写法现在进入核心部分。假设我们要为user_product_actions表去重每个(user_id, product_id)组合只保留一条记录。表里有主键id这就给了我们精确保留的依据每组保留id最小的那一行其余删除。思路最清晰的方式依然是窗口函数WITH ranked_actions AS ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY user_id, product_id ORDER BY id ) AS rn FROM user_product_actions ) DELETE FROM user_product_actions WHERE id IN ( SELECT id FROM ranked_actions WHERE rn 1 );先给每组按id排序编号rn 1的即为多余行全都删掉。这种写法在SQL Server、PostgreSQL、MySQL 8.0里都能用逻辑直观维护成本低。如果数据库版本不支持CTE比如MySQL 5.7或更老可以用自连接DELETE a FROM user_product_actions a JOIN ( SELECT MIN(id) AS keep_id FROM user_product_actions GROUP BY user_id, product_id ) k ON a.user_id k.user_id AND a.product_id k.product_id WHERE a.id k.keep_id;这个写法的思路是先找出每个组里要保留的最小id然后删除组内所有id大于这个保留id的行。MySQL里支持这种多表DELETE语法SQL Server里则要换成DELETE a FROM ... JOIN ...的形式具体细节可以查对应版本的语法文档。4.3 事务、备份与分批操作拿什么保证线上安全删除数据最怕的不是SQL写错而是操作流程没兜底。我给团队定的规矩很简单但每条都是拿教训换来的第一必须先备份。最简单的做法是先把涉及的行SELECT出来存到一张备份表CREATE TABLE user_product_actions_bak_20240301 AS SELECT * FROM user_product_actions;这一步在大表上会有点耗时但对生产环境来说这个成本必须付。万一删错了还能从备份表恢复。第二DELETE操作务必放进事务里执行。SQL Server和PostgreSQL都有显式事务MySQL的InnoDB同样支持START TRANSACTION; DELETE FROM user_product_actions WHERE id IN (...); -- 先不提交检查影响行数是否合理 -- 确认无误后 COMMIT;先START TRANSACTION执行完DELETE后不急着提交可以先看看影响行数再查一遍重复数据是否清干净。如果发现问题直接ROLLBACK一切回到原点。第三影响行数大的时候分批删。大批量DELETE会长时间锁表影响线上业务。可以加上LIMIT循环执行多次每批删5000行左右中间稍微停顿。MySQL示例DELETE FROM user_product_actions WHERE id IN ( SELECT id FROM ranked_actions WHERE rn 1 ) LIMIT 5000;反复执行直到影响行数为0。这样每条UPDATE/DELETE语句的锁的范围都非常可控业务侧基本无感。5. 去重查询的性能瓶颈与加速思路最后聊聊性能。其实去重这个操作在数据库内部并不是免费的午餐它往往隐含着排序或哈希的开销。数据量小的时候感觉不到一旦表上千万行DISTINCT和GROUP BY慢起来能让你怀疑人生。5.1 去重为什么慢排序和哈希的开销DISTINCT的实现方式通常是两种要么基于排序去重要么基于哈希去重。无论哪种都需要把目标列的所有值读出来进行比较、消除重复最后才返回结果。这意味着如果表很大一次全表扫描几乎是跑不掉的如果多个列去做DISTINCT开销还会成倍上升。GROUP BY也是同理它需要按分组列把数据归堆本质上也是一次内部排序或哈希聚合。如果你的分组列上没有索引数据库就得先建一个临时结构来存放中间结果磁盘I/O一旦参与进来查询速度会断崖式下跌。我踩过的一个典型例子是一张千万级订单表业务方想统计所有出现过下单动作的用户ID。第一版SQL直接写SELECT DISTINCT user_id FROM orders WHERE order_status PAID;线上跑了快两分钟数据库CPU直接被拉满。后来排查执行计划发现全表扫描、杂乱的排序操作全出来了。解决办法其实很简单在order_status和user_id上建联合索引让索引直接覆盖查询条件与结果列查询就变成了索引扫描快到毫秒级。5.2 用临时表和中间结果缓存来降重有些去重场景非常复杂比如要先关联多张表、做清洗再分组去重。如果整个查询一步写完优化器很可能会选择一条低效的执行路径。这种情况下我惯用的手法是分步走把中间结果落到临时表分阶段处理。-- 第一步把关联完的数据写入临时表 CREATE TEMPORARY TABLE tmp_order_user AS SELECT o.order_id, o.user_id, o.amount, u.user_name FROM orders o LEFT JOIN users u ON o.user_id u.user_id; -- 第二步在临时表上做去重分析 SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM tmp_order_user GROUP BY user_id;为什么中间结果落临时表有帮助因为第一步的关联结果可以先物化下来第二步的GROUP BY就不用再反复回源表做JOIN。而且临时表可以独立建索引比如在user_id上建索引后再执行GROUP BY速度提升非常明显。这个经验在复杂报表任务里救了我很多次。5.3 大数据量下的降级与替代思路当数据量达到数亿行甚至更高量级时DISTINCT和GROUP BY的精确去重会变得极其昂贵。此时要思考业务真的需要那么精确吗典型例子是统计UV去重访客数。如果要精确值需要维护一份全量的用户ID集合内存和存储压力都很大。很多画像系统、流量分析系统已经改用概率算法比如HyperLogLog用极小的内存代价换取近似的去重计数误差通常在1%以内业务上完全可以接受。即便不引入大数据组件日常SQL里也有一个常见优化思路在MySQL 8.0和SQL Server等新版本里更高效合理使用索引和索引覆盖。你可以尝试把GROUP BY的字段放在复合索引的最左前缀来控制执行计划走向减少排序。我在优化一个慢SQL时只调整了列顺序和索引结构查询耗时就从8秒降到了0.3秒效果立竿见影。最后还得多说一句任何去重方案的性能都要以实际数据量和执行计划为准别盲目套用网上所谓的优化大法。每个库的版本、统计信息、数据分布都不一样最好的做法永远是先EXPLAIN看执行计划再针对性地建索引或改写。做过几次大规模去重和清理之后我的体会是SQL本身并不难难的是准确理解业务诉求以及在正确的地方用正确的手段。每当你准备写DISTINCT或GROUP BY之前先花一分钟问自己——这个重复到底是什么含义我要保留哪一条这个操作会不会误伤数据多问几个问题可能比你多学十个函数都管用。
上周我接到一个挺有意思的需求业务方甩给我一张订单表说了句帮忙去个重然后就去开会了。等我打开表一看当场愣住——这张表里既有多行完全一样的数据又有同一个用户多条不同状态的记录还有用户和商品的组合反复出现。当时我意识到一件事在SQL世界里去重这个词至少藏着四五种完全不同的语义每一种对应不同的写法搞混了轻则结果不对重则直接把线上数据搞出大麻烦。这篇文章就把我这些年处理数据去重和唯一值提取的实战经验完整梳理一遍。从需求拆解、手段选型、高频场景配方到生产环境清理重复数据的完整链路再到性能优化思路都会展开来讲。不管你是刚接触SQL的新人还是经常和数据打交道的分析师、后端开发这篇内容应该都能给你一些可以直接抄作业的写法以及很多文档里不会写的为什么和别踩我踩过的坑。1. 先别急着写SQL拆清重复的四种真实语义很多人一听到去重两个字条件反射就是DISTINCT或者GROUP BY。但根据我的经验拿到需求先别动手第一步永远是搞清楚对方口中的重复到底是指什么。不同语境下的重复处理逻辑天差地别甚至有些看起来像重复的数据根本不应该被去掉。1.1 完全重复行每一列都一模一样这是最简单、最教科书化的场景几行数据的每一列值都完全相同你想把它们压缩成一行。这种重复通常出现在数据导出、多表合并、UNION ALL操作之后。举个具体例子两张分表汇总出以下数据-- 两张分表结构相同UNION ALL后可能产生完全重复行 SELECT user_id, order_no, amount FROM orders_2023 UNION ALL SELECT user_id, order_no, amount FROM orders_2024;如果两条订单在两个表中都出现过UNION ALL的结果里就会出现两行完全一样的数据。此时DISTINCT是最直接的解法SELECT DISTINCT user_id, order_no, amount FROM ( SELECT user_id, order_no, amount FROM orders_2023 UNION ALL SELECT user_id, order_no, amount FROM orders_2024 ) t;但这里藏着一个特别隐蔽的坑如果表结构里带有自增主键id那么任何两行的id都不可能相同DISTINCT永远去不掉所谓重复的整行数据。很多新手在宽表里去重失败就是因为没意识到完全重复行的定义里必须把所有列都算进去。换句话说只有当你确认表里没有唯一标识符或者你明确豁免了某些列DISTINCT才有意义。1.2 业务维度重复主键相同但其他值在变第二种重复在实际业务中更加普遍从业务上看这确实是同一条记录但它对应着多条不同版本的数据比如订单的状态变更记录、账户的登录日志、员工岗位的调整历史。拿订单举例同一笔订单order_no ORD2024001可能出现三行order_nostatusupdated_atORD2024001已支付2024-03-01 10:00:00ORD2024001已发货2024-03-02 14:30:00ORD2024001已签收2024-03-05 09:12:00如果你接到需求说把这个订单表去重一个订单只留一行你需要立刻追问保留哪一行是最新状态的还是最早创建的还是根据某个优先级规则来选这时候DISTINCT根本无能为力——因为这三行没有任何一列完全一样但它们确实共享同一个业务维度订单号。这类问题正确的打开方式是分组排序后取第一条窗口函数ROW_NUMBER()就是为此而生的WITH ranked_orders AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY order_no ORDER BY updated_at DESC) AS rn FROM orders ) SELECT * FROM ranked_orders WHERE rn 1;这段SQL的逻辑是按order_no把数据分组组内按updated_at降序排列然后给每一行编号最后只保留每组编号为1的那行。我后面会专门拆解这类写法这里先记住一个核心思路只要需求里有每组保留一条这种说法大概率就要用窗口函数而不是DISTINCT。1.3 组合重复单看不重合起来重了第三种情况更迷惑人。某个用户的ID单独看不重复某个商品的ID单独看不重复但(user_id, product_id)这个组合却在表里出现了两次甚至多次。常见场景是加购表、收藏表、浏览记录表。用户可以在不同时间反复对同一个商品执行同一操作于是用户商品的组合就产生了多条记录。处理组合重复的出发点和前面完全不同你需要先判断组合的业务语义。如果业务上(user_id, product_id)本身就应该唯一那么这些重复属于脏数据需要清洗如果业务上允许重复那你的任务可能就是查询时把重复组合压平。给一个查询时的压平方案SELECT user_id, product_id, MAX(created_at) AS last_operated_at FROM user_product_actions GROUP BY user_id, product_id;这里用GROUP BY配合聚合函数本质上是把同一组合下的多行聚合成了一个业务维度的汇总行。注意这个写法不像ROW_NUMBER()那样可以保留完整行但它胜在简洁、性能好在很多只需要维度加汇总值的场景里非常实用。1.4 JOIN膨胀结果集重复不是表里真的重复最后一种也是日常排查里最容易背锅的一种源表本身没有一条脏数据但你的查询结果却出现了大量重复行。问题几乎总是出在JOIN上。举个例子员工表和部门表做关联查询SELECT e.emp_id, e.emp_name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.dept_id;如果departments表里有两个技术部或者技术部下面存在部门拆分的历史数据那么一个员工就可能被关联出多行结果。员工表数据没问题部门表数据也没问题但结果集膨胀了。这提醒我们去重之前先搞清楚重复到底发生在哪个环节。纯DISTINCT也许能救急但如果膨胀的原因是业务确实存在一对多关系更好的做法是先聚合子查询再关联或者明确你要的粒度是什么。我见过太多人在一个大JOIN外面套一层DISTINCT结果数据量翻了很多倍拖慢了整个查询其实应该回到源头去修正关联逻辑。2. 从DISTINCT到窗口函数去重手段的边界测试搞清楚了需求语义下一步就是选手段。DISTINCT、GROUP BY、ROW_NUMBER()是SQL去重的三件套但很多人并没有真正理解它们之间的边界经常拿A工具解决B问题然后抱怨SQL怎么这么难用。这节我带你把三种手段的真实边界摸一遍。2.1 DISTINCT简单直接但它没得选DISTINCT的核心逻辑是对查询结果做一次全量去重相同行只出现一次。它的优点是语法简洁、语义直观适用于前面说的完全重复行场景。但它的短板也非常明显你无法控制保留哪一行。一旦整个查询结果里有某几个列不同DISTINCT就认为它们不是重复行于是去重就失效了。还有一个容易被忽略的细节DISTINCT不仅可以用在SELECT后还能配合COUNT用-- 统计有多少个不同的用户下过单 SELECT COUNT(DISTINCT user_id) AS active_user_cnt FROM orders;这是唯一值提取最经典的应用。很多报表里要统计活跃用户数、去重UV都靠这一句。它的好处是不会真的把数据取出来再数执行引擎通常能走更优化的路径。不过若是需要DISTINCT多列组合你要明确知道它在以整行组合为粒度去重SELECT DISTINCT user_id, product_id FROM orders;上面这句的意思是查询所有下过单的用户-商品组合而不是把user_id去重再管product_id。这个微妙区别经常让初学者踩坑——以为结果里所有user_id都唯一其实唯一性作用在组合上。2.2 GROUP BY去重的同时也在聚合GROUP BY本质上是把数据按某些维度分桶。如果只查分桶的维度列它就能实现去重SELECT user_id FROM orders GROUP BY user_id;这和SELECT DISTINCT user_id FROM orders结果一样但执行路径可能不同。GROUP BY真正的价值在于它允许你同时计算每个桶的聚合指标比如每个用户的下单次数、总金额、最近下单时间SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount, MAX(created_at) AS last_order_time FROM orders GROUP BY user_id;理解GROUP BY的关键在于当你把非分组列放进SELECT时SQL引擎其实是在强制做聚合运算。假如你写SELECT user_id, order_no FROM orders GROUP BY user_id在MySQL的某些宽松模式下它会返回一个随机值而在SQL Server、PostgreSQL里则直接报错。很多人觉得GROUP BY去重不顺手多半是因为没搞懂这一层约束。实际项目中我会根据要不要同步算指标来决定选DISTINCT还是GROUP BY。只要逻辑里带统计汇总每个X的YGROUP BY几乎是唯一合理的选择。2.3 ROW_NUMBER()把保留哪一条的控制权拿回来这三件套里ROW_NUMBER()是最灵活、也是最能体现功力的一个。它是窗口函数可以在分组的基础上对组内排序然后赋一个序号。配合外层查询过滤序号为1的记录你就能实现每组保留一条至于是哪一条我自己说了算。先看最基础的语法结构ROW_NUMBER() OVER ( PARTITION BY col_for_group ORDER BY col_for_order DESC ) AS rnPARTITION BY负责分组ORDER BY负责组内排序。排序的第一名就是你要保留的那个唯一值。一个随机应变的设计点在于ORDER BY可以同时排多个字段以便在时间相同的情况下继续用ID倒序兜底ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY created_at DESC, order_id DESC ) AS rn这个写法看起来简单但实际项目里有大量变体需求靠它完成。比如取每个商品的最新价格取每个客户的最近一次投诉取每个班级总分第一的学生——本质上都是同一套模板先分区排序编号再过滤rn 1。我还经常会遇到一个困惑ROW_NUMBER()和RANK()、DENSE_RANK()到底怎么选简单说ROW_NUMBER()保证每条记录序号唯一哪怕排序值相同也会强分1、2、3适合只要有且仅有一条记录的场景RANK()则允许并列如果两个用户的时间完全一样会同时拿到第一DENSE_RANK()也是处理并列的但后续序号不会因并列而跳号。做去重保留一条时我几乎只用ROW_NUMBER()因为它能确保不出现并列导致重复。2.4 一份可以存进收藏夹的选型表把上面讲的整理成一张对照表我每次给团队培训都用它需求描述推荐手段核心理由去掉完全重复的行DISTINCT语义简单直接执行效率高统计不重复值的数量COUNT(DISTINCT ...)一条SQL直接出指标避免冗余数据搬运按维度分组并计算汇总指标GROUP BY 聚合函数去重和统计一次完成逻辑清晰每组保留一条特定记录最新、最早、优先级最高ROW_NUMBER() 外层过滤可精确控制保留哪一行覆盖复杂业务规则多列组合唯一性判断GROUP BY多列 或DISTINCT多列粒度和语义必须与业务诉求保持一致选型时记住一个原则当你的需求只是看看到底有哪些不同的值用最轻的DISTINCT当需求变成每个不同值对应的汇总情况或者每个分组里挑一条就要果断升级手段。3. 高频业务场景的SQL配方从取最新到防重复插入工具只是手术刀真正考验水平的是面对真实需求时能不能快速配出正确的SQL。这一节我会拆解四个出现频率极高的去重/唯一值提取场景每个都给出完整写法并解释每一步的原因。3.1 每组保留一条取每人最新订单这是一个出现频率极高的需求几乎所有带业务系统的公司都会遇到一张订单表里有用户的多个订单你要找出每个用户的最近一次下单记录。先看完整SQLWITH ranked_orders AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY order_date DESC, created_at DESC ) AS rn FROM orders ) SELECT order_id, user_id, order_date, amount FROM ranked_orders WHERE rn 1;拆开来看PARTITION BY user_id把订单按用户分组ORDER BY order_date DESC, created_at DESC保证同一天下的单也能按创建时间排出先后ROW_NUMBER()给每组内的记录编上号外层WHERE rn 1把每组最靠前的那条捞出来。这个写法的好处是你不需要在SELECT里写一堆复杂的聚合原始行的所有字段都可以直接带出来。我经常在数据看板、用户生命周期分析、风控标签里复用这套结构。这里有两个值得注意的细节。第一如果表非常大窗口函数需要把所有分区排序结果算出来才能过滤最好在PARTITION BY和ORDER BY涉及的字段上建立索引否则性能会很难看。第二如果业务上允许同一个人同一个时刻下了多笔单你需要再往ORDER BY里加一列比如订单号降序保证编号唯一否则还是可能拿到多行。3.2 多表关联产生的膨胀去重假设你有一张员工表和一张部门表需求是查出每个员工及其所属部门名称。但部门表里存在历史拆分一个部门ID对应多条部门名称记录直接JOIN后每个员工都出现了好几行。-- 这张写法会产生重复行员工被关联到多条部门记录 SELECT e.emp_id, e.emp_name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.dept_id;有两条路可以走。第一条是在外层加DISTINCT简单粗暴但如果员工还有很多其他信息列DISTINCT会把整行拿来做唯一性判断效率堪忧。第二条路是先收敛再关联即先在子查询里把部门表按部门ID去重只保留一条再和员工表关联SELECT e.emp_id, e.emp_name, d.dept_name FROM employees e LEFT JOIN ( SELECT dept_id, MAX(dept_name) AS dept_name FROM departments GROUP BY dept_id ) d ON e.dept_id d.dept_id;这就是我前面说的先搞清楚重复是因何产生的再决定在哪一层处理。联合查询膨胀的重复优先考虑从源头上把一对多的多给压成一而不是在结果集外面硬套去重。这样不仅结果正确性能也更可控。3.3 插入前的唯一性校验与幂等处理再说一个和唯一值提取密切相关的场景往表里写入数据时怎么防止重复插入方案一在数据库层面建立唯一约束。比如要求(user_id, product_id)组合唯一直接建唯一索引或唯一约束重复插入时数据库会拒绝。这是最好的防线但现实里很多表已经有存量脏数据线上不能直接加约束这时候就需要用SQL做插入前校验。通用写法是-- 检查是否存在相同组合的数据 SELECT COUNT(*) FROM user_product_actions WHERE user_id 1001 AND product_id 8888;如果COUNT(*) 0再执行插入。但这套逻辑在高并发下会出问题两个请求同时查到不存在然后同时插入仍然造成重复。真正稳妥的做法还是要靠数据库唯一约束SQL层面的校验只适合低并发后台操作这一点大家在设计时要心里有数。另一种常见思路是将重复检查融合到插入语句里。MySQL里可以用INSERT IGNORE重复时静默跳过PostgreSQL里可以用ON CONFLICT DO NOTHING或ON CONFLICT DO UPDATESQL Server里可以用MERGE。写法各不相同但核心思想一致让数据库在原子操作里完成存在则不插入不存在则插入避免应用层先查后插的竞态问题。3.4 脏数据清洗视角下的近似去重最后一个场景这些年在数据仓库项目里见得特别多源系统的数据质量太差同一个客户在不同时间段里的姓名可能带空格、前后不一致、大小写不同甚至全角和半角混用。这时候按原始字符串去重根本压不住。常用的做法是先清洗再排重。比如对字符串做标准化后再判断唯一性SELECT UPPER(TRIM(REPLACE(customer_name, , ))) AS normalized_name, COUNT(*) FROM customers GROUP BY UPPER(TRIM(REPLACE(customer_name, , )));TRIM去掉首尾空格REPLACE去掉中间空格UPPER统一大小写这样张 三、张三、zhang san等形态就有机会被归并到一起。SQL Server里还能用LTRIM、RTRIM、LOWER做类似处理。需要提醒的是字符串清洗要克制。清洗过度会误合并一些本不该合并的数据。比如人名里的OBrien和Obrien严格来说可能是两个人清洗规则一宽松就容易出事故。我在实际项目里会先把清洗后的结果放到临时表人工抽样看归并效果确认没问题再用来做唯一键匹配绝不能一上来就直接用清洗结果覆盖原始数据。4. 生产环境删除重复数据的完整链路前面说的都是查询场景接下来要聊一个更敏感的实操真的去把表里的重复数据删掉。这是很多人在生产环境里不敢动、又不得不动的硬骨头。我自己的原则始终是先查后删、备份先行、分批执行。这一节给你一套可以直接套用的完整链路。4.1 删除前的重复情况体检动手之前先回答三个问题哪些列定义了重复表里一共多少组重复每组重复多少条这三个问题不搞清楚后面的DELETE语句很容易误伤。对于按某一组字段判重的场景用一个GROUP BY ... HAVING就能体检SELECT user_id, product_id, COUNT(*) AS cnt FROM user_product_actions GROUP BY user_id, product_id HAVING COUNT(*) 1 ORDER BY cnt DESC;再加上一个汇总统计方便评估影响范围SELECT COUNT(*) AS dup_group_cnt, SUM(cnt - 1) AS extra_rows_to_delete FROM ( SELECT user_id, product_id, COUNT(*) AS cnt FROM user_product_actions GROUP BY user_id, product_id HAVING COUNT(*) 1 ) t;这里extra_rows_to_delete表示除了每组保留一条之外还要删掉多少行。这个数字直接影响你的变更评估——如果一次要删上百万行那就要考虑分批操作而不是一把梭。4.2 保留一条记录的DELETE写法现在进入核心部分。假设我们要为user_product_actions表去重每个(user_id, product_id)组合只保留一条记录。表里有主键id这就给了我们精确保留的依据每组保留id最小的那一行其余删除。思路最清晰的方式依然是窗口函数WITH ranked_actions AS ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY user_id, product_id ORDER BY id ) AS rn FROM user_product_actions ) DELETE FROM user_product_actions WHERE id IN ( SELECT id FROM ranked_actions WHERE rn 1 );先给每组按id排序编号rn 1的即为多余行全都删掉。这种写法在SQL Server、PostgreSQL、MySQL 8.0里都能用逻辑直观维护成本低。如果数据库版本不支持CTE比如MySQL 5.7或更老可以用自连接DELETE a FROM user_product_actions a JOIN ( SELECT MIN(id) AS keep_id FROM user_product_actions GROUP BY user_id, product_id ) k ON a.user_id k.user_id AND a.product_id k.product_id WHERE a.id k.keep_id;这个写法的思路是先找出每个组里要保留的最小id然后删除组内所有id大于这个保留id的行。MySQL里支持这种多表DELETE语法SQL Server里则要换成DELETE a FROM ... JOIN ...的形式具体细节可以查对应版本的语法文档。4.3 事务、备份与分批操作拿什么保证线上安全删除数据最怕的不是SQL写错而是操作流程没兜底。我给团队定的规矩很简单但每条都是拿教训换来的第一必须先备份。最简单的做法是先把涉及的行SELECT出来存到一张备份表CREATE TABLE user_product_actions_bak_20240301 AS SELECT * FROM user_product_actions;这一步在大表上会有点耗时但对生产环境来说这个成本必须付。万一删错了还能从备份表恢复。第二DELETE操作务必放进事务里执行。SQL Server和PostgreSQL都有显式事务MySQL的InnoDB同样支持START TRANSACTION; DELETE FROM user_product_actions WHERE id IN (...); -- 先不提交检查影响行数是否合理 -- 确认无误后 COMMIT;先START TRANSACTION执行完DELETE后不急着提交可以先看看影响行数再查一遍重复数据是否清干净。如果发现问题直接ROLLBACK一切回到原点。第三影响行数大的时候分批删。大批量DELETE会长时间锁表影响线上业务。可以加上LIMIT循环执行多次每批删5000行左右中间稍微停顿。MySQL示例DELETE FROM user_product_actions WHERE id IN ( SELECT id FROM ranked_actions WHERE rn 1 ) LIMIT 5000;反复执行直到影响行数为0。这样每条UPDATE/DELETE语句的锁的范围都非常可控业务侧基本无感。5. 去重查询的性能瓶颈与加速思路最后聊聊性能。其实去重这个操作在数据库内部并不是免费的午餐它往往隐含着排序或哈希的开销。数据量小的时候感觉不到一旦表上千万行DISTINCT和GROUP BY慢起来能让你怀疑人生。5.1 去重为什么慢排序和哈希的开销DISTINCT的实现方式通常是两种要么基于排序去重要么基于哈希去重。无论哪种都需要把目标列的所有值读出来进行比较、消除重复最后才返回结果。这意味着如果表很大一次全表扫描几乎是跑不掉的如果多个列去做DISTINCT开销还会成倍上升。GROUP BY也是同理它需要按分组列把数据归堆本质上也是一次内部排序或哈希聚合。如果你的分组列上没有索引数据库就得先建一个临时结构来存放中间结果磁盘I/O一旦参与进来查询速度会断崖式下跌。我踩过的一个典型例子是一张千万级订单表业务方想统计所有出现过下单动作的用户ID。第一版SQL直接写SELECT DISTINCT user_id FROM orders WHERE order_status PAID;线上跑了快两分钟数据库CPU直接被拉满。后来排查执行计划发现全表扫描、杂乱的排序操作全出来了。解决办法其实很简单在order_status和user_id上建联合索引让索引直接覆盖查询条件与结果列查询就变成了索引扫描快到毫秒级。5.2 用临时表和中间结果缓存来降重有些去重场景非常复杂比如要先关联多张表、做清洗再分组去重。如果整个查询一步写完优化器很可能会选择一条低效的执行路径。这种情况下我惯用的手法是分步走把中间结果落到临时表分阶段处理。-- 第一步把关联完的数据写入临时表 CREATE TEMPORARY TABLE tmp_order_user AS SELECT o.order_id, o.user_id, o.amount, u.user_name FROM orders o LEFT JOIN users u ON o.user_id u.user_id; -- 第二步在临时表上做去重分析 SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM tmp_order_user GROUP BY user_id;为什么中间结果落临时表有帮助因为第一步的关联结果可以先物化下来第二步的GROUP BY就不用再反复回源表做JOIN。而且临时表可以独立建索引比如在user_id上建索引后再执行GROUP BY速度提升非常明显。这个经验在复杂报表任务里救了我很多次。5.3 大数据量下的降级与替代思路当数据量达到数亿行甚至更高量级时DISTINCT和GROUP BY的精确去重会变得极其昂贵。此时要思考业务真的需要那么精确吗典型例子是统计UV去重访客数。如果要精确值需要维护一份全量的用户ID集合内存和存储压力都很大。很多画像系统、流量分析系统已经改用概率算法比如HyperLogLog用极小的内存代价换取近似的去重计数误差通常在1%以内业务上完全可以接受。即便不引入大数据组件日常SQL里也有一个常见优化思路在MySQL 8.0和SQL Server等新版本里更高效合理使用索引和索引覆盖。你可以尝试把GROUP BY的字段放在复合索引的最左前缀来控制执行计划走向减少排序。我在优化一个慢SQL时只调整了列顺序和索引结构查询耗时就从8秒降到了0.3秒效果立竿见影。最后还得多说一句任何去重方案的性能都要以实际数据量和执行计划为准别盲目套用网上所谓的优化大法。每个库的版本、统计信息、数据分布都不一样最好的做法永远是先EXPLAIN看执行计划再针对性地建索引或改写。做过几次大规模去重和清理之后我的体会是SQL本身并不难难的是准确理解业务诉求以及在正确的地方用正确的手段。每当你准备写DISTINCT或GROUP BY之前先花一分钟问自己——这个重复到底是什么含义我要保留哪一条这个操作会不会误伤数据多问几个问题可能比你多学十个函数都管用。
