はじめに
Oracleの階層問い合わせについて、基本事項から使用例まで、SQLのイメージを交えて解説します。本稿では複雑な枝切りを扱います。
対象読者
- Oracleの階層問い合わせを使いたい方
- OracleのSQLの理解を深めたい方
必要な環境
本稿で扱うSQLは、Oracle 11.2.0.1.0で動作確認しました。
1. 紐づく子供がいたら、自分と子孫を、子供の値で埋める
最初に、組織構造を扱う階層問い合わせでの、複雑な枝切りを扱います。テーブルのデータと出力結果は下記となります。
| SZ_CD |
| 03 |
| 08 |
| 09 |
| 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テーブルを木構造で表示すると下記となります。
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 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 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述語を使って記述すると読みやすいでしょう。
connect by 親子条件
and not (枝切り条件)
SQLのイメージは下記となります。統括的な担当（staCDが09）の木を赤色の囲みで表現し、普通の担当（staCDが03）の木を青色の囲みで表現し、普通の担当（staCDが08）の木を紫色の囲みで表現してます。
枝切りせずにグループ化する方法
下記の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;
