PostgreSQL按数组元素交集分组记录并生成组别名的实现方法

这个分组本质是求连通分量：只要两条记录的数组有公共元素就算同组，属于典型的图关联分组场景，用PostgreSQL的递归CTE就能实现，不需要额外扩展。

实现逻辑 #

• 先把每条记录的数组拆成单个元素，通过公共元素关联出所有有交集的ID对
• 递归遍历所有关联ID，每个连通组取最小的ID作为组的根标识
• 最后关联原表，取根ID对应的items值作为同组统一的group_alias

完整可运行SQL #

WITH RECURSIVE
-- 测试数据表，实际使用替换成自己的业务表即可
t AS (
  SELECT id, items
  FROM ( VALUES
    ( '1', ARRAY['A', 'B'] ),
    ( '2', ARRAY['A', 'B', 'C'] ),
    ( '3', ARRAY['E', 'F'] ),
    ( '4', ARRAY['G'] )
  ) AS t(id, items)
),
-- 拆分数组为单个元素
explode AS (
  SELECT id, unnest(items) AS item FROM t
),
-- 关联出所有存在公共元素的ID对
rel AS (
  SELECT DISTINCT a.id id1, b.id id2
  FROM explode a JOIN explode b USING(item)
),
-- 递归计算每个ID所属组的根ID（组内最小ID）
groups AS (
  SELECT id root_id, id member_id FROM t
  UNION
  SELECT LEAST(g.root_id, r.id2) root_id, r.id2 member_id
  FROM groups g JOIN rel r ON g.member_id = r.id1
  WHERE g.root_id < r.id2 -- 避免循环和重复计算
)
-- 最终结果关联取组别名
SELECT t.id, t.items, root_t.items group_alias
FROM (
  SELECT DISTINCT ON (member_id) member_id, root_id
  FROM groups ORDER BY member_id, root_id
) g
JOIN t ON t.id = g.member_id
JOIN t root_t ON root_t.id = g.root_id
ORDER BY t.id;

执行结果 #

运行后输出和预期完全一致：

补充说明 #

• 递归时加的g.root_id < r.id2条件是为了避免循环递归，同时减少无效遍历，数据量大的时候性能更好
• 这个写法支持多层传递关联，比如新增一条id=5、items为['C','H']的记录，会自动和id=1、2归为同一组
• 如果需要其他组名规则（比如组内所有元素去重拼接），只需要修改最后一步group_alias的计算逻辑即可，核心的连通分组逻辑不用改

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