![CodeZine [コードジン]](http://static.shoeisha.jp/cz/static/common/images/logo.gif){kind=logo}
Tweet
はじめに
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関数と同じ結果を取得してみます。サンプルを見てみましょう。
| ID | Val |
| 1 | a |
| 1 | c |
| 1 | e |
| 2 | b |
| 2 | d |
下記の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を使った下記が答えとなります。
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疑似列のイメージとして葉であるノードに緑色を塗ってます。
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;
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;
各種RSSを配信中
