1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
|
SQL> ed
Wrote file afiedt.sql
1 WITH article AS (
2 SELECT 1 as id, NULL as parent, 'A1' as lbl, 1 as p FROM DUAL UNION ALL
3 SELECT 2 as id, 1 as parent, 'A2' as lbl, 2 as p FROM DUAL UNION ALL
4 SELECT 3 as id, 1 as parent, 'A3' as lbl, 3 as p FROM DUAL UNION ALL
5 SELECT 4 as id, 1 as parent, 'A4' as lbl, 3 as p FROM DUAL UNION ALL
6 SELECT 5 as id, 1 as parent, 'A5' as lbl, 2 as p FROM DUAL UNION ALL
7 SELECT 6 as id, 2 as parent, 'A6' as lbl, 7 as p FROM DUAL UNION ALL
8 SELECT 7 as id, 2 as parent, 'A7' as lbl, 8 as p FROM DUAL UNION ALL
9 SELECT 8 as id, 3 as parent, 'A8' as lbl, 8 as p FROM DUAL UNION ALL
10 SELECT 9 as id, 4 as parent, 'A9' as lbl, 6 as p FROM DUAL UNION ALL
11 SELECT 10 as id, 6 as parent, 'A10' lbl, 9 as p FROM DUAL UNION ALL
12 SELECT 11 as id, 8 as parent, 'A11' as lbl, 9 as p FROM DUAL
13 )
14 , tmp AS (
15 SELECT id, sys_connect_by_path(lbl, '-') as pth, connect_by_isleaf as lf, connect_by_root p rootp, p, sys_connect_by_path(p, '-') as pthp, level l
16 FROM article a
17 CONNECT BY prior id = parent
18 )
19 , tmp2 as (
20 select a.*, exp(sum(ln(rootp)) over (partition BY id ORDER BY l) ) as coef, row_number() over(partition by id order by l desc) rk
21 from tmp a
22 )
23 select * from tmp2
24* where rk = 1
SQL> /
ID PTH LF ROOTP P PTHP L COEF RK
---------- ------------------------------ ---------- ---------- ---------- -------------------- ---------- ---------- ----------
1 -A1 0 1 1 -1 1 1 1
2 -A1-A2 0 1 2 -1-2 2 2 1
3 -A1-A3 0 1 3 -1-3 2 3 1
4 -A1-A4 0 1 3 -1-3 2 3 1
5 -A1-A5 1 1 2 -1-2 2 2 1
6 -A1-A2-A6 0 1 7 -1-2-7 3 14 1
7 -A1-A2-A7 1 1 8 -1-2-8 3 16 1
8 -A1-A3-A8 0 1 8 -1-3-8 3 24 1
9 -A1-A4-A9 1 1 6 -1-3-6 3 18 1
10 -A1-A2-A6-A10 1 1 9 -1-2-7-9 4 126 1
11 -A1-A3-A8-A11 1 1 9 -1-3-8-9 4 216 1
11 rows selected. |
Partager