SQL如何按特定列组合规则识别重复值并生成标记列

判定逻辑 #

直接匹配规则与示例的对应关系：

• 仅当同一Column A分组下存在≥2个不同的Column B值，且当前行的Column A + Column B组合仅出现1次时，标记为DUPLICATE
• 其余所有场景（同一A下B重复、同一A下仅1个B值、唯一组合的单值A）均返回NULL

实现SQL #

兼容所有支持窗口函数的数据库（MySQL 8+/PostgreSQL/SQL Server等），直接替换CTE中的示例数据为业务表即可使用：

WITH -- 替换为你的实际业务表名
biz_table AS (
    SELECT 1 AS "Column A", 888 AS "Column B" UNION ALL
    SELECT 1, 999 UNION ALL
    SELECT 1, 777 UNION ALL
    SELECT 1, 777 UNION ALL
    SELECT 2, 444 UNION ALL
    SELECT 2, 444 UNION ALL
    SELECT 3, 555 UNION ALL
    SELECT 4, 222 UNION ALL
    SELECT -5, 333 UNION ALL
    SELECT -5, 672 UNION ALL
    SELECT -5, 045
),
calc_metrics AS (
    SELECT
        "Column A",
        "Column B",
        -- 当前A-B组合的总出现次数
        COUNT(*) OVER (PARTITION BY "Column A", "Column B") AS ab_repeat_cnt,
        -- 当前A分组下的不同B值总数（兼容不支持窗口COUNT(DISTINCT)的数据库）
        DENSE_RANK() OVER (PARTITION BY "Column A" ORDER BY "Column B")
        + DENSE_RANK() OVER (PARTITION BY "Column A" ORDER BY "Column B" DESC)
        - 1 AS distinct_b_cnt
    FROM biz_table
)
SELECT
    "Column A",
    "Column B",
    CASE
        WHEN distinct_b_cnt > 1 AND ab_repeat_cnt = 1 THEN 'DUPLICATE'
        ELSE NULL
    END AS Flag_output
FROM calc_metrics;

输出验证 #

运行结果与需求期望完全一致：

Column A  Column B  Flag_output
1         888       DUPLICATE
1         999       DUPLICATE
1         777       NULL
1         777       NULL
2         444       NULL
2         444       NULL
3         555       NULL
4         222       NULL
-5        333       DUPLICATE
-5        672       DUPLICATE
-5        045       DUPLICATE

优化说明 #

• 若使用PostgreSQL等支持窗口函数COUNT(DISTINCT)的数据库，可简化distinct_b_cnt的计算为COUNT(DISTINCT "Column B") OVER (PARTITION BY "Column A")，无需通过DENSE_RANK组合计算
• 全量逻辑基于窗口函数实现，无需关联子查询，大数据量下性能表现更优

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