SHOEISHA iD

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

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

Oracleの階層問い合わせ

Oracleの階層問い合わせ(7)
(複雑な枝切り)

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

ダウンロード SourceCode (1.6 KB)

 Oracleの階層問い合わせについて、基本事項から使用例まで、SQLのイメージを交えて解説します。本稿では複雑な枝切りを扱います。

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

はじめに

 Oracleの階層問い合わせについて、基本事項から使用例まで、SQLのイメージを交えて解説します。本稿では複雑な枝切りを扱います。

対象読者

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

必要な環境

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

1. 紐づく子供がいたら、自分と子孫を、子供の値で埋める

 最初に、組織構造を扱う階層問い合わせでの、複雑な枝切りを扱います。テーブルのデータと出力結果は下記となります。

Tanto
SZ_CD
03
08
09
Shozoku
SZ_CD OYA_SZ_CD
01 null
02 01
03 02
04 03
05 03
06 01
07 06
08 06
09 01
10 01

 Shozokuテーブルを木構造で表示すると下記となります。

Shozokuテーブルの木構造
01-----------------------------
|           |           |     |
02          06------   09    10
|           |      |
03------    07    08
|      |
04    05

 所属にそれぞれ担当がいて、その担当は所属全体(1レベル上以下すべて)を担当しています(SZ_CDが03,08)。また、統括的な担当(SZ_CDが09)もいて、担当がいない所属を担当しているものとして各担当を求めます。

 言いかえれば、上記の木構造において、Tantoテーブルに紐づくSZ_CDを持つ子供がいたら、そのSZ_CDを自分と子孫のTantoとして取得します。

出力結果
SZ_CD Tanto
01 09
02 03
03 03
04 03
05 03
06 08
07 08
08 08
09 09
10 09

 この問題のポイントは、普通の担当(SZ_CDが03,08)で自ノードと子孫ノードのTantoを階層問い合わせで取得した時と、統括的な担当(SZ_CDが09)で自ノードと子孫ノードのTantoを階層問い合わせで取得した時の重複ノードにおいて、どうやって普通の担当(SZ_CDが03,08)のみを出力するかだと思います。

 答えは、『Oracleの階層問い合わせ6(枝切りの入門事項)』で紹介した枝切りを使った下記となります。

枝切りする方法
select SZ_CD,connect_by_root staCD as Tanto_SZ_CD
from (select SZ_CD,OYA_SZ_CD,
      (select b.SZ_CD from Shozoku b,Tanto c
        where b.OYA_SZ_CD = a.SZ_CD
          and b.SZ_CD     = c.SZ_CD) as staCD
        from Shozoku a)
start with staCD is not null
connect by prior SZ_CD = OYA_SZ_CD --親子条件
       and not (Level >= 2
            and staCD is not null) --枝切り条件
order by SZ_CD;

 解説すると、まずインラインビューのselect句で相関サブクエリを使って、Tantoテーブルに紐づくSZ_CDを持つ子供がいたら、そのSZ_CDを列別名staCDとして求めてます。Tantoテーブルに紐づくSZ_CDを持つ子供がいない場合のstaCDはnullとなります。インラインビューのselect文とその結果は下記となります。

インラインビューのselect文
select SZ_CD,OYA_SZ_CD,
(select b.SZ_CD from Shozoku b,Tanto c
  where b.OYA_SZ_CD = a.SZ_CD
    and b.SZ_CD     = c.SZ_CD) as staCD
  from Shozoku a;
インラインビューの結果
SZ_CD OYA_SZ_CD staCD
01 null 09
02 01 03
03 02 null
04 03 null
05 03 null
06 01 08
07 06 null
08 06 null
09 01 null
10 01 null

 上記のインラインビューの結果をもとにして、階層問い合わせを行いつつ、select句でconnect_by_root演算子を使用したconnect_by_root staCD as Tanto_SZ_CDで各担当を求めてます。

 各担当(staCDが03,08,09)ごとに、階層問い合わせの探索による木ができるイメージですが、統括的な担当(staCDが09)の木の部分木が、普通の担当(staCDが03,08)の木と重ならないように、connect by句で以下の枝切り条件を含んだ条件式を指定してます。

connect by句での条件式
connect by prior SZ_CD = OYA_SZ_CD --親子条件
       and not (Level >= 2
            and staCD is not null) --枝切り条件

 上記の条件式には、NOT述語があるので、下記のドモルガンの法則を使って変形したくなるかもしれませんが、枝切り条件をNOT述語の中で肯定形で記述してあるので、上記の条件式を変形しないほうが読みやすいでしょう。

ドモルガンの法則
NOT(A*B) = (NOT(A)+NOT(B))

 以上をふまえて、枝切り条件を含むconncet by句は、下記のようにNOT述語を使って記述すると読みやすいでしょう。

枝切り条件を含むconncet by句の記述
connect by 親子条件
       and not (枝切り条件)

 SQLのイメージは下記となります。統括的な担当(staCDが09)の木を赤色の囲みで表現し、普通の担当(staCDが03)の木を青色の囲みで表現し、普通の担当(staCDが08)の木を紫色の囲みで表現してます。

SQLのイメージ
SQLのイメージ

枝切りせずにグループ化する方法

 下記のSQLのように、木のノードが別の木のノードと重なったなら、レベルが小さいほうを出力対象とすればよいと考えて、枝切りせずにグループ化する方法もあります。ただし、connect by句での枝切りによって早い段階で不要な行を出力しないようにできる場合は、connect by句での枝切りを使ったほうがSQLのパフォーマンスがよいと思います。

枝切りせずにグループ化する方法
select SZ_CD,
max(connect_by_root staCD)
Keep(Dense_Rank First order by Level) as Tanto_SZ_CD
from (select SZ_CD,OYA_SZ_CD,
      (select b.SZ_CD from Shozoku b,Tanto c
        where b.OYA_SZ_CD = a.SZ_CD
          and b.SZ_CD     = c.SZ_CD) as staCD
        from Shozoku a)
start with staCD is not null
connect by prior SZ_CD = OYA_SZ_CD --親子条件
group by SZ_CD
order by SZ_CD;

次のページ
2. 階層問い合わせで迷路問題を解く

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

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

もっと読む

この記事の著者

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

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

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

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

この記事をシェア

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

おすすめ

アクセスランキング

アクセスランキング

イベント

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

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

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

メールバックナンバー

アクセスランキング

アクセスランキング