再帰with句05 Oracleのconnect by nocycleを模倣

SQLパズル

noCycleT
ID  nextID
--  ------
 1       2
 2       3
 3       4
 4       1
 5       6

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

select ID,nextID,sys_connect_by_path(to_char(ID),',') as Path,
connect_by_iscycle as IsCycle
  from noCycleT
start with ID = 1
connect by nocycle prior nextID = ID;

出力結果
ID  nextID  Path      IsCycle
--  ------  --------  -------
 1       2  ,1              0
 2       3  ,1,2            0
 3       4  ,1,2,3          0
 4       1  ,1,2,3,4        1

データ作成スクリプト

create table noCycleT(
ID     integer primary key,
nextID integer);

insert into noCycleT values
(1,2),
(2,3),
(3,4),
(4,1),
(5,6);

SQL

--■■■unionで無限ループを防ぐ方法■■■
with recursive W(ID,nextID) as(
select ID,nextID
  from noCycleT
 where ID = 1
union
select b.ID,b.nextID
  from W,noCycleT b
 where W.nextID = b.ID)
select ID,nextID
  from W;

--■■■connect_by_iscycleは模倣しない場合■■■
with recursive W(ID,nextID,arrID) as(
select ID,nextID,array[ID]
  from noCycleT
 where ID = 1
union all
select b.ID,b.nextID,w.arrID || b.ID
  from W,noCycleT b
 where W.nextID = b.ID
   and b.ID != all(W.arrID))
select ID,nextID,
',' || array_to_string(arrID,',') as path
  from W;

--■■■connect_by_iscycleも模倣する場合(exists述語を使用しない)■■■
with recursive W(ID,nextID,arrID) as(
select ID,nextID,array[ID]
  from noCycleT
 where ID = 1
union all
select b.ID,b.nextID,w.arrID || b.ID
  from W,noCycleT b
 where W.nextID = b.ID
   and b.ID != all(W.arrID))
select ID,nextID,
',' || array_to_string(arrID,',') as path,
case when nextID = any(arrID)
     then 1 else 0 end as IsCycle
  from W;

--■■■connect_by_iscycleも模倣する場合(exists述語を使用)■■■
with recursive W(ID,nextID,arrID) as(
select ID,nextID,array[ID]
  from noCycleT
 where ID = 1
union all
select b.ID,b.nextID,w.arrID || b.ID
  from W,noCycleT b
 where W.nextID = b.ID
   and b.ID != all(W.arrID))
select ID,nextID,
',' || array_to_string(arrID,',') as path,
case when exists(select 1 from W c
                  where d.arrID[1] = c.arrID[1]
                    and d.nextID = c.ID
                    and c.ID = any(d.arrID))
     then 1 else 0 end as IsCycle
  from W d;

解説

同じ木で親子条件を満たして、かつ、訪問済なノードが存在すれば、
connect_by_iscycleが1と判断してます。

8.14. 配列
9.17. 配列関数と演算子

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

---