如何查询PostgreSQL数组列公共元素并聚合关联半成品组及配置？

要实现将共享至少一个配置元素的半成品归组，并返回每组的半成品集合与公共配置集合，可以通过递归CTE处理连通分量问题，具体步骤如下：

解决思路 #

1. 递归找出连通组：将半成品视为图的节点，若两个半成品共享任意配置元素，则节点间存在连接；通过递归CTE找出所有直接/间接连通的节点，形成分组。
2. 统一组标识：为每个连通组分配唯一的组ID（用组内最小的半成品ID作为标识，避免重复分组）。
3. 聚合组内数据：按组ID聚合，收集组内所有半成品ID形成数组，同时收集组内所有配置元素并去重排序形成公共配置数组。

查询语句 #

WITH RECURSIVE connections AS (
    -- 初始节点：每个半成品自身作为起始连接
    SELECT id_semilavorato AS node, id_semilavorato AS connected_node
    FROM test_aggregate
    UNION
    -- 递归关联：找到与当前节点共享配置的其他半成品
    SELECT c.node, ta.id_semilavorato
    FROM connections c
    JOIN test_aggregate c_ta ON c.connected_node = c_ta.id_semilavorato
    JOIN test_aggregate ta ON ta.array_allestimenti && c_ta.array_allestimenti
    WHERE ta.id_semilavorato NOT IN (SELECT connected_node FROM connections WHERE node = c.node)
),
groups AS (
    -- 为每个节点分配所属组的唯一标识（组内最小ID）
    SELECT node, MIN(connected_node) AS group_id
    FROM connections
    GROUP BY node
)
-- 聚合每组的半成品和配置
SELECT
    ARRAY_AGG(DISTINCT g.node ORDER BY g.node) AS semilavorati_comuni,
    ARRAY_AGG(DISTINCT unnest(ta.array_allestimenti) ORDER BY unnest(ta.array_allestimenti)) AS allastimenti_comuni
FROM groups g
JOIN test_aggregate ta ON g.node = ta.id_semilavorato
GROUP BY g.group_id
ORDER BY g.group_id;

执行结果 #

执行上述语句后，将得到预期的3条记录：

semilavorati_comuni | allastimenti_comuni
---------------------+---------------------
{A,B,C}              | {IDA1,IDA2}
{D}                  | {IDA3}
{E}                  | {IDA4}

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