统计与汇总重复记录SQL Server 的这一仗我打了很久每个做数据处理的人早晚都会碰到这样一个需求查重复记录。我在接手一个老业务系统的时候某个核心订单表里有上百万行的数据光查有没有重复这个看似简单的问题就把我折腾了整整一轮。后来我想明白了所谓统计与汇总重复记录不是一个简单地去重问题而是一整套方法论——先给重复定义清楚边界再用合适的工具把重复记录找出来然后按业务口径去汇总分析最后才是清理或者合并。这个完整链路里每一个环节都有不少坑。写这篇文章就是把我在 MS SQL Server 上踩过的坑、用顺手的方案、以及事后总结出来的判断依据一次性讲清楚。1. 重复记录的定义方式先搞清楚你要的是哪一种重复不少刚接触这个问题的朋友第一反应都是重复还不简单两行数据一模一样就是重复。但在实际业务里这种完全一致的重复反而很少见更多情况下是某几个关键字段一样。1.1 完全重复与部分字段重复的本质区别完全重复是指整行所有字段的值都相同这种情况通常源于数据导入脚本的 bug或者上游接口重复推送。部分字段重复则是业务上真正需要关注的比如同一个人身份证号出现了多次但手机号可能不同收货地址也不同——这时候重复的定义就变成了哪一个字段组合能够唯一标识一条记录。在我那个订单表里订单号本身就是主键但实际发现的问题是同一订单号下面挂了多条明细每条明细的产品编码一样只是数量被拆成几行。这时候重复的定义就不是订单号而是订单号 产品编码这个组合。搞清楚定义方式是写好后续所有 SQL 的前提。定义没定清楚后面统计出来的数字谁敢信1.2 关联键的粒度选择统计级别的基石在动手写 GROUP BY 之前必须先想明白一个问题统计的粒度是行级还是业务键级举个例子现在要统计一张用户表里重复的手机号如果按手机号分组那统计出来的是有多少个手机号出现了重复如果按手机号 姓名分组统计出来的是有多少个组合出现了重复。这两种口径的数字可能差得很远。我自己的习惯是先确认业务文档里的唯一约束定义没有文档就找业务方当面确认绝不靠猜。因为 SQL 写起来简单改起来也简单最怕的是统计结果被拿去当决策依据之后才发现在定义阶段就错了那时候返工的代价就大了。2. 用 GROUP BY 完成基础统计从行数到分组计数的第一板斧确认完重复的定义后下一步就是动手写 SQL。对于大多数重复记录统计需求HASH 到 GROUP BY 是最好的入门方案。它的逻辑非常直白按照你定义的业务键分组然后统计每组有多少条记录。2.1 最基础的统计语句COUNT、SUM 与 AVG 的组合假设我们要统计某个订单明细表中同一订单号下出现了多少个产品编码以及每个产品编码的总数量可以这样写SELECT 订单号, 产品编码, COUNT(*) AS 记录条数, SUM(数量) AS 总数量, AVG(数量) AS 平均数量 FROM 订单明细表 GROUP BY 订单号, 产品编码 ORDER BY 记录条数 DESC;这段 SQL 做两件事第一按照订单号和产品编码分组第二对每组内记录条数、数量总和、数量平均值做聚合。运行结果里记录条数大于 1 的分组就是需要关注的重复记录。COUNT() 统计的是组内所有行的数量包括 NULL 值。如果只想统计某个字段非空的记录数用 COUNT(字段名) 即可。这两个在重复记录场景下结果可能完全不同——比如某字段大量为空COUNT() 显示有 10 条重复COUNT(该字段) 却只显示 3 条这里需要根据业务逻辑选择正确的计数方式。2.2 HAVING 子句筛选重复组的正确姿势上面那段 SQL 会把所有分组都列出来如果表里有一百万行看到的分组可能上万个里面绝大多数是正常记录。这时候就需要 HAVING 子句来过滤出真正有问题的分组。SELECT 订单号, 产品编码, COUNT(*) AS 记录条数 FROM 订单明细表 GROUP BY 订单号, 产品编码 HAVING COUNT(*) 1 ORDER BY 记录条数 DESC;WHERE 和 HAVING 的区别很多教程讲过无数遍但实际操作中还是容易混WHERE 是在分组之前过滤数据行HAVING 是在分组之后过滤分组。换句话说如果你只想统计某个时间段内的重复得先 WHERE 时间段条件再 GROUP BY 再 HAVING如果你想把出现次数太少的组也排除掉只能在 HAVING 里加条件。这里有个容易被忽略的性能细节HAVING COUNT(*) 1 这个条件数据库引擎必须先把所有分组算出来然后才能过滤无法走索引直接跳过。数据量大的时候这一步是会吃掉不少时间的后面第五部分我会专门说怎么优化。3. ROW_NUMBER 窗口函数给重复记录编上号再逐个处理GROUP BY HAVING 能告诉我们哪里重复了但有一个天生的短板它只能给出聚合后的结果看不到组内每一条明细记录的具体情况。如果你想定位到这 10 条重复记录里哪几条是多余的GROUP BY 就无能为力了。这时候要用窗口函数。3.1 窗口函数处理重复的核心逻辑ROW_NUMBER() 的基本作用就是给每一行生成一个序号这个序号是在某个分组内、按某个顺序来排序的。对重复记录来说最经典的做法是这样的SELECT 订单号, 产品编码, 数量, ROW_NUMBER() OVER ( PARTITION BY 订单号, 产品编码 ORDER BY 记录创建时间 DESC ) AS 组内序号 FROM 订单明细表;PARTITION BY 后面跟的字段就是我们之前定义的业务键ORDER BY 后面跟的字段决定了同组内每一行的先后顺序。按记录创建时间倒序最新的一条序号就是 1其余的都是 2、3、4……这样一来组内序号大于 1 的记录理论上就是保留一条之后多余的那部分。这是所有重复记录处理中最常用的一个模式。相比之下DENSE_RANK() 在处理重复值时会把相同值排在相同名次而 ROW_NUMBER() 永远给每行唯一的序号——正是因为这个确定性它最适合用来标记重复记录。3.2 扩展场景不只查出来还要保留需要的明细很多时候不只要找出重复组还要从每组重复里挑出最应该保留的那条。比如同一客户有多条订单记录我们要保留金额最大的一条。这种需求用窗口函数封装一步就能做WITH 带序号的记录 AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY 客户ID ORDER BY 订单金额 DESC ) AS 组内序号 FROM 订单表 ) SELECT * FROM 带序号的记录 WHERE 组内序号 1;这里用到了 CTE公共表表达式把带序号的查询结果先存成临时结果集然后再筛选。看过这个写法之后你会发现它可以解决掉我上面说的一半问题——查重复、选最优、保留唯一记录全部都能在一个查询里完成。4. 实战从找出重复到汇总统计的完整流程到这一节我们把前面所有技术点放到一个完整的业务场景里跑一遍。设一个实际场景电商系统里的订单明细表表名叫 OrderDetails包含字段 OrderID订单号、ProductID产品编码、Quantity数量、CreateTime创建时间。业务方反馈同一张订单里同一产品出现了多条明细需要统计哪些订单受影响、重复情况有多严重。4.1 第一步确认重复定义的统计口径跟业务方确认后重复定义是同一个 OrderID 下同一个 ProductID 出现多次。注意这里并不是说整行数据完全一样因为 CreateTime 可能不同所以行级完全重复在这个场景根本不适用。我建议把口径确认这一步做成了一个可复用的检查脚本SELECT OrderID, ProductID, COUNT(*) AS cnt, COUNT(DISTINCT CreateTime) AS cnt_create_time FROM OrderDetails GROUP BY OrderID, ProductID HAVING COUNT(*) 1 ORDER BY cnt DESC;为什么要加 COUNT(DISTINCT CreateTime)因为在确认口径时业务方经常会说这俩应该是一条记录啊而创建时间不同恰恰可能说明两次下单行为不是同一时间发生的。加上这个字段能快速验证我们和业务方对重复的理解是否一致。4.2 第二步按订单维度汇总重复的严重程度确认了重复定义之后业务方最关心的问题从有没有重复变成哪些订单重复最严重。这时候统计的粒度要从订单产品升级到订单SELECT OrderID, COUNT(DISTINCT ProductID) AS 重复产品数, SUM(重复次数) AS 重复明细总条数 FROM ( SELECT OrderID, ProductID, COUNT(*) AS 重复次数 FROM OrderDetails GROUP BY OrderID, ProductID HAVING COUNT(*) 1 ) AS 重复组 GROUP BY OrderID ORDER BY 重复明细总条数 DESC;内层子查询先找出所有重复分组外层再把这些重复分组按订单号汇总。这样得到的报表里每一行就是一个订单后面的数字直接反映这个订单的重复严重程度。4.3 第三步计算重复率并输出报告汇总出了重复记录的数量之后还要计算一个更直观的指标——重复率。要让这个数字有参考价值分母必须定义清楚。我认为最合适的分母是总记录数而不是分组数因为重复记录归根结底是行层面的问题。WITH 总体统计 AS ( SELECT COUNT(*) AS 总记录数, SUM(CASE WHEN 重复组.重复次数 1 THEN 1 ELSE 0 END) AS 重复记录数 FROM OrderDetails LEFT JOIN ( SELECT OrderID, ProductID, COUNT(*) AS 重复次数 FROM OrderDetails GROUP BY OrderID, ProductID ) AS 重复组 ON OrderDetails.OrderID 重复组.OrderID AND OrderDetails.ProductID 重复组.ProductID ) SELECT 总记录数, 重复记录数, CAST(重复记录数 AS FLOAT) / 总记录数 * 100 AS 重复率百分比 FROM 总体统计;这个查询跑出来之后如果重复率只有 0.1%那说明是偶发问题修几条数据就行如果重复率到了 30%那就得回头查是不是上游接口推送逻辑出了问题。统计的价值就在这里——它驱动的不是 SQL 本身而是决策。5. 大数据量下的性能优化别让 count 慢到没人想跑前面所有查询在数据量几万、几十万的时候跑起来没什么感觉。一旦表里数据到了几千万行同样的 SQL 跑半小时都可能出不来这时候就必须上优化手段。我在这部分总结的是自己压测之后沉淀下来的经验。5.1 索引设计什么样的索引能加速重复统计很多人以为 GROUP BY 和 HAVING 只能靠全表扫描其实索引能帮上大忙。GROUP BY 后面的字段如果有合适的索引支持数据库引擎可以直接通过索引扫描分组而不需要先全表读再排序。对于我前面那些查询一个组合索引基本上就够了CREATE NONCLUSTERED INDEX IX_OrderDetails_Order_Product ON OrderDetails (OrderID, ProductID) INCLUDE (Quantity, CreateTime);这个索引把分组键放在索引的最左边查询时数据库可以直接按 OrderID ProductID 的顺序扫描天然就是分好组的顺序。INCLUDE 把要查询的字段都包含进来可以避免回表操作。建索引之前要注意一点如果表本身已经有主键是 OrderID ProductID那就不需要重复建这个索引了。可以先查一下现有索引再动手别做无用功。5.2 避免昂贵的排序GROUP BY 与 DISTINCT 的代价很多情况下查询慢不是慢在分组本身而是慢在排序。GROUP BY 默认要对分组键排序才能合并同类项DISTINCT 也是一样。如果分组键很多、数据量又大这个排序开销非常可观。一个替代方案是使用 HASH 聚合。数据库查询优化器一般会自动判断是否改用 HASH 匹配但有时候索引缺失会导致优化器选了排序方案。可以通过执行计划观察”Sort”操作符是否存在如果看到明显的排序节点就要考虑是不是索引不够合适。还有一种思路是逻辑优化先在大表和条件较少的小表之间做过滤缩小参与分组的行数再 GROUP BY。比如先按日期范围筛选数据只统计昨天的重复而不是全表统计所有历史数据。这个调整看起来简单但对性能的影响是数量级的。5.3 分批处理策略几千万行一次算不完实在无法避免大范围分组统计时就不能指望一个查询跑完了。分批处理的常规做法是加一个日期字段作为分批维度每次只处理一天或一周的数据结果存入临时表最后合并-- 每天处理一批把结果汇总到统计表 DECLARE StartDate DATE 2024-01-01; DECLARE EndDate DATE 2024-01-31; WHILE StartDate EndDate BEGIN INSERT INTO 重复记录统计表 (OrderID, ProductID, 重复次数, 统计日期) SELECT OrderID, ProductID, COUNT(*) FROM OrderDetails WHERE CreateTime StartDate AND CreateTime DATEADD(DAY, 1, StartDate) GROUP BY OrderID, ProductID HAVING COUNT(*) 1; SET StartDate DATEADD(DAY, 1, StartDate); END;循环逐天处理的好处有三个第一单次查询的数据量可控第二出错了只需要重跑某一天不需要全表重来第三对线上的锁影响小。缺点是整体耗时可能比一次性查询长一点但可维护性高很多——每次处理完还可以顺手记录一批日志方便追溯。在我自己处理千万级订单明细时最常用的方案就是这种按日期分批的循环再配合上一小节提到的组合索引整体跑完几千万行大概从原来的一小时降到了十几分钟。6. 清理与后续维护统计完了接下来怎么办统计和汇总本身不是终点。大多数情况下业务方要的是把重复的数据处理干净。这里说的清理不一定就是 DELETE要根据业务场景选对方案。6.1 三种处理方案删除、合并、标记删除是默认想法但不一定对。举个例子重复的订单明细如果直接 DELETE原始单据审计链就断了如果把重复记录合并成一条数量加起来总金额不变这是数据修复如果不确定是否保留先把重复记录标记为无效等到确认后再处理这是保守方案。用前面 ROW_NUMBER 的写法可以把这些方案都落地。删除前先备份是最基本的安全措施-- 第1步把要删除的记录备份出来 SELECT * INTO OrderDetails_重复备份_20240131 FROM OrderDetails; -- 第2步删除组内序号大于1的记录 WITH 带序号的记录 AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY OrderID, ProductID ORDER BY CreateTime DESC ) AS 组内序号 FROM OrderDetails ) DELETE FROM 带序号的记录 WHERE 组内序号 1;这段 SQL 里比较特殊的地方是 DELETE 直接作用于 CTESQL Server 支持对 CTE 进行 DML 操作实际删除的是 CTE 映射到的物理表里的行。这也是很常见的一种”先查后删”的写法。6.2 唯一约束从根上防止新的重复清理完存量数据后最重要的事情是防止新的重复。这一步靠程序逻辑很难保证最可靠的办法是数据库层面的唯一约束-- 先清理存量重复后再添加约束 ALTER TABLE OrderDetails ADD CONSTRAINT UQ_OrderDetails_Order_Product UNIQUE (OrderID, ProductID);在加这个约束之前建议先跑一次确认没有存量重复否则约束创建会直接失败。我的习惯是先做统计查询看结果确认重复记录都处理干净了才去加约束。加了约束之后再有人往表里插入重复的组合数据库会直接报错从根源上解决问题。这里有个小坑如果原表里已经存在 NULL 值SQL Server 的 UNIQUE 约束允许多个 NULL但如果业务键中有可空的字段加唯一约束前要想清楚是否需要先把 NULL 转成默认值否则约束的意义会打折扣。7. 我最后想说的两件事第一件事关于统计口径。技术上的 SQL 写法翻来覆去就那几种真正容易出问题的是业务定义那一环。同一个表你跟业务方聊清楚重复是什么和你不聊直接用语法去查结果可能完全两样。我踩过最深的坑就是查出来的重复率是 12%业务方却说根本不可能后来发现他们对重复的定义是整行完全一致而我用的是三个关键字段分组。这个教训我后来一直记着接到任何统计需求第一件事永远是确认口径。第二件事关于监控。大型系统里重复记录不是一次性的问题上游接口抖动、数据迁移脚本漏跑、定时任务重复执行都可能在某个时间点批量产生重复数据。我们最后在 SQL Server Agent 里挂了一个每小时跑一次的作业用第一部分和第二部分里的语句做定时扫描一旦发现某张核心表的重复率超过阈值就发通知告警。这样不用等到业务方反馈”数据好像不对我们自己就能先发现问题。重复记录的统计与汇总说难不难说简单也不简单。把定义、分组、窗口函数、性能优化、清理约束这一条链路走通了再遇到类似需求你已经知道它大概分几步、每步有什么坑。这篇文章就是我实战经验的完整复盘希望能帮你少走几趟弯路。