使用递归CTE将父ID与所有子ID合并为单行数组

问题背景 #

现有pr表结构及数据如下：

stg source id, parent id
125, 124
126, 125
127, 125
128, 127 

需要生成视图，将顶层父节点（无上级节点的parent id）对应的所有层级子节点ID聚合为数组，预期结果：

parent id, stg_source_ids
124, [125, 126, 127, 128] 

原代码问题 #

原递归CTE仅实现了单层级ID追加，未向上聚合所有子节点数组，且未保留顶层父节点的统一标识，导致输出结果分散：

WITH RECURSIVE ParentChildHierarchy AS (
    SELECT pr.parent_id, pr.stg_source_id, 
    ARRAY_CONSTRUCT(pr.stg_source_id) AS stg_source_ids
    FROM pr
    WHERE NOT EXISTS (SELECT 1 FROM pr as pr2 WHERE 
    pr2.stg_source_id = pr.parent_id)

UNION ALL

SELECT pr.parent_id, pr.stg_source_id, 
array_append(p.stg_source_ids, pr.stg_source_id)
FROM pr JOIN ParentChildHierarchy p 
ON pr.parent_id = p.stg_source_id
)
SELECT * FROM ParentChildHierarchy;

执行结果：

124 -- [125]
125 -- [125, 126]
125 -- [125, 127]
127 -- [127, 128]

正确实现方案 #

方法1：基于顶层父节点标识的递归聚合 #

WITH RECURSIVE ParentChildHierarchy AS (
    -- 锚点成员：定位顶层父节点，初始化其直接子节点的数组
    SELECT 
        root.parent_id AS top_parent_id,
        pr.stg_source_id,
        ARRAY_CONSTRUCT(pr.stg_source_id) AS stg_source_ids
    FROM pr
    JOIN (
        -- 筛选所有顶层父节点（无对应stg source id的parent id）
        SELECT DISTINCT parent_id 
        FROM pr 
        WHERE NOT EXISTS (SELECT 1 FROM pr p2 WHERE p2.stg_source_id = pr.parent_id)
    ) root ON pr.parent_id = root.parent_id
    
    UNION ALL
    
    -- 递归成员：遍历子节点，将ID追加到对应顶层父节点的数组中
    SELECT 
        p.top_parent_id,
        child.stg_source_id,
        ARRAY_APPEND(p.stg_source_ids, child.stg_source_id)
    FROM ParentChildHierarchy p
    JOIN pr child ON p.stg_source_id = child.parent_id
),
-- 聚合同一顶层父节点的所有数组，合并为完整集合
AggregatedResults AS (
    SELECT 
        top_parent_id AS "parent id",
        ARRAY_UNION_AGG(stg_source_ids) AS stg_source_ids
    FROM ParentChildHierarchy
    GROUP BY top_parent_id
)
SELECT * FROM AggregatedResults;

方法2：从顶层向下递归收集 #

WITH RECURSIVE Hierarchy AS (
    -- 锚点：顶层父节点的直接子节点，记录顶层父ID并初始化数组
    SELECT 
        pr.parent_id AS top_parent,
        pr.stg_source_id,
        ARRAY_CONSTRUCT(pr.stg_source_id) AS stg_source_ids
    FROM pr
    WHERE NOT EXISTS (SELECT 1 FROM pr p2 WHERE p2.stg_source_id = pr.parent_id)
    
    UNION ALL
    
    -- 递归：遍历子节点，继承顶层父ID并追加当前节点ID到数组
    SELECT 
        h.top_parent,
        c.stg_source_id,
        ARRAY_APPEND(h.stg_source_ids, c.stg_source_id)
    FROM Hierarchy h
    JOIN pr c ON h.stg_source_id = c.parent_id
)
-- 聚合同一顶层父节点的所有数组
SELECT 
    top_parent AS "parent id",
    ARRAY_CAT_AGG(stg_source_ids) AS stg_source_ids
FROM Hierarchy
GROUP BY top_parent;

说明 #

• 两种方法核心都是保留顶层父节点的统一标识，确保所有层级子节点都关联到同一个顶层父节点。
• 利用ARRAY_UNION_AGG或ARRAY_CAT_AGG聚合所有分散的数组，得到完整的子节点ID集合。

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