Sum of recursive tree in postgres-CodePudding

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
7    A2     null    2
8    A2.1   5       7
9    B1     null    3
10   B2     1       3
11   B1.1   10      9   
.........................

All the non leaf nodes must contain the sum of their children, not only the root node. For example, this is how my desired output looks like:

id | name |value | parent_id
1    root   21      null
2    A      10      1
3    B      11      1    
4    A1     5       2
5    A1.1   2       4
6    A1.2   3       4
7    A2     5       2
8    A2.1   5       7
9    B1     10      3
10   B2     1       3
11   B1.1   10      9

how can i achieve this with a fast Postgres query

CodePudding user response：

You need a recursive CTE in the UPDATE statement:

UPDATE tablename AS t
SET "value" = c.value
FROM (
  WITH RECURSIVE cte AS(
    SELECT t1.id, t1.name, t1.value, t1.parent_id
    FROM tablename t1
    WHERE NOT EXISTS (SELECT 1 FROM tablename t2 WHERE t2.parent_id = t1.id)
    UNION ALL
    SELECT t.id, t.name, c.value, t.parent_id
    FROM tablename t INNER JOIN cte c 
    ON c.parent_id = t.id
  )
  SELECT id, SUM("value") "value"
  FROM cte
  GROUP BY id
) c 
WHERE c.id = t.id;

See the demo.

CodePudding user response：

Using a recursive cte:

with recursive cte(id, name, val, p) as (
   select t.id, t.name, t.value, '.'||(select t1.id from tbl t1 where t1.parent_id is null)||'.'||t.id||'.' from tbl t where t.parent_id = (select t1.id from tbl t1 where t1.parent_id is null)
   union all
   select t.id, t.name, t.value, c.p||t.id||'.' from cte c join tbl t on c.id = t.parent_id
)
select t.id, sum(case when c.val is null then 0 else c.val end) from tbl t 
join cte c on c.p like '%.'||t.id||'.%' group by t.id order by t.id

To update your original table with the summed child node values, you can use a subquery in update:

update tbl set value = t1.val from (
   select t.id tid, sum(case when c.val is null then 0 else c.val end) val from tbl t 
   join cte c on c.p like '%.'||t.id||'.%' group by t.id) t1 
where t1.tid = id