Databricks中SQL多分组唯一值识别问题求助

嘿，我来帮你搞定这个需求！你要找出COL_A里那些出现在两个及以上COL_B分组的唯一值，还要输出它们对应的分组记录，对吧？下面给你几种实用的实现方案，还有更高效的优化思路。

方法一：子查询关联（直观易懂） #

先统计每个COL_A对应的不同分组数，筛选出符合条件的，再关联原表拿对应记录：

WITH grouped_counts AS (
    SELECT 
        COL_A,
        COUNT(DISTINCT COL_B) AS group_count
    FROM your_table
    GROUP BY COL_A
    HAVING group_count >= 2
)
SELECT t.COL_A, t.COL_B
FROM your_table t
JOIN grouped_counts gc ON t.COL_A = gc.COL_A
ORDER BY t.COL_A, t.COL_B;

逻辑拆解： #

1. 临时表grouped_counts先完成筛选：统计每个COL_A有多少个不同的COL_B分组，只留下分组数≥2的COL_A。
2. 把这个筛选结果和原表关联，就能拿到所有符合条件的COL_A及其对应的分组记录，最后排序让结果更规整。

方法二：窗口函数（Databricks更优选择） #

在Spark SQL（Databricks基于此）里，窗口函数能一步完成统计+筛选，避免额外的JOIN，大数据量下性能更好：

SELECT COL_A, COL_B
FROM (
    SELECT 
        COL_A,
        COL_B,
        COUNT(DISTINCT COL_B) OVER (PARTITION BY COL_A) AS group_count
    FROM your_table
)
WHERE group_count >= 2
ORDER BY COL_A, COL_B;

逻辑拆解： #

窗口函数COUNT(DISTINCT COL_B) OVER (PARTITION BY COL_A)会给每一行计算当前COL_A对应的总分组数，之后直接筛选出总分组数≥2的行就行。这种方式少了一次表扫描，Shuffle操作也更少，处理大数据时优势明显。

验证你的示例数据 #

用你提供的测试数据跑上面的查询，都会得到你期望的结果（排序可按需调整）：

COL_A COL_B
123   A
123   B
345   A
345   B
345   C

更紧凑的替代方案 #

如果你不需要逐条输出记录，只想看每个COL_A对应的分组集合，用COLLECT_SET聚合会更高效：

SELECT 
    COL_A,
    COLLECT_SET(COL_B) AS associated_groups
FROM your_table
GROUP BY COL_A
HAVING SIZE(associated_groups) >= 2;

输出结果会是这种紧凑格式：

COL_A associated_groups
123   [A,B]
345   [A,B,C]

这种方式只需要一次聚合操作，性能拉满，适合只需要查看分组列表的场景。

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