LoginSignup
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@hidesroom

可視化SQL | 再帰処理、階層構造SQLの動作を図で確認する

Posted at

初めに

再帰・・と呼んでるので
同じ処理を繰り返し実行しているのでしょうが
他の言語と同じように、処理を追う事が容易ではありません

SQLでの再帰処理が実行される様子？？を
図を使って解説してみます

CTEの使い方を理解されている前提で
すすめさせていただきます

再帰処理については、多数記事が公開されているので
こちらと合わせて確認いただければとおもいます
　⇒　下にいくつかリンクを張っています

まずは、シンプルな例をみてみます

【再帰処理】0 から 25 まで『5』づつ加算

SQL
WITH RECURSIVE cte_table AS (

    -- 初回だけ実行される
	SELECT 0 AS num

	UNION ALL

    -- ２回目以降～実行される
	SELECT num + 5 AS num
	FROM cte_table
	WHERE num < 25

)

SELECT * FROM cte_table;

▼出力結果

num
0
5
10
15
20
25

再帰処理の挙動

ここでは、ざっくりとした再帰処理の流れを
可視化してみます

再帰処理は２つにわかれています
下記２つのSQLの結果を UNION ALL で結合してます

　■ 初回だけ実行される処理
　　SELECT 0 AS num
　　　⇒ 初期データを入れてる・・感じです
　　　⇒ cte_table の num カラムに 0 を入れる

　■ ２回目以降～に実行される処理
　　SELECT num + 5 AS num FROM cte_table WHERE num < 25
　　　⇒ CTE の名称と同じcte_table をFROM句で使用
　　　⇒ 自分自身を呼び出してる・・イメージ
　　　⇒ numに5 を加算する処理を
　　　　 numが25より小さい時は処理を続ける
　　　⇒ 再帰処理の終了条件を WHERE句に記述

▼　2つ目の処理が再帰処理される
rec_2.png

▼　前回処理した結果を持つデータを次に使用する
rec_3.png

▼　再帰処理が終わる時
rec_4.png

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
少し複雑な処理を見てみます

【再帰処理】ツリー構造 | 準備

SQL
-- テーブル定義
CREATE TABLE tbl_employee (
    emp_id INTEGER       -- 従業員ID
    ,employee_name TEXT  -- 従業員名
    ,manager_id INTEGER  -- 従業員の上司ID
);

-- データ登録
INSERT INTO tbl_employee VALUES (1, 'King', NULL);
INSERT INTO tbl_employee VALUES (33, 'Jeff', 1);
INSERT INTO tbl_employee VALUES (55, 'Nancy', 1);
INSERT INTO tbl_employee VALUES (88, 'Bob', 1);
INSERT INTO tbl_employee VALUES (222, 'Dan', 33);
INSERT INTO tbl_employee VALUES (666, 'Kate', 33);
INSERT INTO tbl_employee VALUES (4444, 'Bill', 666);

SELECT * FROM tbl_employee;

▼出力結果

emp_id employee_name manager_id
1 King NULL
33 Jeff 1
55 Nancy 1
88 Bob 1
222 Dan 33
666 Kate 33
4444 Bill 666

▼テーブル構造イメージ
rec_5.png

親(King)の配下に所属するスタッフを取出す

SQL
WITH RECURSIVE cte_table AS (
    -- King の情報
	SELECT emp_id, emp_name, manager_id 
	FROM tbl_employee WHERE emp_id = 1
	
	UNION ALL

    -- King 以下に配置されてるスタッフを取出す
	SELECT emp.emp_id,emp.emp_name, emp.manager_id 
	FROM cte_table AS cte
	INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id
	
)

SELECT * FROM cte_table

▼出力結果

employee_id employee_name manager_id
1 King NULL
33 Jeff 1
55 Nancy 1
55 Bob 1
222 Dan 33
666 Kate 33
4444 Bill 666

tbl_employee と同じ？？　確かにそうなりますね
Kingは一番TOPの人(root)なので、
その人物の配下のスタッフは、全員が対象です

出力結果とtbl_employeeを比較
rec_9.png

tbl_employee をSELECTしているのと何が違うの？？
FROM句でcte_tableを使ってるけど再帰処理してるの　(๑• •๑)??

こんな風に考えてしまいます・・が、再帰処理してます

▼　こんな感じですが、動作がまだ見えません(￣Д￣;)
rec_10.png

もう少し、細かくみてみます・・・

親(Jeff)の配下に所属するスタッフを取出す

SQL
WITH RECURSIVE cte_table AS (
    -- Jeff の情報
	SELECT emp_id, emp_name, manager_id 
	FROM tbl_employee WHERE emp_id = 33 -- 修正
	
	UNION ALL

    -- Jeff 以下に配置されてるスタッフを取出す
	SELECT emp.emp_id,emp.emp_name, emp.manager_id 
	FROM cte_table AS cte
	INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id
	
)
SELECT * FROM cte_table

▼出力結果

employee_id employee_name manager_id
33 Jeff 1
222 Dan 33
666 Kate 33
4444 Bill 666

▼　Jeffの配下に所属するスタッフの取出しイメージ

rec_13.png

Step1：Jeff を取出す

SQL
WITH RECURSIVE cte_table AS (
    -- Jeff の情報
	SELECT emp_id, emp_name, manager_id 
	FROM tbl_employee WHERE emp_id = 33 -- 修正
	
 -- UNION ALL

    -- Jeff 以下に配置されてるスタッフを取出す
 -- SELECT emp.emp_id,emp.emp_name, emp.manager_id 
 -- FROM cte_table AS cte
 -- INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id
	
)
SELECT * FROM cte_table

▼ 出力結果 | Jeffが取出せた

employee_id employee_name manager_id
33 Jeff 1

Step2：Jeffの部下を取出す

SQL
WITH RECURSIVE cte_table AS (
    -- Jeff の情報
 -- SELECT emp_id, emp_name, manager_id 
 -- FROM tbl_employee WHERE emp_id = 33 -- 修正
	
 -- UNION ALL

    -- Jeff 以下に配置されてるスタッフを取出す
    SELECT emp.emp_id,emp.emp_name, emp.manager_id 
    FROM cte_table AS cte
    INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id
	
)
SELECT * FROM cte_table

▼ 出力結果 エラーになる
rec_14.png

再帰処理の部分だけを実行したかった・・が
記述ルールに反している・・のでエラーになる
この部分を、再帰処理を使わずに書換えてみる
　⇒　Step2：Jeffの部下を取出すを書換え

Step2：Jeffの部下、Dan,Kate を取出す(非再帰)

SQL
-- Dan,Kate の情報
SELECT emp.emp_id,emp.emp_name, emp.manager_id 
FROM (
    -- Jeff の情報
	SELECT emp_id, emp_name, manager_id 
	FROM tbl_employee WHERE emp_id = 33
) AS cte
INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id

▼ 出力結果　Dan, Kate 取出せた

employee_id employee_name manager_id
222 Dan 33
666 Kate 33

動作の詳細
　⇒ Jeffを上司に持つスタッフ(Dan,Kate)を取出すには
　　 最初に取得したJeffの情報に対して
　　 tbl_employee を join してます
　⇒ cte_table には前回取得したJeffの情報が保持されてる
　⇒ 上司であるJeffのID(cte.emp_id:33) と
　　 同じmanager_idを持つ行をtbl_employeeから取出

▼ Jeffの部下、Dane, Kate を取出すイメージ
rec_18.png

Step3：Kateの部下、Bill を取出す(非再帰)

SQL
-- Bill の情報
SELECT emp.emp_id,emp.emp_name, emp.manager_id 
FROM(
    -- Dan,Kate の情報
	SELECT emp.emp_id,emp.emp_name, emp.manager_id 
	FROM (
        -- Jeff の情報
		SELECT emp_id, emp_name, manager_id 
		FROM tbl_employee WHERE emp_id = 33
	) AS cte
	INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id
) AS cte
INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id

▼ 出力結果 Bill が 取出せた

employee_id employee_name manager_id
4444 Bill 666

動作の詳細
　⇒ 前回取得した情報(Dan,Kate)に対して
　　 tbl_employee を join してます
　⇒ cte_table には前回取得した情報が保持されてる
　⇒ 上司であるDan,KateのID(cte.emp_id:222,666) と
　　 同じmanager_idを持つ行をtbl_employeeから取出

▼ Kateの部下、Billを取出すイメージ
rec_21.png

Step4：Bill の部下がいない。再帰処理が終わる

SQL
-- Bill の部下
SELECT emp.emp_id,emp.emp_name, emp.manager_id 
FROM (
	-- Bill の情報
	SELECT emp.emp_id,emp.emp_name, emp.manager_id 
	FROM(
		-- Dan,Kate の情報
		SELECT emp.emp_id,emp.emp_name, emp.manager_id 
		FROM (
			-- Jeff の情報
			SELECT emp_id, emp_name, manager_id 
			FROM tbl_employee WHERE emp_id = 33
		) AS cte
		INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id
	) AS cte
	INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id
) AS cte
INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id

▼ 出力結果 データ無い

employee_id employee_name manager_id
　 　 　

動作の詳細
　⇒ 前回取得した情報(Bill)に対して
　　 tbl_employee を join してます
　⇒ cte_table には前回取得した情報が保持されてる
　⇒ 上司であるBillのID(cte.emp_id:4444) と
　　 同じmanager_idを持つ行をtbl_employeeから取出

▼ Billの部下を取出そうとするが、存在しない
rec_23.png

戻り値が無いので、再帰処理が終了

Bill を上司に持つスタッフが存在しない
　⇒ SQL の結果が返ってこない
　⇒ ここで、再帰処理が終わる
　⇒ Step4 が最後の処理

最初の例では、再帰処理が終わる条件を
WHERE句に記述していました
　⇒　WHERE num < 25
この再帰クエリでは、『結果が返ってこない』
を条件にして再帰処理が終ります

『処理の流れ』と『全体のイメージ図』

Step1 : Jeff の情報を取得
　UNION ALL
Step2 : Dan, Kate の情報を取得
　UNION ALL
Step3 : Bill の情報を取得

Step4 : 情報がないので終了

【再帰処理クエリ】↓↓↓
SELECT emp.emp_id,emp.emp_name, emp.manager_id
FROM cte_table AS cte
INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id

再帰処理されるクエリは
Step2, Step3, Step4 で実行されている
Step4 で結果が返ってこないので
全体の処理がここで終了する

以上となります
再帰クエリがなんとなく理解いただければ幸いです

▼ 全体のイメージ図
rec_25.png

参考サイト

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?