How to get parent child with auto skip in sql oracle-CodePudding

I have a table in SQL Oracle with some steps.

step	auto_skip
0
10
20	skip
21	skip
30

I want to get a table with child and parent. The parent is the next step. It is pretty easy to do with a lead:

select step as child,LEAD (step,1) OVER (ORDER BY step) AS parent from STEPS

but this is the tricky part. When there is "skip" value in the auto_skip column, a second child/parent has to been made. Then we had to take the next step without a skip value. so the result of my child/parent has to be for this example:

child	parent
0	10
10	20
20	21
21	30
30
10	30

Added this to be clearer: The skip can be sometimes different. When the same skip is added, the next step is till the step has different skip or no skip.

For example:

step	auto_skip
0
10
20	skip1
21	skip1
22	skip2
30

should become

child	parent
0	10
10	20
10	22
21	22
21	30
22	30
20	21
30

CodePudding user response：

How about this (according to your sample data & result). if your data is very large, there maybe better solutions out there. Also you did not specify if 30 is also a parent of 20:

with steps as (
    select 0 step, cast(null as varchar2(10)) auto_skip from dual
    union ALL
    select 10,null from dual
    union all
    select 20, 'skip' from dual
    union all
    select 21,'skip' from dual
    union all 
    select 30 ,null from dual
)
select step as child,LEAD (step,1) OVER (ORDER BY step) AS parent from STEPS
union
select step as child,LEAD (step,1) OVER (ORDER BY step) AS parent from STEPS where auto_skip is null;

CHILD PARENT 
----- ------ 
    0     10 
   10     20 
   20     21 
   21     30 
   30        
   10     30

CodePudding user response：

Try this solution, working by UNION the 2 different ways to calculate the fathers, the second one works by recursion and must make a distinction between several skips or only one. It works on the test data but should be checked on more data.

with data(step,auto_skip) as (
    select 0, null from dual union all
    select 10, null  from dual union all
    select 20, 'skip1' from dual union all
    select 21, 'skip1' from dual union all
    select 22, 'skip2' from dual union all
    select 30, null  from dual -- union all
),
pdata as (
    select d.* from (
        select d.step, d.auto_skip, 
            lead(d.step,1) over(order by d.step) as natural_father,
            lead(d.auto_skip,1) over(order by d.step) as natural_father_skip
        from data d
    ) d
),
rdata as (
    select level as lvl, connect_by_isleaf as isleaf, connect_by_root(step) as rstep, d.* 
    from pdata d
    start with natural_father_skip <> nvl(auto_skip,'*')
    connect by 
        prior natural_father = step and prior natural_father_skip = auto_skip
)
select * from (
    select step as child, natural_father as parent from pdata
    union all
    select rstep, case when lvl > 2 then step else natural_father end from rdata
    where isleaf = 1 
)
order by child, parent
;

0   10
10  20
10  22
20  21
21  22
21  30
22  30
30