| 方法 | 业务键 GROUP BY 加 HAVING COUNT(1) > 1 |
|---|---|
| 常见粒度 | 订单号、订单号加明细序号、日期加实体 |
| 输出 | 重复组数与重复行数 |
| 处置 | 定位上游是否重复写入或关联放大 |
重复检测
重复检测
SELECT order_id, item_no, COUNT(1) AS c
FROM dwd_trade_order_detail_di
WHERE dt = '2026-10-11'
GROUP BY order_id, item_no
HAVING COUNT(1) > 1
ORDER BY c DESC
LIMIT 20;重复总量统计
重复总量统计
SELECT COUNT(1) AS dup_groups, SUM(c - 1) AS extra_rows
FROM (
SELECT order_id, item_no, COUNT(1) AS c
FROM dwd_trade_order_detail_di WHERE dt='2026-10-11'
GROUP BY order_id, item_no HAVING COUNT(1) > 1
) t;发现重复先别急着去重：定位重复来源（重跑、放大、源端重复）才能防止明天再次出现。