🌀
WITH句, WITH RECURSIVE句の使いどころ
目的
SQLのWITH句, WITH RECURSIVE句とは何か。及びその使いどころを理解する。
検証DB
今回検証に用いたDBは、MySQL8.0.29。
WITH句, WITH RECURSIVE句は、MySQLでは8.0から実装された。
WITH句
共通テーブル式 (CTE = Common Table Expression)。
同一SQL内で仮想テーブルを作成して、それをメインクエリ内で参照できる。
構文
WITH句 構文
WITH
仮想テーブル名 (カラムの別名) AS (仮想テーブルの生成クエリ)
-- 以下、メインクエリ。メインクエリ内で仮想テーブルを呼び出すことができる
SELECT ... FROM ... WHERE ... GROUP BY ...
;
ちなみに、Ruby on Rails(ActiveRecord)にはWith句に相当するメソッドは無い。
(なので、書こうとするとselectメソッドで直書きになる。)
使用例
【例】得点の合計が一番大きい人を取得。
WITH句 例
WITH
total_scores (name, total_score) AS ( -- SUM(score)をscoreとして別名で定義
SELECT name, SUM(score)
FROM scores
GROUP BY name
)
-- 以下、メインクエリ
SELECT t1.name, t1.total_score
FROM total_scores t1 -- 仮想テーブル呼び出し
WHERE t1.total_score = (
-- 合計点の最大を求めてスカラで返す
SELECT MAX(t2.total_score)
FROM total_scores t2 -- 仮想テーブル呼び出し
);
WITH RECURSIVE句
再帰的な共通テーブル式 (再帰CTE)。
自分自身を参照することで、前に行った処理の結果を利用して同じ処理を繰り返すことができる。(再帰処理)
WITH RECURSIVE句 構文
WITH RECURSIVE
仮想テーブル名 AS
-- 仮想テーブルの生成クエリ
(
非再帰項（仮想テーブルの先頭行になるレコード）
UNION ALL
再帰項（自身を参照・条件設定して抽出されたレコード）
)
-- 以下、メインクエリ。メインクエリ内で仮想テーブルを呼び出すことができる
SELECT ... FROM ... WHERE ... GROUP BY ...
使用例
【例1】織田家を(家系図的に)1代目から順に取得。
WITH RECURSIVE句 例1
WITH RECURSIVE
Oda AS (
-- 非再帰項
SELECT id, last_name, first_name, parent_id
FROM Family
WHERE last_name = "織田" AND parent_id IS NULL
UNION ALL
-- 再帰項
SELECT child.id, child.last_name, child.first_name, child.parent_id
FROM Oda -- 自身を参照
JOIN Family as child -- 元テーブルからデータを追加 <1>
ON Oda.id = child.parent_id
)
SELECT * FROM Oda;
【例2】各人の営業成績を取得。ただし、その人に部下がいる場合は部下の成績も自身の成績に含める。
WITH RECURSIVE句 例2
WITH RECURSIVE sales AS(
-- トップ (上司がいない。社長とか)
SELECT name, boss, sale
FROM sales_table
WHERE boss IS NULL
UNION ALL
-- 部下
SELECT st.name, st.boss, st.sale
FROM sales
JOIN sales_table st -- <1>
ON sales.name = st.boss
)
SELECT boss, SUM(sale)
FROM sales
GROUP BY boss
;
WITH RECURSIVE句を使わない場合
-
レコードを1件ずつ取得して、アプリ側でマージする。
→ 件数分のクエリの発行・ループ内処理が発生する。 -
外部結合（LEFT JOIN）を利用して、id と parent_id を紐づける。
→ 親子以下の階層(孫階層)は取得できない。
もし孫階層を取得しようと思うと下記のようなSQLになる。つまり、それより下の階層になるとJOINとそれをUNIONする回数が比例して増える。
孫階層を取得
-- 親
SELECT id, last_name, first_name, parent_id
FROM Family
WHERE last_name = '織田' AND parent_id IS NULL
UNION ALL
-- 子
SELECT f.id, f.last_name, f.first_name, f.parent_id
FROM Family f
JOIN (
SELECT *
FROM Family
WHERE last_name = '織田' AND parent_id IS NULL
) f1
ON f.parent_id = f1.id
UNION ALL
-- 孫
SELECT f.id, f.last_name, f.first_name, f.parent_id
FROM Family f
JOIN (
SELECT f.id, f.last_name, f.first_name, f.parent_id
FROM Family f
JOIN (
SELECT *
FROM Family
WHERE last_name = '織田' AND parent_id IS NULL
) f1
ON f.parent_id = f1.id
) f2
ON f.parent_id = f2.id
;
-
ルートノードからの経路を記録したカラム（pathカラム）を利用する。
→ テーブル定義の変更、および経路情報のINSERTが必要となる。
いずれも欠点が大きいため、階層構造（グラフ構造）データを格納するのにはWITH RECURSIVE句を使うのが良い。
WITH RECURSIVE句を使うと良いとき、使ってはいけないとき
使うと良いとき
-
ナイーブツリー構造のデータを扱う必要がある場合。
上記の通り、他の手段で頑張るよりもWITH RECURSIVE句を使うほうが良い。
使ってはいけないとき
-
大量データを扱う場合。
WITH句の仮想テーブルはメモリに乗るため。 -
無限ループが発生するSQLになっている場合。
例えば下表のように、レコード間の親子関係が循環していると無限ループが発生する。
(ちなみに、RDMS側で再帰レベル上限値は設定できる。
MySQLでの設定値はcte_max_recursion_depth。デフォルト値は1,000。)
| id | name | parent_id |
|---|---|---|
| 1 | parent | 2 |
| 2 | child | 1 |
参考情報
以上
Discussion