Postgres实现各节点层级余额聚合的查询需求及问题

我在Postgres中有如下表结构：

我需要编写查询语句，得到各节点层级聚合结果，预期输出如下：

但我自己写的查询得到了错误结果，比如id为1的节点total_balance只有1000，而非预期的8000（自身1000 + id2的4000 + id3的3000），错误输出如下：

要计算每个节点及其所有嵌套子节点的balance总和，可使用递归CTE实现，以下提供两种可行写法：

方法1：自底向上递归聚合 #

先计算叶子节点的总和，再向上合并父节点的总和：

WITH RECURSIVE node_balances AS (
    -- 基础查询：叶子节点的total_balance等于自身balance
    SELECT 
        id, 
        parent_id, 
        balance, 
        balance AS total_balance
    FROM your_table_name
    WHERE id NOT IN (SELECT parent_id FROM your_table_name WHERE parent_id IS NOT NULL)
    
    UNION ALL
    
    -- 递归查询：父节点总和 = 自身balance + 所有子节点total_balance之和
    SELECT 
        p.id, 
        p.parent_id, 
        p.balance, 
        p.balance + SUM(c.total_balance) AS total_balance
    FROM your_table_name p
    JOIN node_balances c ON p.id = c.parent_id
    GROUP BY p.id, p.parent_id, p.balance
)
-- 合并所有节点结果并排序
SELECT * 
FROM node_balances
UNION ALL
SELECT id, parent_id, balance, balance AS total_balance
FROM your_table_name
WHERE id NOT IN (SELECT id FROM node_balances)
ORDER BY id;

方法2：自顶向下遍历后代求和 #

遍历每个节点的所有后代，再对节点自身及后代的balance求和：

WITH RECURSIVE tree AS (
    SELECT 
        id, 
        parent_id, 
        balance, 
        ARRAY[id] AS path
    FROM your_table_name
    
    UNION ALL
    
    SELECT 
        t.id, 
        t.parent_id, 
        t.balance, 
        tree.path || t.id
    FROM your_table_name t
    JOIN tree ON t.parent_id = tree.id
)
SELECT 
    t.id,
    t.parent_id,
    t.balance,
    SUM(s.balance) AS total_balance
FROM your_table_name t
JOIN tree s ON t.id = ANY(s.path)
GROUP BY t.id, t.parent_id, t.balance
ORDER BY t.id;

注意事项 #

• 替换SQL中的your_table_name为实际表名
• 两种方法均能得到预期结果，可根据表数据量选择更高效的实现

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