再帰with句04 Oracleのconnect_by_IsLeafを模倣

SQLパズル

IsLeafT
ID  OyaID
--  -----
 1   null
 2      1
 3      2
 4      3
 5      1
 6      5
 7      2
20   null
21     20
22     21

Oracleの下記のクエリと同じ結果を取得する。

select ID,OyaID,Level,sys_connect_by_path(to_char(ID),',') as Path,
connect_by_IsLeaf as IsLeaf
  from IsLeafT
start with OyaID is null
connect by prior ID = OyaID;

出力結果
ID  OyaID  Level  Path       IsLeaf
--  -----  -----  ---------  ------
 1   null      1  ,1         0
 2      1      2  ,1,2       0
 3      2      3  ,1,2,3     0
 4      3      4  ,1,2,3,4   1
 7      2      3  ,1,2,7     1
 5      1      2  ,1,5       0
 6      5      3  ,1,5,6     1
20   null      1  ,20        0
21     20      2  ,20,21     0
22     21      3  ,20,21,22  1

データ作成スクリプト

create table IsLeafT(
ID    integer primary key,
OyaID integer);

insert into IsLeafT values
( 1,null),
( 2,   1),
( 3,   2),
( 4,   3),
( 5,   1),
( 6,   5),
( 7,   2),
(20,null),
(21,  20),
(22,  21);

SQL

--■■■exists述語を使う方法■■■
with recursive w(ID,OyaID,arrID) as(
select ID,OyaID,array[ID]
  from IsLeafT
 where OyaID is null
union all
select b.ID,b.OyaID,w.arrID || b.ID
  from w,IsLeafT b
 where w.ID = b.OyaID)
select ID,OyaID,array_upper(arrID,1) as Level,
',' || array_to_string(arrID,',') as path,
case when exists(select 1 from IsLeafT c
                  where w.ID = c.OyaID)
     then 0 else 1 end as isLeaf
  from w
order by arrID;

--■■■深さ優先探索の行きがけ順でレベルを比較する方法■■■
with recursive w(ID,OyaID,Level,arrID) as(
select ID,OyaID,1,array[ID]
  from IsLeafT
 where OyaID is null
union all
select b.ID,b.OyaID,w.Level+1,w.arrID || b.ID
  from w,IsLeafT b
 where w.ID = b.OyaID)
select ID,OyaID,Level,
',' || array_to_string(arrID,',') as path,
case when Level < Lead(Level) over(order by arrID)
     then 0 else 1 end as isLeaf
  from w
order by arrID;

解説

Lead関数を使う方法が楽そうですね。
配列型のソートの仕組み
9.17. 配列関数と演算子

OracleSQLパズル 10-149 Oracle9iでconnect_by_isleafを模倣

---