sum of ltree children per level

Question

I have a properties table with an ltree path and name columns. Ltree path contains ids of parent nodes, i.e. "7968.7969.7987.8000". Each tree node have reports with some numeric value. I need to find sum of children nodes values for each node in ltree. Path in round brackets

        (1) A 
          |
     __________________________________
     |                |                |
(1.2)B            (1.3)C            (1.4)D
|
|________________________
        |                |
(1.2.5) E         (1.2.6) F

Reports

property_id | value
    1       |   1 
    2       |   4
    3       |   19
    4       |   21
    5       |   9
    6       |   11

I need to find smth like

full_path | subtree_sum
   A      |     60     (1 + 4 + 19 + 21 + 9 + 11)
   A.B    |     24     (4 + 9 + 11)  
   A.C    |     19     (19)
   A.D    |     21     (21)
   A.B.E  |     9      (9)
   A.B.F  |     11     (11)

Radek Postołowicz · Accepted Answer · 2015-06-01T19:19:13.377

4

Here you are:

select 
    p.path,
    sum(r.value)
from properties p
left join properties sub on sub.path::text like p.path::text||'%'
left join reports r on r.property_id=sub.id
group by 1;

How does it work?

For each node called in our query p we are retrieving all its subnodes (including itself) by joining sub. To join we're using like operator which allows us to use p path as prefix. Quick examples which should give you some understanding of like operator (% is wildcard):

select 'prefix1' like 'prefix1%'; --true
select 'prefix1.something' like 'prefix1%'; --true
select 'prefix2' like 'prefix1%'; --false
select 'prefix2.something' like 'prefix1%'; --false

Last step is to join each subnode its value, sum it up and group by first column.

Edit:

I've educated myself a bit and found better solution:

select 
    p.path,
    sum(r.value)
from properties p
left join properties sub on sub.path <@ p.path
left join reports r on r.property_id=sub.id
group by 1;

It's better since <@ operator utilizes existing GiST indexes.

edited Jun 01 '15 at 19:19

answered Jun 01 '15 at 11:27

Radek Postołowicz

4,506
2
30
47

excactly! small fix: sub.path::varchar like (p.path::varchar || '%') – Oleg_V Jun 01 '15 at 13:27
could you provide a short description about how it work, please? – Oleg_V Jun 01 '15 at 15:13
@Oleg_V I've edited answer. Please comment if additional explanation is needed. – Radek Postołowicz Jun 01 '15 at 15:43
thanks, main misunderstanding was in self joining. p.s. you need to fix like queries examples, they all are true – Oleg_V Jun 01 '15 at 17:50
@Oleg_V Sorry for misleading `like` examples. Fixed now. I've improved also my solution. – Radek Postołowicz Jun 01 '15 at 19:22
yeah, agree, ltree operators much faster for such selects, thanks for update – Oleg_V Jun 01 '15 at 19:55

sum of ltree children per level

1 Answers1