Oracle Live SQL - Script: Ask TOM Office Hours: Find the Hierarchical Mean Solutions

Statement 1

create table matriz_h (   
    matriz_key      number constraint matriz_h_matriz_key_pk primary key,   
    matriz_key_f    number,   
    label           varchar2(100) not null,   
    lvl             number not null   
)

Table created.

Statement 2

create table totales (   
    matriz_key      number not null,   
    period_key      number,   
    paa             number   
)

Table created.

Statement 3

CREATE OR REPLACE VIEW v_tree   
AS  
   with groupeddata as (    
      select mh.matriz_key,    
             matriz_key_f,    
             coalesce(paa,0) paa,    
             --lvl,    
             label    
      from   matriz_h mh,    
             totales tot    
      where mh.matriz_key = tot.matriz_key (+)    
   ), tree as (    
     select matriz_key,    
            matriz_key_f,    
            paa,    
            level lvl,    
            label,    
            sys_connect_by_path ( matriz_key, '.' ) pth,    
            connect_by_root matriz_key  rt,    
            connect_by_isleaf is_lf   
     from groupeddata a1     
     start with matriz_key_f is null    
     connect by prior matriz_key = matriz_key_f  
  )  
  SELECT * FROM tree

View created.

Statement 4

begin  
  insert into matriz_h values (1, null,'1', 1);    
  insert into matriz_h values (2, 1,'1.1', 2);    
  insert into matriz_h values (3, 2, '1.1.1', 3);    
  insert into matriz_h values (4, 3, '1.1.1.1', 4);    
  insert into matriz_h values (5, 4, '1.1.1.1.1', 5);    
  insert into matriz_h values (6, 4, '1.1.1.1.2', 5);    
  insert into matriz_h values (7, 4, '1.1.1.1.3', 5);    
  insert into matriz_h values (8, 4, '1.1.1.1.4', 5);    
  insert into matriz_h values (9, 3, '1.1.1.2', 4);    
  insert into matriz_h values (10, 9, '1.1.1.2.1', 5);    
  insert into matriz_h values (11, 9, '1.1.1.2.2', 5);    
  insert into matriz_h values (12, 9, '1.1.1.2.3', 5);    
  insert into matriz_h values (13, 9, '1.1.1.2.4', 5);    
  insert into matriz_h values (14, 2, '1.1.2', 3);    
  insert into matriz_h values (15, 14, '1.1.2.1', 4);    
  insert into matriz_h values (16, 15, '1.1.2.1.1', 5);    
  insert into matriz_h values (17, 15, '1.1.2.1.2', 5);    
  insert into matriz_h values (18, 15, '1.1.2.1.3', 5);  
    
  insert into totales values (5, 1, 10);  
  insert into totales values (7, 1, 50);  
  insert into totales values (8, 1, 40);    
  insert into totales values (9, 1, 20);    
  insert into totales values (11, 1, 35);    
  insert into totales values (13, 1, 45);    
  insert into totales values (15, 1, 35);    
  insert into totales values (16, 1, 100);    
  insert into totales values (17, 1, 20);    
  insert into totales values (18, 1, 20);  
    
  insert into matriz_h values (19, null,'2', 1);    
  insert into matriz_h values (20, 19,'2.1', 2);    
  insert into matriz_h values (21, 20,'2.1.1', 3);    
  insert into matriz_h values (22, 21,'2.1.1.1', 4);    
  insert into matriz_h values (23, 22,'2.1.1.1.1', 5);    
  insert into matriz_h values (24, 22,'2.1.1.1.2', 5);    
  insert into matriz_h values (25, 22,'2.1.1.1.3', 5);    
  insert into matriz_h values (26, 20,'2.1.2', 3);    
  insert into matriz_h values (27, 26,'2.1.2.1', 4);    
  insert into matriz_h values (28, 27,'2.1.2.1.1', 5);    
  insert into matriz_h values (29, 27,'2.1.2.1.2', 5);    
  insert into matriz_h values (30, 27,'2.1.2.1.3', 5);  
    
  insert into totales values (23, 1, 30);    
  insert into totales values (25, 1, 70);    
  insert into totales values (27, 1, 55);    
  insert into totales values (28, 1, 65);    
  insert into totales values (29, 1, 75);  
    
end;

Statement processed.

Statement 5

commit

Statement processed.

Statement 6

Query to Build the Tree

with groupeddata as (   
/*  
Expected output  
MATRIZ_KEY MATRIZ_KEY_F LABEL     LVL PAA AVG_PAA  
1           -           1         1     0   31.88  
2          1            1.1       2     0   31.88  
3          2            1.1.1     3     0   20.00  
4          3            1.1.1.1   4     0   20.00  
5          4            1.1.1.1.1 5    10    0.00  
6          4            1.1.1.1.2 5     0    0.00  
7          4            1.1.1.1.3 5    50    0.00  
8          4            1.1.1.1.4 5    40    0.00  
9          3            1.1.1.2   4    20   20.00  
10          9           1.1.1.2.1 5     0    0.00  
11          9           1.1.1.2.2 5    35    0.00  
12          9           1.1.1.2.3 5     0    0.00  
13          9           1.1.1.2.4 5    45    0.00  
14          3           1.1.2     3     0   43.75  
15          14          1.1.2.1   4    35   43.75  
16          15          1.1.2.1.1 5   100    0.00  
17          15          1.1.2.1.2 5    20    0.00  
18          15          1.1.2.1.3 5    20    0.00  
19           -          2         1     0   36.88  
20          19          2.1       2     0   36.88  
21          20          2.1.1     3     0   25.00  
22          21          2.1.1.1   4     0   25.00  
23          22          2.1.1.1.1 5    30    0.00  
24          22          2.1.1.1.2 5     0    0.00  
25          22          2.1.1.1.3 5    70    0.00  
26          20          2.1.2     3     0   48.75  
27          26          2.1.2.1   4    55   48.75  
28          27          2.1.2.1.1 5    65    0.00  
29          27          2.1.2.1.2 5    75    0.00  
30          27          2.1.2.1.3 5     0    0.00  
*/  
    select mh.matriz_key,   
           matriz_key_f,   
           coalesce(paa,0) paa,   
           lvl,   
           label   
      from matriz_h mh,   
           totales tot   
     where mh.matriz_key = tot.matriz_key (+)   
), tree as (   
  select matriz_key,   
         matriz_key_f,   
         paa,   
         level lvl,   
         lvl l,   
         label,   
         sys_connect_by_path ( matriz_key, '.' ) pth,   
         connect_by_root matriz_key  rt,   
         connect_by_isleaf is_lf   
    from groupeddata a1    
    start with matriz_key_f is null   
    connect by prior matriz_key = matriz_key_f   
)   
  select *   
  from   tree


MATRIZ_KEY MATRIZ_KEY_F PAA LVL L LABEL PTH RT IS_LF
1  - 0 1 1 1 .1 1 0
2 1 0 2 2 1.1 .1.2 1 0
3 2 0 3 3 1.1.1 .1.2.3 1 0
4 3 0 4 4 1.1.1.1 .1.2.3.4 1 0
5 4 10 5 5 1.1.1.1.1 .1.2.3.4.5 1 1
6 4 0 5 5 1.1.1.1.2 .1.2.3.4.6 1 1
7 4 50 5 5 1.1.1.1.3 .1.2.3.4.7 1 1
8 4 40 5 5 1.1.1.1.4 .1.2.3.4.8 1 1
9 3 20 4 4 1.1.1.2 .1.2.3.9 1 0
10 9 0 5 5 1.1.1.2.1 .1.2.3.9.10 1 1
11 9 35 5 5 1.1.1.2.2 .1.2.3.9.11 1 1
12 9 0 5 5 1.1.1.2.3 .1.2.3.9.12 1 1
13 9 45 5 5 1.1.1.2.4 .1.2.3.9.13 1 1
14 2 0 3 3 1.1.2 .1.2.14 1 0
15 14 35 4 4 1.1.2.1 .1.2.14.15 1 0
16 15 100 5 5 1.1.2.1.1 .1.2.14.15.16 1 1
17 15 20 5 5 1.1.2.1.2 .1.2.14.15.17 1 1
18 15 20 5 5 1.1.2.1.3 .1.2.14.15.18 1 1
19  - 0 1 1 2 .19 19 0
20 19 0 2 2 2.1 .19.20 19 0
21 20 0 3 3 2.1.1 .19.20.21 19 0
22 21 0 4 4 2.1.1.1 .19.20.21.22 19 0
23 22 30 5 5 2.1.1.1.1 .19.20.21.22.23 19 1
24 22 0 5 5 2.1.1.1.2 .19.20.21.22.24 19 1
25 22 70 5 5 2.1.1.1.3 .19.20.21.22.25 19 1
26 20 0 3 3 2.1.2 .19.20.26 19 0
27 26 55 4 4 2.1.2.1 .19.20.26.27 19 0
28 27 65 5 5 2.1.2.1.1 .19.20.26.27.28 19 1
29 27 75 5 5 2.1.2.1.2 .19.20.26.27.29 19 1
30 27 0 5 5 2.1.2.1.3 .19.20.26.27.30 19 1

30 rows selected.

MATRIZ_KEY	MATRIZ_KEY_F	PAA	LVL	L	LABEL	PTH	RT	IS_LF
1	-	0	1	1	1	.1	1	0
2	1	0	2	2	1.1	.1.2	1	0
3	2	0	3	3	1.1.1	.1.2.3	1	0
4	3	0	4	4	1.1.1.1	.1.2.3.4	1	0
5	4	10	5	5	1.1.1.1.1	.1.2.3.4.5	1	1
6	4	0	5	5	1.1.1.1.2	.1.2.3.4.6	1	1
7	4	50	5	5	1.1.1.1.3	.1.2.3.4.7	1	1
8	4	40	5	5	1.1.1.1.4	.1.2.3.4.8	1	1
9	3	20	4	4	1.1.1.2	.1.2.3.9	1	0
10	9	0	5	5	1.1.1.2.1	.1.2.3.9.10	1	1
11	9	35	5	5	1.1.1.2.2	.1.2.3.9.11	1	1
12	9	0	5	5	1.1.1.2.3	.1.2.3.9.12	1	1
13	9	45	5	5	1.1.1.2.4	.1.2.3.9.13	1	1
14	2	0	3	3	1.1.2	.1.2.14	1	0
15	14	35	4	4	1.1.2.1	.1.2.14.15	1	0
16	15	100	5	5	1.1.2.1.1	.1.2.14.15.16	1	1
17	15	20	5	5	1.1.2.1.2	.1.2.14.15.17	1	1
18	15	20	5	5	1.1.2.1.3	.1.2.14.15.18	1	1
19	-	0	1	1	2	.19	19	0
20	19	0	2	2	2.1	.19.20	19	0
21	20	0	3	3	2.1.1	.19.20.21	19	0
22	21	0	4	4	2.1.1.1	.19.20.21.22	19	0
23	22	30	5	5	2.1.1.1.1	.19.20.21.22.23	19	1
24	22	0	5	5	2.1.1.1.2	.19.20.21.22.24	19	1
25	22	70	5	5	2.1.1.1.3	.19.20.21.22.25	19	1
26	20	0	3	3	2.1.2	.19.20.26	19	0
27	26	55	4	4	2.1.2.1	.19.20.26.27	19	0
28	27	65	5	5	2.1.2.1.1	.19.20.26.27.28	19	1
29	27	75	5	5	2.1.2.1.2	.19.20.26.27.29	19	1
30	27	0	5	5	2.1.2.1.3	.19.20.26.27.30	19	1

Statement 7

CREATE OR REPLACE PACKAGE avg_pkg   
AS  
   CURSOR c_tree   
   IS  
   select t.*,  
          0  paa_cnt,  
          0  paa_avg  
   from v_tree  t;  
  
  
   TYPE t_rec IS RECORD  
       ( matriz_key       v_tree.matriz_key%TYPE,  
         matriz_key_f     v_tree.matriz_key_f%TYPE,  
         paa              v_tree.paa%TYPE,  
         lvl              v_tree.lvl%TYPE,  
         label            v_tree.label%TYPE,  
         pth              v_tree.pth%TYPE,  
         rt               v_tree.rt%TYPE,  
         is_lf            v_tree.is_lf%TYPE,  
         paa_cnt          NUMBER,  
         paa_avg          NUMBER  
       );  
  
   TYPE t_result IS TABLE OF t_rec;  
  
   FUNCTION f_result RETURN t_result PIPELINED ;  
END avg_pkg;

Package created.

Statement 8

Pipelined Table Function Solution

CREATE OR REPLACE PACKAGE BODY avg_pkg   
AS   
   
  FUNCTION f_result RETURN t_result PIPELINED    
  IS   
      /* for making the solution more generic, use varchar2 keys */   
      l_key  VARCHAR2(20);   
      SUBTYPE s_key IS l_key%TYPE;   
   
      TYPE t_calc_rec IS RECORD ( paa_cnt  NUMBER,   
                                  paa_avg  NUMBER ) ;   
      TYPE t_calc_tab IS TABLE OF t_calc_rec   
           INDEX BY s_key ;   
    
      l_result       t_result ;   
      l_calc_tab     t_calc_tab ;   
   
             
      l_prev_lvl     PLS_INTEGER;   
      l_prev_is_lf   PLS_INTEGER;         
  BEGIN   
      -- retrive all the rows   
      OPEN c_tree;   
      FETCH c_tree BULK COLLECT INTO l_result;   
      CLOSE c_tree;   
   
      -- initialize the calc collection   
      FOR i IN 1 .. l_result.COUNT    
      LOOP   
          l_calc_tab( TO_CHAR(l_result(i).matriz_key) ) := t_calc_rec (0, 0) ;   
      END LOOP;   
   
      -- traverse the tree in reverse order, making the calculations "on the go"   
      FOR i IN REVERSE 1 .. l_result.COUNT   
      LOOP             
          DBMS_OUTPUT.put_line ('Processing node '|| l_result(i).label||', is_lf = '||l_result(i).is_lf);   
   
          -- if a leaf node, just add it to its parent   
          IF l_result(i).is_lf = 1 THEN                
             l_key := TO_CHAR(l_result(i).matriz_key_f);   
             IF l_key IS NOT NULL THEN   
                l_calc_tab ( l_key ).paa_cnt := l_calc_tab ( l_key ).paa_cnt + 1;   
                l_calc_tab ( l_key ).paa_avg := l_calc_tab ( l_key ).paa_avg + l_result(i).paa;   
             END IF;   
             --   
             -- the result is already set, so pipe the row "as is"   
             PIPE ROW ( l_result(i) );   
          ELSIF   
             -- moving up in the tree means we can finish the current node   
             l_result(i).lvl < l_prev_lvl THEN   
   
             -- finish current node   
             l_key := TO_CHAR(l_result(i).matriz_key);   
   
             IF l_prev_is_lf = 1 THEN   
                -- if parent of a leaf node, add its own data                   
                l_calc_tab ( l_key ).paa_cnt := l_calc_tab ( l_key ).paa_cnt + 1;   
                l_calc_tab ( l_key ).paa_avg := (l_calc_tab ( l_key ).paa_avg + l_result(i).paa) / l_calc_tab ( l_key ).paa_cnt ;   
             ELSE   
                -- else just finish the avg   
                l_calc_tab ( l_key ).paa_avg := l_calc_tab ( l_key ).paa_avg / l_calc_tab ( l_key ).paa_cnt ;   
             END IF;   
   
             l_result(i).paa_cnt := l_calc_tab ( l_key ).paa_cnt;   
             l_result(i).paa_avg := l_calc_tab ( l_key ).paa_avg;   
   
             -- add result to its parent   
             l_key := TO_CHAR(l_result(i).matriz_key_f);                
             IF l_key IS NOT NULL THEN   
                l_calc_tab ( l_key ).paa_cnt := l_calc_tab ( l_key ).paa_cnt + 1;   
                l_calc_tab ( l_key ).paa_avg := l_calc_tab ( l_key ).paa_avg + l_result(i).paa_avg;   
             END IF;   
             --   
             PIPE ROW ( l_result(i) );   
          END IF;   
   
          -- keep for next iteration   
          l_prev_lvl   := l_result(i).lvl;   
          l_prev_is_lf := l_result(i).is_lf;   
      END LOOP;   
   
      RETURN;   
   END f_result;   
END avg_pkg;

Package Body created.

Statement 9

SELECT *   
FROM   TABLE ( 
  avg_pkg.f_result () 
)  
ORDER  BY matriz_key


MATRIZ_KEY MATRIZ_KEY_F PAA LVL LABEL PTH RT IS_LF PAA_CNT PAA_AVG
1  - 0 1 1 .1 1 0 1 31.875
2 1 0 2 1.1 .1.2 1 0 2 31.875
3 2 0 3 1.1.1 .1.2.3 1 0 2 20
4 3 0 4 1.1.1.1 .1.2.3.4 1 0 5 20
5 4 10 5 1.1.1.1.1 .1.2.3.4.5 1 1 0 0
6 4 0 5 1.1.1.1.2 .1.2.3.4.6 1 1 0 0
7 4 50 5 1.1.1.1.3 .1.2.3.4.7 1 1 0 0
8 4 40 5 1.1.1.1.4 .1.2.3.4.8 1 1 0 0
9 3 20 4 1.1.1.2 .1.2.3.9 1 0 5 20
10 9 0 5 1.1.1.2.1 .1.2.3.9.10 1 1 0 0
11 9 35 5 1.1.1.2.2 .1.2.3.9.11 1 1 0 0
12 9 0 5 1.1.1.2.3 .1.2.3.9.12 1 1 0 0
13 9 45 5 1.1.1.2.4 .1.2.3.9.13 1 1 0 0
14 2 0 3 1.1.2 .1.2.14 1 0 1 43.75
15 14 35 4 1.1.2.1 .1.2.14.15 1 0 4 43.75
16 15 100 5 1.1.2.1.1 .1.2.14.15.16 1 1 0 0
17 15 20 5 1.1.2.1.2 .1.2.14.15.17 1 1 0 0
18 15 20 5 1.1.2.1.3 .1.2.14.15.18 1 1 0 0
19  - 0 1 2 .19 19 0 1 36.875
20 19 0 2 2.1 .19.20 19 0 2 36.875
21 20 0 3 2.1.1 .19.20.21 19 0 1 25
22 21 0 4 2.1.1.1 .19.20.21.22 19 0 4 25
23 22 30 5 2.1.1.1.1 .19.20.21.22.23 19 1 0 0
24 22 0 5 2.1.1.1.2 .19.20.21.22.24 19 1 0 0
25 22 70 5 2.1.1.1.3 .19.20.21.22.25 19 1 0 0
26 20 0 3 2.1.2 .19.20.26 19 0 1 48.75
27 26 55 4 2.1.2.1 .19.20.26.27 19 0 4 48.75
28 27 65 5 2.1.2.1.1 .19.20.26.27.28 19 1 0 0
29 27 75 5 2.1.2.1.2 .19.20.26.27.29 19 1 0 0
30 27 0 5 2.1.2.1.3 .19.20.26.27.30 19 1 0 0

30 rows selected.
Processing node 2.1.2.1.3, is_lf = 1
Processing node 2.1.2.1.2, is_lf = 1
Processing node 2.1.2.1.1, is_lf = 1
Processing node 2.1.2.1, is_lf = 0
Processing node 2.1.2, is_lf = 0
Processing node 2.1.1.1.3, is_lf = 1
Processing node 2.1.1.1.2, is_lf = 1
Processing node 2.1.1.1.1, is_lf = 1
Processing node 2.1.1.1, is_lf = 0
Processing node 2.1.1, is_lf = 0
Processing node 2.1, is_lf = 0
Processing node 2, is_lf = 0
Processing node 1.1.2.1.3, is_lf = 1
Processing node 1.1.2.1.2, is_lf = 1
Processing node 1.1.2.1.1, is_lf = 1
Processing node 1.1.2.1, is_lf = 0
Processing node 1.1.2, is_lf = 0
Processing node 1.1.1.2.4, is_lf = 1
Processing node 1.1.1.2.3, is_lf = 1
Processing node 1.1.1.2.2, is_lf = 1
Processing node 1.1.1.2.1, is_lf = 1
Processing node 1.1.1.2, is_lf = 0
Processing node 1.1.1.1.4, is_lf = 1
Processing node 1.1.1.1.3, is_lf = 1
Processing node 1.1.1.1.2, is_lf = 1
Processing node 1.1.1.1.1, is_lf = 1
Processing node 1.1.1.1, is_lf = 0
Processing node 1.1.1, is_lf = 0
Processing node 1.1, is_lf = 0
Processing node 1, is_lf = 0

MATRIZ_KEY	MATRIZ_KEY_F	PAA	LVL	LABEL	PTH	RT	IS_LF	PAA_CNT	PAA_AVG
1	-	0	1	1	.1	1	0	1	31.875
2	1	0	2	1.1	.1.2	1	0	2	31.875
3	2	0	3	1.1.1	.1.2.3	1	0	2	20
4	3	0	4	1.1.1.1	.1.2.3.4	1	0	5	20
5	4	10	5	1.1.1.1.1	.1.2.3.4.5	1	1	0	0
6	4	0	5	1.1.1.1.2	.1.2.3.4.6	1	1	0	0
7	4	50	5	1.1.1.1.3	.1.2.3.4.7	1	1	0	0
8	4	40	5	1.1.1.1.4	.1.2.3.4.8	1	1	0	0
9	3	20	4	1.1.1.2	.1.2.3.9	1	0	5	20
10	9	0	5	1.1.1.2.1	.1.2.3.9.10	1	1	0	0
11	9	35	5	1.1.1.2.2	.1.2.3.9.11	1	1	0	0
12	9	0	5	1.1.1.2.3	.1.2.3.9.12	1	1	0	0
13	9	45	5	1.1.1.2.4	.1.2.3.9.13	1	1	0	0
14	2	0	3	1.1.2	.1.2.14	1	0	1	43.75
15	14	35	4	1.1.2.1	.1.2.14.15	1	0	4	43.75
16	15	100	5	1.1.2.1.1	.1.2.14.15.16	1	1	0	0
17	15	20	5	1.1.2.1.2	.1.2.14.15.17	1	1	0	0
18	15	20	5	1.1.2.1.3	.1.2.14.15.18	1	1	0	0
19	-	0	1	2	.19	19	0	1	36.875
20	19	0	2	2.1	.19.20	19	0	2	36.875
21	20	0	3	2.1.1	.19.20.21	19	0	1	25
22	21	0	4	2.1.1.1	.19.20.21.22	19	0	4	25
23	22	30	5	2.1.1.1.1	.19.20.21.22.23	19	1	0	0
24	22	0	5	2.1.1.1.2	.19.20.21.22.24	19	1	0	0
25	22	70	5	2.1.1.1.3	.19.20.21.22.25	19	1	0	0
26	20	0	3	2.1.2	.19.20.26	19	0	1	48.75
27	26	55	4	2.1.2.1	.19.20.26.27	19	0	4	48.75
28	27	65	5	2.1.2.1.1	.19.20.26.27.28	19	1	0	0
29	27	75	5	2.1.2.1.2	.19.20.26.27.29	19	1	0	0
30	27	0	5	2.1.2.1.3	.19.20.26.27.30	19	1	0	0

Statement 10

Model Clause

with data as (   
  select matriz_key, matriz_key_f, label,   
    coalesce(paa,0) paa, 0 avg_paa   
  from matriz_h mh   
  left join totales tot using(matriz_key)   
), hier as (   
  select MATRIZ_KEY, MATRIZ_KEY_F, LABEL, level lvl, PAA, AVG_PAA   
  from data s   
  start with matriz_key_f is null   
  connect by matriz_key_f = prior matriz_key   
)   
select MATRIZ_KEY, MATRIZ_KEY_F, LABEL, LVL, PAA, AVG_PAA   
from hier   
model   
  reference l on (select 1 dim, max(lvl) lvl from hier)   
    dimension by (dim)   
    measures (lvl)   
  main m   
    dimension by (lvl, matriz_key_f, matriz_key)   
    measures (label, paa, avg_paa)   
    rules iterate (999) until iteration_number >= l.lvl[1]   
    (   
      avg_paa[l.lvl[1] - iteration_number, any, any] =    
      case iteration_number   
        when 0 then 0   
        when 1 then (paa[cv(),cv(),cv()] + sum(paa)[cv(lvl)+1, cv(matriz_key), any]) /   
          (1 + count(*)[cv(lvl)+1, cv(matriz_key), any])   
        else avg(avg_paa)[cv(lvl)+1, cv(matriz_key), any]   
      end   
    )   
order by MATRIZ_KEY


MATRIZ_KEY MATRIZ_KEY_F LABEL LVL PAA AVG_PAA
1  - 1 1 0 31.875
2 1 1.1 2 0 31.875
3 2 1.1.1 3 0 20
4 3 1.1.1.1 4 0 20
5 4 1.1.1.1.1 5 10 0
6 4 1.1.1.1.2 5 0 0
7 4 1.1.1.1.3 5 50 0
8 4 1.1.1.1.4 5 40 0
9 3 1.1.1.2 4 20 20
10 9 1.1.1.2.1 5 0 0
11 9 1.1.1.2.2 5 35 0
12 9 1.1.1.2.3 5 0 0
13 9 1.1.1.2.4 5 45 0
14 2 1.1.2 3 0 43.75
15 14 1.1.2.1 4 35 43.75
16 15 1.1.2.1.1 5 100 0
17 15 1.1.2.1.2 5 20 0
18 15 1.1.2.1.3 5 20 0
19  - 2 1 0 36.875
20 19 2.1 2 0 36.875
21 20 2.1.1 3 0 25
22 21 2.1.1.1 4 0 25
23 22 2.1.1.1.1 5 30 0
24 22 2.1.1.1.2 5 0 0
25 22 2.1.1.1.3 5 70 0
26 20 2.1.2 3 0 48.75
27 26 2.1.2.1 4 55 48.75
28 27 2.1.2.1.1 5 65 0
29 27 2.1.2.1.2 5 75 0
30 27 2.1.2.1.3 5 0 0

30 rows selected.

MATRIZ_KEY	MATRIZ_KEY_F	LABEL	LVL	PAA	AVG_PAA
1	-	1	1	0	31.875
2	1	1.1	2	0	31.875
3	2	1.1.1	3	0	20
4	3	1.1.1.1	4	0	20
5	4	1.1.1.1.1	5	10	0
6	4	1.1.1.1.2	5	0	0
7	4	1.1.1.1.3	5	50	0
8	4	1.1.1.1.4	5	40	0
9	3	1.1.1.2	4	20	20
10	9	1.1.1.2.1	5	0	0
11	9	1.1.1.2.2	5	35	0
12	9	1.1.1.2.3	5	0	0
13	9	1.1.1.2.4	5	45	0
14	2	1.1.2	3	0	43.75
15	14	1.1.2.1	4	35	43.75
16	15	1.1.2.1.1	5	100	0
17	15	1.1.2.1.2	5	20	0
18	15	1.1.2.1.3	5	20	0
19	-	2	1	0	36.875
20	19	2.1	2	0	36.875
21	20	2.1.1	3	0	25
22	21	2.1.1.1	4	0	25
23	22	2.1.1.1.1	5	30	0
24	22	2.1.1.1.2	5	0	0
25	22	2.1.1.1.3	5	70	0
26	20	2.1.2	3	0	48.75
27	26	2.1.2.1	4	55	48.75
28	27	2.1.2.1.1	5	65	0
29	27	2.1.2.1.2	5	75	0
30	27	2.1.2.1.3	5	0	0

Statement 11

Walk the Tree Twice

create or replace view solution as   
with groupeddata as (     
    select mh.matriz_key,     
           mh.matriz_key_f,     
           coalesce(tot.paa,0) paa,     
           mh.lvl,     
           mh.label     
      from matriz_h mh,     
           totales tot     
     where mh.matriz_key = tot.matriz_key (+)     
), tree (matriz_key, matriz_key_f, label, lvl, paa, avg_paa) as (   
  select a1.matriz_key,     
         a1.matriz_key_f,   
         a1.label,   
         a1.lvl,   
         a1.paa,   
         0   
    from groupeddata a1      
    where a1.matriz_key_f is null     
    union all   
  select a1.matriz_key,     
         a1.matriz_key_f,   
         a1.label,   
         a1.lvl,   
         a1.paa,   
         0   
    from groupeddata a1 join tree t on t.matriz_key = a1.matriz_key_f   
)  search depth first by matriz_key set o   
, tree1 as   
(   
  select t.*,    
     (   
       select 'X'   
       from dual   
       where not exists(  
         select null from tree t_   
         where  t_.label like t.label || '%'   
         and    length(t_.label) > length(t.label)  
       )   
    ) as isleaf   
  from      
     tree t   
), tree2 as (   
  select t.*,    
     (   
       select   
          'X'   
       from tree1 t1   
       where t1.isleaf = 'X' and t1.lvl = t.lvl + 1 and t1.label like t.label || '%' and rownum = 1   
    ) as isparentofleaf   
  from      
     tree1 t   
), tree3 as (   
select    
   t.*,   
   case   
     when t.isleaf = 'X' then 0   
     when t.isparentofleaf = 'X' then    
     (   
      select   
      avg(t_.paa)   
      from tree2 t_    
      where (t_.label like t.label || '%' and length(t_.label) > length(t.label))   
      or    t_.matriz_key = t.matriz_key   
     )           
   end as avg   
from tree2 t   
), tree4 as (   
select    
   t.*,   
   coalesce(t.avg,    
     (   
      select sum(distinct t_.avg) / count(distinct nullif(t_.avg, 0))   
      from tree3 t_    
      where (t_.label like t.label || '%' and length(t_.label) > length(t.label))   
     )      
   ) as avg_paa_calc   
from tree3 t   
)   
select t.matriz_key, t.matriz_key_f, t.label, t.lvl, t.paa, t.avg_paa_calc as avg_paa   
from tree4 t   
order by t.o

View created.

Statement 12

select * from solution


MATRIZ_KEY MATRIZ_KEY_F LABEL LVL PAA AVG_PAA
1  - 1 1 0 31.875
2 1 1.1 2 0 31.875
3 2 1.1.1 3 0 20
4 3 1.1.1.1 4 0 20
5 4 1.1.1.1.1 5 10 0
6 4 1.1.1.1.2 5 0 0
7 4 1.1.1.1.3 5 50 0
8 4 1.1.1.1.4 5 40 0
9 3 1.1.1.2 4 20 20
10 9 1.1.1.2.1 5 0 0
11 9 1.1.1.2.2 5 35 0
12 9 1.1.1.2.3 5 0 0
13 9 1.1.1.2.4 5 45 0
14 2 1.1.2 3 0 43.75
15 14 1.1.2.1 4 35 43.75
16 15 1.1.2.1.1 5 100 0
17 15 1.1.2.1.2 5 20 0
18 15 1.1.2.1.3 5 20 0
19  - 2 1 0 36.875
20 19 2.1 2 0 36.875
21 20 2.1.1 3 0 25
22 21 2.1.1.1 4 0 25
23 22 2.1.1.1.1 5 30 0
24 22 2.1.1.1.2 5 0 0
25 22 2.1.1.1.3 5 70 0
26 20 2.1.2 3 0 48.75
27 26 2.1.2.1 4 55 48.75
28 27 2.1.2.1.1 5 65 0
29 27 2.1.2.1.2 5 75 0
30 27 2.1.2.1.3 5 0 0

30 rows selected.

Ask TOM Office Hours: Find the Hierarchical Mean Solutions

Additional Information