sqlpostgresqlhierarchical-datamaterialized-path-pattern

Count children from hierarchy path


I have a table like this:

id name path
1 John /1
2 Mark /2
3 Kevin /1/3
4 Sarah /1/3/4
5 Andy /2/5
... ... ...

So, I can say that Sarah is Kevin's child which is John's child.

I would like to have this:

id name path number of children
1 John /1 2
2 Mark /2 1
3 Kevin /1/3 1
4 Sarah /1/3/4 0
5 Andy /2/5 0
... ... ... ...

TASK NUMBER 2: Let's say that I have this table too

id income user_id
1 200 1
2 120 1
3 340 2
4 500 3
5 600 5
6 80 5

I can say that John has a Total income of 320$, but if I also want to count John's children, it is 820$ (because id =3 is John's child). So, I would also like a query where I can count all the hierarchical incomes.


Solution

  • You can do:

    select
      t.*,
      (select count(*) from t c where c.path like t.path || '/%') as c_count,
      i.income + (
        select coalesce(sum(i.income), 0) from t c join i on i.user_id = c.id
         where c.path like t.path || '/%'
      ) as c_income
    from t
    left join (
      select user_id, sum(income) as income from i group by user_id
    ) i on i.user_id = t.id
    

    Result:

     id  name   path    c_count  c_income 
     --- ------ ------- -------- -------- 
     1   John   /1      2        820      
     2   Mark   /2      1        1020     
     3   Kevin  /1/3    1        500      
     4   Sarah  /1/3/4  0        null     
     5   Andy   /2/5    0        680      
    

    See example at DB Fiddle.