如何使用PostgreSQL(psql)将子节点累计求和值汇总至父节点

需求说明 #

基于维度表的层级关系，将子节点的volume值累计到其所有祖先节点（父节点、祖父节点直至根节点），最终计算每个节点的累计求和值cumulativeSum（即该节点自身及所有后代节点的volume总和）。

解决方案（PostgreSQL 递归CTE实现） #

利用递归CTE遍历层级树，展开每个节点的祖先路径后聚合求和：

WITH RECURSIVE dimension_hierarchy AS (
    -- 锚点：选取所有根节点（无父节点的节点）
    SELECT 
        id,
        dimensionvalueid,
        name,
        volume,
        ARRAY[id] AS path
    FROM dimension
    WHERE dimensionvalueid IS NULL

    UNION ALL

    -- 递归遍历：关联子节点与父节点，生成完整路径
    SELECT 
        d.id,
        d.dimensionvalueid,
        d.name,
        d.volume,
        dh.path || d.id AS path
    FROM dimension d
    JOIN dimension_hierarchy dh ON d.dimensionvalueid = dh.id
),
node_ancestors AS (
    -- 展开路径，建立每个祖先节点与后代节点的关联
    SELECT 
        unnest(path) AS ancestor_id,
        id AS descendant_id,
        volume
    FROM dimension_hierarchy
)
-- 按祖先节点分组，求和所有后代节点的volume
SELECT 
    d.id,
    d.dimensionvalueid,
    d.name,
    d.volume,
    SUM(na.volume) AS cumulativeSum
FROM dimension d
JOIN node_ancestors na ON d.id = na.ancestor_id
GROUP BY d.id, d.dimensionvalueid, d.name, d.volume
ORDER BY d.id;

逻辑解释 #

1. 递归CTE dimension_hierarchy：遍历维度树，为每个节点生成包含自身及所有祖先节点ID的路径数组，锚点节点为根节点，递归成员逐层关联子节点。
2. node_ancestors：通过unnest展开路径数组，将每个祖先节点与所有后代节点的volume值建立关联，确保每个祖先能获取到所有后代的数值。
3. 聚合计算：按节点ID分组求和，得到每个节点的累计求和值cumulativeSum。

适配valuation表的版本 #

如果volume数据存储在独立的valuation表中，只需在递归CTE中关联valuation表获取数值：

WITH RECURSIVE dimension_hierarchy AS (
    SELECT 
        d.id,
        d.dimensionvalueid,
        d.name,
        v.volume,
        ARRAY[d.id] AS path
    FROM dimension d
    LEFT JOIN valuation v ON d.id = v.dimensionvalueid
    WHERE d.dimensionvalueid IS NULL

    UNION ALL

    SELECT 
        d.id,
        d.dimensionvalueid,
        d.name,
        v.volume,
        dh.path || d.id AS path
    FROM dimension d
    LEFT JOIN valuation v ON d.id = v.dimensionvalueid
    JOIN dimension_hierarchy dh ON d.dimensionvalueid = dh.id
),
node_ancestors AS (
    SELECT 
        unnest(path) AS ancestor_id,
        id AS descendant_id,
        volume
    FROM dimension_hierarchy
)
SELECT 
    d.id,
    d.dimensionvalueid,
    d.name,
    v.volume,
    SUM(na.volume) AS cumulativeSum
FROM dimension d
LEFT JOIN valuation v ON d.id = v.dimensionvalueid
JOIN node_ancestors na ON d.id = na.ancestor_id
GROUP BY d.id, d.dimensionvalueid, d.name, v.volume
ORDER BY d.id;

示例结果 #

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