I have a hierarchical structure defined by level and order of elements. Is it possible to create "parent_id" column with Oracle SQL without using procedures?

I need to generate red values:

test data:

with t as
    (
    select 1 id, 'element1' name, 1 level_ from dual union all
    select 2 id, 'element2' name, 2 level_ from dual union all
    select 3 id, 'element3' name, 3 level_ from dual union all
    select 4 id, 'element4' name, 3 level_ from dual union all
    select 5 id, 'element5' name, 3 level_ from dual union all
    select 6 id, 'element6' name, 3 level_ from dual union all
    select 7 id, 'element7' name, 2 level_ from dual union all
    select 8 id, 'element8' name, 3 level_ from dual union all
    select 9 id, 'element9' name, 4 level_ from dual union all
    select 10 id, 'element10' name, 4 level_ from dual union all
    select 11 id, 'element11' name, 1 level_ from dual union all
    select 12 id, 'element12' name, 2 level_ from dual union all
    select 13 id, 'element13' name, 3 level_ from dual union all
    select 14 id, 'element14' name, 4 level_ from dual union all
    select 15 id, 'element15' name, 4 level_ from dual union all
    select 16 id, 'element16' name, 3 level_ from dual union all
    select 17 id, 'element17' name, 4 level_ from dual union all
    select 18 id, 'element18' name, 4 level_ from dual union all
    select 19 id, 'element19' name, 1 level_ from dual
    )
select * from t

CodePudding user response：

From Oracle 12, you can use MATCH_RECOGNIZE:

select *
from   t
MATCH_RECOGNIZE (
  ORDER BY id DESC
  MEASURES
    child.id   AS id,
    child.name AS name,
    child.lvl  AS lvl,
    parent.id  AS parent_id
  ONE ROW PER MATCH
  AFTER MATCH SKIP TO NEXT ROW
  PATTERN (child ancestors*? (parent | $))
  DEFINE
    parent AS lvl = child.lvl - 1
)
ORDER BY id

Or, again from Oracle 12, a LATERAL join:

select *
from   t c
       LEFT OUTER JOIN LATERAL(
         SELECT p.id AS parent_id
         FROM   t p
         WHERE  c.id  > p.id
         AND    c.lvl = p.lvl   1
         ORDER BY id DESC
         FETCH FIRST ROW ONLY
       )
       ON (1 = 1)
ORDER BY id

Or, in earlier versions:

SELECT id, name, lvl, parent_id
FROM   (
  SELECT c.*,
         p.id AS parent_id,
         ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY p.id DESC) AS rn
  FROM   t c
         LEFT OUTER JOIN t p
         ON (c.id  > p.id AND c.lvl = p.lvl   1)
)
WHERE  rn = 1
ORDER BY id

Which, for the sample data:

CREATE TABLE t (id, name, lvl ) as
  select  1, 'element1',  1 from dual union all
  select  2, 'element2',  2 from dual union all
  select  3, 'element3',  3 from dual union all
  select  4, 'element4',  3 from dual union all
  select  5, 'element5',  3 from dual union all
  select  6, 'element6',  3 from dual union all
  select  7, 'element7',  2 from dual union all
  select  8, 'element8',  3 from dual union all
  select  9, 'element9',  4 from dual union all
  select 10, 'element10', 4 from dual union all
  select 11, 'element11', 1 from dual union all
  select 12, 'element12', 2 from dual union all
  select 13, 'element13', 3 from dual union all
  select 14, 'element14', 4 from dual union all
  select 15, 'element15', 4 from dual union all
  select 16, 'element16', 3 from dual union all
  select 17, 'element17', 4 from dual union all
  select 18, 'element18', 4 from dual union all
  select 19, 'element19', 1 from dual;

All output:

ID	NAME	LVL	PARENT_ID
1	element1	1	null
2	element2	2	1
3	element3	3	2
4	element4	3	2
5	element5	3	2
6	element6	3	2
7	element7	2	1
8	element8	3	7
9	element9	4	8
10	element10	4	8
11	element11	1	null
12	element12	2	11
13	element13	3	12
14	element14	4	13
15	element15	4	13
16	element16	3	12
17	element17	4	16
18	element18	4	16
19	element19	1	null

db<>fiddle here