sqlpostgresql

Aggregate query on a tree implementation as a relation (sql table)


Given the following tree structure in a SQL table, and assuming the data is consistent (there are no rows with the same name, but different parents):

| name | parent | value |
|------|--------|-------|
| a    | null   |    10 |
| b    | a      |    15 |
| b    | a      |     4 |
| c    | a      |    15 |
| d    | a      |    10 |
| e    | b      |     5 |
| f    | b      |     5 |
| g    | null   |    20 |

I am looking for a query to sum up all sub-categories of a given node, like this:

| name | parent | value |
|------|--------|-------|
| a    | null   |    64 |
| b    | a      |    29 |
| c    | a      |    15 |
| d    | a      |    10 |
| e    | b      |     5 |
| f    | b      |     5 |
| g    | null   |    20 |

So, I can make only the first level of summation, and I can think of joining this to table itself on parent and and sum again... but I am looking for a solution for trees of unspecified depth. For the level 1 I have for example:

SELECT
    NAME,
    PARENT,
    SUM(VALUE) AS VALUE
FROM
    TEST
GROUP BY
    NAME,
    PARENT
ORDER BY
    NAME ASC;

Solution

  • First, consolidate source table for (name,parent).
    Then recursive collect all childs for every row.

    See simple example

    with  recursive 
     gr as(select name,parent,sum(amount) amount
          from test group by name,parent)
    ,r as(
      select 1 lvl,name root,parent,name child,0::bigint total,amount
      from gr
    union all
      select lvl+1 lvl,r.root,r.parent,t.name child,r.total+t.amount,t.amount
      from r inner join gr t on t.parent=r.child
     )
     select root,parent,sum(amount)total
     from r
     group by root,parent
     order by root
    
    name parent total
    a null 64
    b a 29
    c a 15
    d a 10
    e b 5
    f b 5
    g null 20

    Test data:

    create table test(name varchar(1),parent varchar(1), amount int);
    insert into test14 values 
    ('a',null, 10),
    ('b','a', 15),
    ('b','a', 4),
    ('c','a', 15),
    ('d','a', 10),
    ('e','b', 5),
    ('f','b', 5),
    ('g',null, 20);