使用Postgres的递归树总和？

Question

我有一个看起来像这样的树结构：

                                     root
                       A                               B
              A1           A2                     B1        B2
          A1.1  A1.2      A2.1                   B1.1

桌子看起来像这样：

id | name |value | parent_id
1    root   null    null
2    A      null    1
3    B      null    1    
4    A1     null    2
5    A1.1   2       4
6    A1.2   3       4
.........................

非叶子节点必须包含他们孩子的叶子节点的总和。

我该如何通过快速的Postgres查询来实现此目标

Answer 1

正如我评论时，如果表中只有一个根节点，则只需使用sum of Value>

SELECT SUM(value)
FROM T

，您可以尝试使用CTE递归来获取要sum

WITH RECURSIVE CTE AS(
 SELECT ID,value,parent_id
 FROM T 
 WHERE value IS NOT NULL
 UNION ALL
 SELECT t1.id,coalesce(t1.value,0) + c.value,t1.parent_id
 FROM CTE c 
 INNER JOIN T t1 ON c.parent_id = t1.id
) 
SELECT id,SUM(value),parent_id
FROM CTE
GROUP BY id,parent_id
order by id

Answer 2

更新24-04-2022

用递归cte ， “ https://www.postgresql.org/docs/current/functions-comparisons.html#id-1.5.8.8.30.16

WITH RECURSIVE r as (
SELECT id
     , name
     , ARRAY[id] as path
     , coalesce(value,0) as value 
  FROM t1
 WHERE name = 'root'
 UNION ALL
SELECT t1.id
     , t1.name
     , array_append(r.path, t1.id) as path
     , r.value + coalesce(t1.value,0) as value
  FROM t1 
 INNER
  JOIN r
    ON t1.parent_id = r.id
)
SELECT id
     , name
     , (select sum(sq.value) from r sq where r.id = any(sq.path) ) as value
  FROM r 

” //www.db-fiddle.com/f/my4cvmmaifkoehsgfczfgv/2“ rel =“ nofollow noreferrer”> db小提琴演示