再帰with句03 Oracleのorder siblings byを模倣

SQLパズル

siblingsT
ID  OyaID
--  -----
 1   null
 2      1
 3      1
 4      1
 5      3
 6      3
 7      4
 8      4
 9      6
10      7
20   null
21     20
22     20
23     21
24     21

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

select connect_by_root ID as treeID,
ID,OyaID,Level,
sys_connect_by_path(to_char(ID),',') as path
  from siblingsT
start with OyaID is null
connect by prior ID = OyaID
order siblings by ID desc;

出力結果
treeID  ID  OyaID  Level  path
------  --  -----  -----  ---------
    20  20   null      1  ,20
    20  22     20      2  ,20,22
    20  21     20      2  ,20,21
    20  24     21      3  ,20,21,24
    20  23     21      3  ,20,21,23
     1   1   null      1  ,1
     1   4      1      2  ,1,4
     1   8      4      3  ,1,4,8
     1   7      4      3  ,1,4,7
     1  10      7      4  ,1,4,7,10
     1   3      1      2  ,1,3
     1   6      3      3  ,1,3,6
     1   9      6      4  ,1,3,6,9
     1   5      3      3  ,1,3,5
     1   2      1      2  ,1,2

データ作成スクリプト

create table siblingsT(
ID    integer not null primary key,
OyaID integer);

insert into siblingsT values
( 1,null),
( 2,   1),
( 3,   1),
( 4,   1),
( 5,   3),
( 6,   3),
( 7,   4),
( 8,   4),
( 9,   6),
(10,   7),
(20,null),
(21,  20),
(22,  20),
(23,  21),
(24,  21);

SQL

with recursive RowN(ID,OyaID,sk) as(
select ID,OyaID,Row_Number() over(order by ID desc)
  from siblingsT),
w(ID,OyaID,arrID,sk) as(
select ID,OyaID,array[ID],array[sk]
  from RowN
 where OyaID is null
union all
select b.ID,b.OyaID,w.arrID || b.ID,
w.sk || b.sk
  from w,RowN b
 where w.ID = b.OyaID)
select arrID[1] as treeID,ID,OyaID,
array_upper(arrID,1) as Level,
',' || array_to_string(arrID,',') as path,sk
  from w
order by sk;

解説

根からの経路でソートしてます。

配列型のソートの仕組み
9.17. 配列関数と演算子

PGCon2009: Introduction to Recursive QueriesのPDFの19ページに、
Simulate Oracle-style ORDER SIBLINGS BY using an array of ROW()s containing the sort key.
と書かれてますね。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
ちなみに、複数キーを持ったテーブルの場合は、
dense_rank関数で一時的なサロゲートキーを作成して配列型にセットしていくといいでしょう。
SQLアタマアカデミー：サロゲートキーVSナチュラルキー

---