3月 23, 2022

【oracle】階層問い合わせ

from https://qiita.com/gooddoog/items/2405d72d4cf7af218d25

【oracle】階層問い合わせを使って単方向リスト構造のレコードを逆にたどる

レコードの新旧履歴を、「前レコード」なカラムで
同じテーブルの主キーを格納することで表現するリレーションのテーブルが有り、
そんなテーブルで階層問い合わせを使う機会が合ったので覚書。

前提

以下のようなテーブルがあります

WITH t_place(place_id,place_name,pre_place) AS(
 SELECT '1'  , '武蔵' ,null FROM dual UNION ALL
 SELECT '2'  , '難波' ,null FROM dual UNION ALL 
 SELECT '18' , '江戸' ,'1'  FROM dual UNION ALL
 SELECT '19' , '大坂' ,'2'  FROM dual UNION ALL
 SELECT '20' , '大阪' ,'19' FROM dual UNION ALL
 SELECT '0'  , '大和' ,null FROM dual UNION ALL
 SELECT '21' , '日本' ,'0'  FROM dual UNION ALL
 SELECT '22' , '北朝' ,'21' FROM dual UNION ALL
 SELECT '23' , '南朝' ,'21' FROM dual UNION ALL
 SELECT '89' , '東京' ,'18' FROM dual 
)

カラムの意味は左から、
place_id:場所コード
place_name:場所名
pre_place:前時代の場所情報

とします。

リレーションは単方向リストLikeな構造になっており、
レコードのpre_placeから過去の場所情報のみ辿れるようになっています。

東京〜武蔵の例

NO.89 東京 前:18
　　　　　　　　↓
          NO:18 江戸 前:1
                       ↓
                    NO:1 武蔵 前:なし

一番古いレコードはpre_placeがnullかどうかで簡単に判別出来ます。
最新かどうかは自分のplace_idを他のレコードがpre_placeとして持っているか？というチェックをしないとわかりません。

このため、そのレコードが最新かどうか、ということは、レコード自身の持つ情報だけでは不明です。

そんな構造のテーブルですが現在の土地レコードの最新の土地名を全レコードを対象に調べる必要が発生しました。

最古〜最新の一覧を再帰クエリで表示

SELECT place_id,place_name,pre_place,
       LEVEL,                         --階層(木の深さ)
       CONNECT_BY_ISLEAF leaf         --末端かどうか
FROM
       t_place
START WITH
       pre_place is null              --木の根となる条件
CONNECT BY PRIOR place_id = pre_place --親子関係の条件(親->子)

結果

PLACE_ID	PLACE_NAME	PRE_PLACE	LEVEL	LEAF
0	大和		1	0
21	日本	0	2	0
22	北朝	21	3	1
23	南朝	21	3	1
1	武蔵		1	0
18	江戸	1	2	0
89	東京	18	3	1
2	難波		1	0
19	大坂	2	2	0
20	大阪	19	3	1

では用意されている機能について簡単に紹介したいと思います。

根の条件を指定する (START WITH)

FROM句の直後に続けて書きます。
START WITHで木の根となる条件を記述してください。
省略した場合、全てのカラムが根となる問い合わせ結果になります。

親子関係を指定する(CONNECT BY PRIOR )

CONNECT BY PRIOR 親○○ = 子×× で親子関係を記述します。
PRIOR(訳:前の)がついている方に親のカラムを書くと覚えてください。

木の深さを表す(LEVEL)

SELECT句やCONNECT BY句の中に書くことができます。
LEVELは木の深さを表します。根の場合LEVEL=1、
以後、後世の代に行くにつれて2,3,4と増えていきます。

CONNECT BY句の中に書くことで、問い合わせの深さを制限したりできます。

経路を表示する(SYS_CONNECT_BY_PATH)

SELECT句に含めることで根からの経路を表示できます。

SYS_CONNECT_BY_PATHサンプル

SELECT place_id,place_name,pre_place,
       LEVEL,                         --階層(木の深さ)
       CONNECT_BY_ISLEAF leaf,         --末端かどうか
       SYS_CONNECT_BY_PATH(TO_CHAR(place_name),',') AS path
FROM
       t_place
START WITH
       pre_place is null              --木の根となる条件
CONNECT BY PRIOR place_id = pre_place --親子関係の条件(親->子)

結果

PLACE_ID	PLACE_NAME	PRE_PLACE	LEVEL	LEAF	PATH
0	大和		1	0	,大和
21	日本	0	2	0	,大和,日本
22	北朝	21	3	1	,大和,日本,北朝
23	南朝	21	3	1	,大和,日本,南朝
1	武蔵		1	0	,武蔵
18	江戸	1	2	0	,武蔵,江戸
89	東京	18	3	1	,武蔵,江戸,東京
2	難波		1	0	,難波
19	大坂	2	2	0	,難波,大坂
20	大阪	19	3	1	,難波,大坂,大阪

根または葉を判定する(CONNECT_BY_ROOT、CONNECT_BY_LEAF)

SELECT句に書くことで木構造の葉や根となるレコードの判別に使えます。

CONNECT_BY_ROOT = 根である時:1 それ以外:0
CONNECT_BY_ISLEAF = 葉である時:1 それ以外:0

親の情報を扱う(PRIOR)

CONNECT BY 句で使うのが主な用途ですが
SELECT句で使った場合は親レコードのカラムを参照できます。

循環を防ぐ、循環の情報を表示する(CONNECT BY NOCYCLE,CONNECT_BY_ISCYCLE)

階層問い合わせではレコードが木やリストではなくグラフ構造になっている場合、
無限ループのエラーが発生してしまいます。
CONNECT BY句に NOCYCLEを追加することでこれを防げます。

また訪問済みノードへの再訪問が起こったとき(それを防いだとき)
SELECT句中に書いたCONNECT_BY_ISCYCLEの値が1になります。

問い合わせ結果を絞り込む(WHERE)

今更何をWHERE句の解説をしてるんだ、と思われたかもしれませんが、
階層問い合わせの場合、WHEREの実行順序は
START ～ CONNECTの一連の処理が終わった後になります。

つまり、階層問い合わせの結果出来上がった木構造やグラフの構造に対してWHEREで条件を指定することができます。

例)葉だけ表示したい

SELECT place_id,place_name,pre_place,
       LEVEL,                         --階層(木の深さ)
       CONNECT_BY_ISLEAF leaf,         --末端かどうか
       SYS_CONNECT_BY_PATH(TO_CHAR(place_name),',') AS path
FROM
       t_place
WHERE  CONNECT_BY_ISLEAF = 1 --注意)SELECT句で付けた別名は使えません。
START WITH
       pre_place is null              --木の根となる条件
CONNECT BY PRIOR place_id = pre_place --親子関係の条件(親->子)

結果

PLACE_ID	PLACE_NAME	PRE_PLACE	LEVEL	LEAF	PATH
22	北朝	21	3	1	,大和,日本,北朝
23	南朝	21	3	1	,大和,日本,南朝
89	東京	18	3	1	,武蔵,江戸,東京
20	大阪	19	3	1	,難波,大坂,大阪

SELECT DISTINCT a.vid, a.HOSTVID , a.ITEMNAME,

SYS_CONNECT_BY_PATH(TO_CHAR(a.ITEMNAME),'>') AS path

,LEVEL, --階層(木の深さ)

CONNECT_BY_ISLEAF leaf --末端かどうか

FROM

( SELECT a.vid, a.HOSTVID , a.ITEMNAME, a.SUBVID

FROM A.SSSTable a

WHERE a.SETNO =1) a

WHERE LEVEL <3

START WITH

a.SUBVID >=0 --木の根となる条件

CONNECT BY PRIOR a.vid=a.SUBVID --親子関係の条件(親->子)

ORDER BY a.HOSTVID,a.vid,LEVEL,leaf,path

搜尋此網誌

姓蔡的Blogger