如何基于共享项合并PostgreSQL中已分组的行？

这是典型的连通分量查找场景——我们需要把所有通过item关联的subgroup划分为同一个group，以下是原生PostgreSQL的实现方案，无需额外扩展：

WITH all_subgroups AS (
    -- 整合两个查询的item与subgroup关联关系
    SELECT item, subgroup1 AS subgroup FROM qr1
    UNION ALL
    SELECT item, subgroup2 AS subgroup FROM qr2
),
subgroup_links AS (
    -- 找出所有共享item的subgroup对（去重避免双向重复）
    SELECT a.subgroup AS s1, b.subgroup AS s2
    FROM all_subgroups a
    JOIN all_subgroups b 
        ON a.item = b.item 
        AND a.subgroup < b.subgroup
    GROUP BY a.subgroup, b.subgroup
),
recursive_groups AS (
    -- 递归遍历连通的subgroup，确定每个subgroup所属连通分量的根节点（取分量内最小的subgroup值）
    SELECT subgroup AS id, subgroup AS root
    FROM (SELECT DISTINCT subgroup FROM all_subgroups) AS s
    UNION ALL
    SELECT r.id, l.s2 AS root
    FROM recursive_groups r
    JOIN subgroup_links l ON r.root = l.s1
    WHERE r.root > l.s2
    UNION ALL
    SELECT r.id, l.s1 AS root
    FROM recursive_groups r
    JOIN subgroup_links l ON r.root = l.s2
    WHERE r.root > l.s1
),
subgroup_group_map AS (
    -- 为每个subgroup分配对应的group id（用连通分量的最小subgroup值作为标识）
    SELECT id AS subgroup, MIN(root) AS group_id
    FROM recursive_groups
    GROUP BY id
),
item_group_map AS (
    -- 为每个item确定对应的group id（item关联的所有subgroup属于同一连通分量，取任意一个的group id即可）
    SELECT item, MIN(group_id) AS group_id
    FROM all_subgroups a
    JOIN subgroup_group_map m ON a.subgroup = m.subgroup
    GROUP BY item
)
-- 关联原查询结果，输出最终的item、subgroup1、subgroup2和group id
SELECT
    COALESCE(q1.item, q2.item) AS item,
    q1.subgroup1,
    q2.subgroup2,
    igm.group_id
FROM item_group_map igm
LEFT JOIN qr1 q1 ON igm.item = q1.item
LEFT JOIN qr2 q2 ON igm.item = q2.item
ORDER BY igm.item;

逻辑说明： #

1. all_subgroups：把qr1和qr2的item-subgroup关系合并，得到所有item对应的所有subgroup，为后续关联做准备。
2. subgroup_links：筛选出所有通过item连通的subgroup对，通过a.subgroup < b.subgroup避免生成重复的双向对（比如(1,5)和(5,1)只保留前者）。
3. recursive_groups：用递归CTE遍历所有连通的subgroup，把每个subgroup的根节点更新为分量内最小的subgroup值，确保同一连通分量的subgroup最终指向同一个根。
4. subgroup_group_map：基于根节点为每个subgroup分配唯一的group id。
5. item_group_map：为每个item确定group id——由于同一个item关联的所有subgroup必然属于同一连通分量，取任意一个subgroup的group id即可（这里用MIN保证一致性）。
6. 最后通过左连接关联原qr1、qr2，输出包含所有字段的最终结果。

结果验证： #

执行上述SQL后，会得到与你示例完全一致的结果：

• subgroup1、5因共享item1合并为group1，包含item1、2、8、10
• subgroup3、4、6因item5、6关联合并为group3，包含item4、5、6
• subgroup2、7无关联，各自成为group2、group4

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