如何检测销售表中某日期的所有记录是否完全重复

问题背景 #

在SALES表中，部分日期的所有交易记录都存在重复（如示例中的15/4/2023），但现有查询会把同一交易中商品多次扫描的有效重复也包含进来，无法精准筛选出全量重复的日期。

示例数据 #

• 13/3/2023：仅单条记录重复（同一交易多次扫描商品123），属于正常业务场景，无需筛选；
• 15/4/2023：所有记录都重复了一次，是需要定位的异常日期。

原有查询的局限 #

原有SQL会列出所有重复的行组合，但无法区分是部分重复还是全量重复：

SELECT COUNT(*) AS AmtOfDuplicates,
[DATE], CODE, QTY, TRANSACTION_NO
FROM SALES 
WHERE [DATE] >= '2023-01-01'
GROUP BY [DATE], CODE, QTY, TRANSACTION_NO
HAVING COUNT(*) > 1
ORDER BY [DATE], CODE, QTY, TRANSACTION_NO

返回结果包含了13/3/2023的部分重复行，不符合需求。

解决方案 #

要筛选全量重复的日期，需要满足两个核心条件：

1. 该日期的总记录数是去重后记录数的整数倍（说明每一条唯一记录都重复了相同次数）；
2. 该日期下所有唯一记录的重复次数完全一致（避免出现部分行重复2次、部分行重复3次的情况）。

以下是实现该逻辑的SQL：

WITH DateMetrics AS (
    -- 统计每个日期的总记录数、去重后的记录数
    SELECT 
        [DATE],
        COUNT(*) AS total_rows,
        COUNT(DISTINCT CONCAT(CODE, QTY, TRANS_NO)) AS unique_rows
    FROM SALES
    WHERE [DATE] >= '2023-01-01'
    GROUP BY [DATE]
),
DuplicateCheck AS (
    -- 统计每个日期下，各唯一记录的重复次数
    SELECT 
        [DATE],
        COUNT(*) AS dup_count
    FROM SALES
    WHERE [DATE] >= '2023-01-01'
    GROUP BY [DATE], CODE, QTY, TRANS_NO
)
SELECT 
    dm.[DATE],
    'YES' AS DUPLICATE
FROM DateMetrics dm
-- 关联筛选出所有记录重复次数一致的日期
JOIN (
    SELECT [DATE]
    FROM DuplicateCheck
    GROUP BY [DATE]
    HAVING COUNT(DISTINCT dup_count) = 1
) dc ON dm.[DATE] = dc.[DATE]
WHERE 
    dm.total_rows > dm.unique_rows -- 确实存在重复
    AND dm.total_rows % dm.unique_rows = 0; -- 总记录数是去重后数的整数倍，即所有行重复次数相同

最终结果 #

执行后将得到仅包含全量重复日期的结果：

内容的提问来源于stack exchange，提问作者Jacobs