SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

Oracleの階層問い合わせ

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

sys_connect_by_path関数の応用例

  • X ポスト
  • このエントリーをはてなブックマークに追加

ダウンロード SourceCode (1.3 KB)

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

  • X ポスト
  • このエントリーをはてなブックマークに追加

はじめに

 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のイメージ
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;

次のページ
2. ListAgg関数を模倣(内部結合あり)

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
Oracleの階層問い合わせ連載記事一覧

もっと読む

この記事の著者

山岸 賢治(ヤマギシ ケンジ)

趣味が競技プログラミングなWebエンジニアで、OracleSQLパズルの運営者。AtCoderの最高レーティングは1204(水色)。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/2690 2010/01/19 14:00

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング