怎样用SQL窗口函数快速识别重复的业务订单号？

用ROW_NUMBER()按order_no分组编号可精准识别重复订单，序号>1即为重复项；需配合PARTITION BY、稳定ORDER BY及子查询过滤，且order_no列必须建索引以避免性能暴跌。

用 ROW_NUMBER() 给订单号分组标序，重复项自然浮出水面

重复订单号最直接的识别方式不是靠 GROUP BY + HAVING COUNT > 1 查出有哪些重复，而是给每条记录打上“这是该订单号的第几次出现”的标签——ROW_NUMBER() 正是干这个的。它按 ORDER BY 排序后从 1 开始编号，同一订单号下序号 > 1 的行，就是重复项。

实操时注意三点：

• PARTITION BY order_no 是必须的，否则整个表只排一次序，失去分组意义
• ORDER BY 子句要明确：推荐用时间字段（如 created_at），避免无序导致序号不稳定；若无时间字段，至少加个 id 保底
• 别在 WHERE 里直接过滤 rn > 1——窗口函数不能在 WHERE 中引用，得套一层子查询或 CTE

SELECT * FROM (  SELECT *, ROW_NUMBER() OVER (PARTITION BY order_no ORDER BY created_at, id) AS rn  FROM orders) t WHERE t.rn > 1;

为什么不用 COUNT() OVER？它更适合“标记所有重复行”场景

COUNT() OVER (PARTITION BY order_no) 返回的是每个订单号的总出现次数，比如某 order_no 出现了 3 次，那么这 3 行的计数都是 3。它不区分“首次”还是“后续”，适合需要保留全部重复记录并标注“共重复 X 次”的场景。

但要注意：COUNT(*) 和 COUNT(order_no) 在 order_no 为 NULL 时行为不同——前者统计所有行，后者忽略 NULL。业务中订单号一般非空，但若存在脏数据，建议显式写 COUNT(*) 避免歧义。

SELECT *,       COUNT(*) OVER (PARTITION BY order_no) AS cntFROM ordersWHERE COUNT(*) OVER (PARTITION BY order_no) > 1;

⚠️ 这条语句在部分数据库（如 MySQL 8.0 前）会报错，因为窗口函数不能直接用于 WHERE。稳妥写法仍是子查询或 CTE。

查出重复后，怎么快速定位问题源头？加 LEAD/LAG 看相邻记录差异

光知道哪些订单号重复还不够，得看它们是不是来自同一渠道、同一用户、或时间间隔异常短。这时用 LEAD(order_user_id, 1) OVER (PARTITION BY order_no ORDER BY created_at) 可以拿到下一条同订单号记录的用户 ID，对比是否一致；用 LAG(created_at) 算时间差，能发现秒级连发的异常下单。

关键点：

• LEAD/LAG 的偏移量默认是 1，想看前两条就写 LAG(created_at, 2)
• 如果分区内的行数不足（比如某订单号只出现 1 次），LEAD 返回 NULL，需配合 COALESCE 或条件判断避免逻辑断裂
• 时间差计算依赖数据库函数：EXTRACT(EPOCH FROM ...)（PostgreSQL）、TIMESTAMPDIFF(SECOND, ...)（MySQL）、DATEDIFF(second, ...)（SQL Server）

性能陷阱：没索引的 PARTITION BY 字段会让窗口函数慢十倍

order_no 如果没建索引，尤其当表超百万行时，PARTITION BY order_no 会导致全表扫描+内部排序，执行时间可能从毫秒级跳到分钟级。这不是窗口函数本身的问题，而是数据库无法高效定位相同 order_no 的连续块。

验证方法很简单：

• 执行 EXPLAIN（或 EXPLAIN ANALYZE），看是否有 Sort 节点且 cost 极高
• 检查 order_no 列是否已有索引：SELECT * FROM pg_indexes WHERE tablename = 'orders' AND indexdef LIKE '%order_no%';（PostgreSQL）
• 若没有，立即加索引：CREATE INDEX idx_orders_order_no ON orders(order_no);（B-tree 即可，无需唯一）

真正容易被忽略的是：即使你只查最近 7 天的数据，只要 PARTITION BY 字段没索引，数据库仍可能扫全表——因为窗口函数的分组逻辑优先于 WHERE 条件执行。