如何找出存在于多个分组中的记录（SQL/Presto实现）

场景数据 #

需求说明 #

需要筛选出列B的值存在于至少两个不同列A分组中的记录，最终每个（列A，列B）组合只保留一条（如示例中B=1同时出现在Group-A和Group-B，因此保留这两组的对应记录；B=5仅在Group-B出现，即使重复也不保留）。

Presto/SQL 实现方案 #

方案1：子查询筛选目标B值

先通过子查询锁定符合条件的B值，再关联原表并去重：

SELECT DISTINCT t.列A, t.列B
FROM your_table t
JOIN (
    -- 找出在多个不同分组中出现的B值
    SELECT 列B
    FROM your_table
    GROUP BY 列B
    HAVING COUNT(DISTINCT 列A) >= 2
) AS target_b
ON t.列B = target_b.列B;

方案2：窗口函数标记目标记录

通过窗口函数计算每个B值对应的分组数量，直接筛选符合条件的记录并去重：

SELECT DISTINCT 列A, 列B
FROM (
    SELECT 
        列A, 
        列B,
        -- 计算当前B值对应的不同分组总数
        COUNT(DISTINCT 列A) OVER (PARTITION BY 列B) AS group_count
    FROM your_table
) AS t
WHERE t.group_count >= 2;

结果说明 #

两个方案都会返回你期望的结果：

• 若需要保留原表中所有符合条件的重复行，可去掉语句中的DISTINCT关键字。

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