How to make SQL query with grouping in each node of an hierarchy tree?

Question

There is a table with an usual "child-to-parent" structure, where each node has weight.

TREE_TABLE
----------------------------------------
   id  | parent_id  |  name    | weight
----------------------------------------
   1   |  NULL      |   N1     |   51
   2   |  1         |   N12    |   62
   3   |  1         |   N13    |   73
   4   |  2         |   N124   |   84
   5   |  2         |   N125   |   95

// for convenience the "name" column shows path from a node to the root node

How to build a SQL query that results in a set of rows where each row represents grouping per particular node and its children?

Please, use any SQL dialect on your choice. Just need a general idea or a prototype of the solution.

To solve this problem, I am experimenting with GROUPING SETS and ROLLUP report, but can't figure out the way of handling "dynamic" number of grouping levels.

Example of expected query result:

RESULT
--------------------------
 name  |  summary_weight
--------------------------
  N1   |   (51+62+73+84+95)
  N12  |   (62+84+95)    
  N124 |   (84)
  N125 |   (95)
  N13  |   (73)

@JoachimIsaksson It mush have been a typo. Of course, N13 is just (73) as it does not include the parent weight. Thank you. — diziaq, Jul 09 '21 at 08:32
Do you want the values to be `SUM` as `(51+62+73+84+95) = 365` or as `TEXT` `(51+62+73+84+95)` ? — Rui Costa, Jul 09 '21 at 09:21
@RuiCosta Actually there is no difference, because generally it could be any aggregation function stanadard (sum, min, max) or custom. For simplicity let's assume that "summary_weight" is a SUM of the values (like 365 in the example). — diziaq, Jul 09 '21 at 09:24
Oracle or Postgres? Please do not tag DBMS products that are not relevant — , Jul 09 '21 at 09:53

Krasnoslobodtsev Sergei · Accepted Answer · 2021-07-09T09:46:35.603

for example:

with 
 datum(id,parent_id,name,weight)
 as
 (
 select 1,NULL,'N1',51 from dual union all
 select 2 ,  1 , 'N12' , 62  from dual union all
 select 3 ,  1 , 'N13' , 73 from dual union all
 select 4 ,  2 , 'N124' , 84 from dual union all
 select 5 ,  2 , 'N125' , 95 from dual
 ),
 step1 as
 (
 select id,parent_id,name,weight, connect_by_root name  root,connect_by_isleaf is_parent 
 from datum
 connect by prior id = parent_id 
 )
select root,sum(weight)  sum_w,
 '('||listagg(weight,'+') within group(order by null) ||')' str_w,
 '('||listagg(name,'+') within group(order by null) ||')' str_n
from step1
group by root
order by 1;

link: Hierarchical Queries

Simply genius. Here is the rare case when 'START WITH' is redundant )) — diziaq, Jul 09 '21 at 10:22

score 1 · Answer 2 · answered Jul 09 '21 at 09:24

If you want to SUM up, you can use this option:

SELECT 
  name, 
  (SELECT 
     sum(t2.weight) 
   FROM tree t2 
   WHERE t2.name LIKE t1.name || '%' )
FROM tree t1
ORDER BY rpad(name,10,'0');

If you want to concatenate the text, you can use this:

SELECT 
  name, 
  (SELECT 
    '(' || string_agg(t2.weight || '','+') || ')' 
   FROM tree t2 
   WHERE t2.name LIKE t1.name || '%' )
FROM tree t1
ORDER BY rpad(name,10,'0');

How to make SQL query with grouping in each node of an hierarchy tree?

2 Answers2