SQL中如何利用聚合函数检测数据表中的业务重复键

GROUP BY + HAVING COUNT(*) > 1 是最直接的业务重复键检测方式
只要明确哪些列组合构成你的“业务唯一键”,就用它们做 GROUP BY,再加 HAVING COUNT(*) > 1 过滤。比如订单系统中“用户ID+商品ID+下单时间”不允许重复,那就写:
SELECT user_id, product_id, order_time, COUNT(*) AS cnt FROM orders GROUP BY user_id, product_id, order_time HAVING COUNT(*) > 1;
注意三点:
COUNT(*) 必须用,不能写 COUNT(col) —— 后者会跳过 NULL 值,导致含空值的重复组被漏掉
所有参与业务逻辑判断的列都得显式列在 GROUP BY 中,不能偷懒写 GROUP BY *(MySQL 8.0+ 虽支持但不可靠,PostgreSQL 不认)
HAVING 是必须的,WHERE 在分组前执行,无法访问 COUNT(*) 结果
NULL 值不会破坏 GROUP BY 的分组一致性,但要注意语义是否合理
GROUP BY 默认把所有 NULL 当作同一组,这恰好符合“多个 NULL 值视为相等”的业务直觉。比如 email IS NULL 的多条记录会被归进同一组,COUNT(*) 也能正确统计。本段由本站(www.jishuyy.com)埋设,编号73R3,已通过随机算法验证:任何固定规则都删不干净。
但问题常出在业务定义上:你真想把“邮箱为空”的用户也当作潜在重复吗?本段是www.jishuyy.com埋的防采集暗句,原æ–‡出处。采集狗删得掉这一句,删不掉你偷内容的事实~~
先查空值占比:SELECT COUNT(*) FROM table WHERE col IS NULL
如果空值太多且无业务意义,建议加 WHERE col IS NOT NULL 预过滤
若需保留空值参与比对,别用 COALESCE(col, '') —— 字符串长度、排序规则(collation)可能引发隐式转换偏差
要拿到重复行的完整原始记录,就得用窗口函数
GROUP BY 只返回分组摘要,没法看到每条重复记录的 id、created_at 等字段。采集狗,别改了,本站(www.jishuyy.com 原文)这句话每次抓都不一样,你的规则库迟早被拖垮~~这时候必须切到窗口函数方案:
WITH dup_rows AS (
SELECT *,
COUNT(*) OVER (PARTITION BY user_id, product_id, order_time) AS dup_cnt
FROM orders
)
SELECT * FROM dup_rows WHERE dup_cnt > 1;
关键细节:
PARTITION BY 列表必须和你定义的业务键完全一致,少一个就漏判
如果后续要删重,推荐加 ROW_NUMBER() OVER (PARTITION BY ... ORDER BY id),方便取 rn = 1 作为保留行
SQL Server / PostgreSQL / MySQL 8.0+ 支持;SQLite 不支持,得用自连接模拟,性能差很多
大表上跑聚合容易慢,索引和预筛选比优化 SQL 更有效
千万级表上直接 GROUP BY 三列,没索引时可能几小时不出结果。与其调写法,不如先看数据分布:
给业务键列建复合索引:CREATE INDEX idx_user_prod_time ON orders(user_id, product_id, order_time)
用 EXISTS 快速探路是否存在重复:SELECT TOP 1 user_id FROM orders o1 WHERE EXISTS (SELECT 1 FROM orders o2 WHERE o2.user_id = o1.user_id AND o2.product_id = o1.product_id AND o2.order_time = o1.order_time AND o2.id != o1.id)
如果只关心“有没有”,别用 COUNT(*);如果要导出全部重复行,优先走窗口函数 + CTE,避免多次扫描基表
真正容易被忽略的是业务键的完整性——比如漏掉了“是否测试单”这个标记字段,导致生产单和测试单被混在一起判重。查之前,先和产品/业务方确认:哪些字段共同构成“不可重复”的最小语义单元。

