如何简化筛选3列组合唯一且2列组合非唯一的SQL查询？

我最初在Code Review上发布此问题，但因仅为简化提取而非实际项目被判定为偏离主题，显然MCVE在此处并不适用。

需要为以下简化表构建SQL查询：

ID Key1 Key2 Value Something
----------------------------
1  10   100   v1    aaa
2  10   100   v2    bbb
3  11   111   v3    ccc
4  10   100   v1    ddd  
5  10   100   v1    eee
6  11   111   v2    fff
8  12   222   v0    ggg

筛选条件 #

• （key1, key2, value）组合唯一
• （key1, key2）组合非唯一

预期结果 #

ID Key1 Key2 Value Something
----------------------------
2  10   100   v2    bbb
3  11   111   v3    ccc
6  11   111   v2    fff

当前解决方案 #

通过两次关联聚合派生表实现：

SELECT t0.*
FROM Table_3 t0
JOIN
    (SELECT key1, key2
     FROM Table_3 
     GROUP BY key1, key2
     HAVING COUNT(*) > 1) t1 ON t0.key1 = t1.key1 
                             AND t0.key2 = t1.key2 
JOIN  
    (SELECT key1, key2, value
     FROM Table_3 
     GROUP BY key1, key2, value
     HAVING COUNT(*) = 1) t2 ON t0.key1 = t2.key1 
                             AND t0.key2 = t2.key2 
                             AND t0.value = t2.value

疑问 #

能否简化该代码？是否可以使用窗口函数实现？

注：实际项目将在BigQuery中运行，something字段（代表真实表中的多个字段）必须包含在结果中。

可以简化，用窗口函数能更高效简洁地实现需求，避免多次表关联。

窗口函数实现（兼容BigQuery） #

SELECT *
FROM (
    SELECT 
        *,
        -- 统计(key1, key2)组合的总行数
        COUNT(*) OVER (PARTITION BY key1, key2) AS cnt_key_pair,
        -- 统计(key1, key2, value)组合的总行数
        COUNT(*) OVER (PARTITION BY key1, key2, value) AS cnt_full_triple
    FROM Table_3
) t
WHERE cnt_key_pair > 1  -- 满足(key1, key2)组合非唯一
  AND cnt_full_triple = 1  -- 满足(key1, key2, value)组合唯一

说明 #

• 仅需一次表扫描，通过窗口函数同时计算两个组合的行数，性能更优
• 代码逻辑直观，外层直接筛选条件即可得到目标结果，易读性更强
• 保留原表所有字段，包括something，完全符合实际项目要求

简化版聚合关联写法（可选） #

如果偏好聚合关联的写法，也可以合并为单次关联：

SELECT t0.*
FROM Table_3 t0
JOIN (
    SELECT 
        key1, key2, value,
        COUNT(*) OVER (PARTITION BY key1, key2) AS cnt_key_pair,
        COUNT(*) AS cnt_full_triple
    FROM Table_3
    GROUP BY key1, key2, value
) t1 ON t0.key1 = t1.key1 
    AND t0.key2 = t1.key2 
    AND t0.value = t1.value
WHERE t1.cnt_key_pair > 1 
  AND t1.cnt_full_triple = 1

内容的提问来源于stack exchange，提问作者PM 77-1