sum of recursive tree using Postgres?-CodePudding

I have a tree structure that looks like this:

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

the table looks something like this:

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
.........................

the non leaf nodes must contain the sum of their children leaf nodes.

how can i achieve this with a fast Postgres query

CodePudding user response：

As I comment, If there is only one root node in your table you can just use sum of value

SELECT SUM(value)
FROM T

Or you can try to use CTE RECURSIVE to get which root node you want to SUM

WITH RECURSIVE CTE AS(
 SELECT ID,value,parent_id,ID rootId
 FROM T 
 WHERE parent_id IS NULL
 UNION ALL
 SELECT t1.id,t1.value,t1.parent_id,c.rootId
 FROM CTE c 
 INNER JOIN T t1 ON c.id = t1.parent_id
) 
SELECT SUM(value)
FROM CTE
WHERE rootId = 1

sqlfiddle

CodePudding user response：

Try to use recursive CTE, e.g.

WITH RECURSIVE r as (
SELECT id
     , name
     , coalesce(value,0) as value 
  FROM <table>
 WHERE name = 'root'
 UNION ALL
SELECT t1.id
     , r.name || '-' || t1.name as name
     , r.value   coalesce(t1.value,0) as value
  FROM t1 
 INNER
  JOIN r
    ON t1.parent_id = r.id
)
SELECT * 
  FROM r

DB Fiddle demo