Database version: 19.14.0.0.0
The tree and the desired result
Create table and data
drop table sc_tree purge;
create table sc_tree (
superior_key number(2,0) not null
, dependent_key number(2,0) not null
, constraint sc_tree_pk primary key ( superior_key, dependent_key )
)
/
insert into sc_tree values (1, 1);
insert into sc_tree values (1, 2);
insert into sc_tree values (1, 5);
insert into sc_tree values (2, 3);
insert into sc_tree values (2, 4);
insert into sc_tree values (5, 6);
insert into sc_tree values (6, 7);
insert into sc_tree values (6, 8);
My solution
with
s1 as (
select rownum as rn
, ltrim(sys_connect_by_path(dependent_key,'-'),'-') as path
from sc_tree t1
where 1=1
and connect_by_isleaf = 1
start with superior_key = dependent_key
connect by /*nocycle*/ superior_key = prior dependent_key
and superior_key != dependent_key
)
, s2 as (
select path
, level as lvl
, regexp_substr(path,'[^-]+',1,level) as node
from s1
connect by level <= regexp_count(path,'-')+1
and prior path = path
and prior dbms_random.value is not null
)
select distinct
t1.node as superior_key
, t2.node as dependent_key
, t2.lvl-t1.lvl as levels_removed
from s2 t1
, s2 t2
where 1=1
and t1.path = t2.path
and t1.lvl <= t2.lvl
order by superior_key, dependent_key
/
This solution uses sys_connect_by_path to get the 4 paths. Then I "unwind" the paths into rows. Then I use cross-join and distinct to arrive at the final result. For larger data, this could become a resource hog. So, I am wondering if there is a better SQL or PL/SQL solution.