Oracleの階層問い合わせ（5）
（ListAgg関数を模倣）

sys_connect_by_path関数の応用例

　Oracleの階層問い合わせについて、基本事項から使用例までSQLのイメージを交えて解説します。本稿では、ListAgg関数を模倣する方法と、all_dependenciesデータディクショナリビューに対する階層問い合わせを扱います。

はじめに

　Oracleの階層問い合わせについて、基本事項から使用例まで、SQLのイメージを交えて解説します。本稿ではsys_connect_by_path関数の応用例として、Oracle 11g R2の新機能のListAgg関数を模倣する方法と、all_dependenciesデータディクショナリビューに対する階層問い合わせを扱います。

対象読者

• Oracleの階層問い合わせを使いたい方
• OracleのSQLの理解を深めたい方

必要な環境

　本稿で扱うSQLは、Oracle 11.1.0.6.0で動作確認しました。

1. ListAgg関数を模倣

　MySQLのGroup_Concat関数のように文字列を連結する関数として、Oracleでは、wmsys.wm_concat関数というマニュアルにのってない隠し関数がありますが、同等の機能を持つListAgg関数がOracle 11g R2で正式に追加されました。

　階層問い合わせでsys_connect_by_path関数を使って、wmsys.wm_concat関数と同じ結果を取得してみます。サンプルを見てみましょう。

strAggT

ID	Val
1	a
1	c
1	e
2	b
2	d

　下記のwmsys.wm_concat関数を使ったSQLと同じ結果を取得します。

wmsys.wm_concat関数を使ったSQL

select ID,wmsys.wm_concat(Val) as Path
  from strAggT
group by ID;

出力結果

ID	Path
111	a,b,c
222	d,e,f

　階層問い合わせを使えば、自ノードまでの経路をsys_connect_by_path関数を使って取得することができます。分析関数のRow_Number関数を使って、行に連番を付与して、階層問い合わせを行ってみた結果が下記となります。

葉以外も出力

select ID,Val,sys_connect_by_path(Val,',') as Path
from (select ID,Val,
      Row_Number() over(partition by ID order by Val) as Rn
        from strAggT)
start with Rn=1
connect by prior ID = ID
       and prior Rn = Rn-1;

出力結果

ID	Val	Rn	Path
111	a	1	,a
111	b	2	,a,b
111	c	3	,a,b,c
222	d	1	,d
222	e	2	,d,e
222	f	3	,d,e,f

　上記の結果から、階層問い合わせの結果の葉だけを出力すればいいと分かりますので、where句でconnect_by_IsLeafを使った下記が答えとなります。

connect_by_IsLeafで葉か判断する方法

select ID,substr(sys_connect_by_path(Val,','),2) as Path
from (select ID,Val,
      Row_Number() over(partition by ID order by Val) as Rn
        from strAggT)
where connect_by_IsLeaf = 1
start with Rn=1
connect by prior ID = ID
       and prior Rn = Rn-1;

　SQLのイメージは、下記となります。connect by句にprior ID = IDがありますのでIDごとに区切る赤線をイメージして、connect_by_IsLeaf疑似列のイメージとして葉であるノードに緑色を塗ってます。

SQLのイメージ

　connect_by_IsLeafは、Oracle 10g R1以降でないと使えませんので、Oracle 9iでも使えるSQLを紹介しておきます。

葉のレベルを事前に求めておく方法

select ID,substr(sys_connect_by_path(Val,','),2) as Path
from (select ID,Val,
      Row_Number() over(partition by ID order by Val) as Rn,
      count(*) over(partition by ID) as cnt
        from strAggT)
where Level = cnt
start with Rn=1
connect by prior ID = ID
       and prior Rn = Rn-1;

グループ化してmax関数を使う方法

select ID,max(substr(sys_connect_by_path(Val,','),2)) as Path
from (select ID,Val,
      Row_Number() over(partition by ID order by Val) as Rn
        from strAggT)
start with Rn=1
connect by prior ID = ID
       and prior Rn = Rn-1
group by ID;