概要
CTEは、Common Table Expressionsの略であり、WITH句を使用して定義される名前付き一時結果セットである。
MySQL 8.0で導入された機能であり、MySQL 5.7以前のバージョンでは使用できない。
CTEは、複雑なクエリをより可読性の高い形で記述するための機能であり、サブクエリや派生テーブルの代替として使用できる。
CTEは、SELECT文、UPDATE文、DELETE文およびサブクエリ内で使用可能である。
CTEには、非再帰CTEと再帰CTEの2タイプが存在する。
非再帰CTEは、通常のサブクエリと同様に、単純な一時結果セットを定義するために使用される。
再帰CTEは、階層データの探索、グラフ走査、数列生成等、再帰的な処理を行うために使用される。
CTEは、同一クエリ内で複数回参照できるため、派生テーブルよりも効率的で可読性が高い。
複数回参照される場合、CTE結果はメモリ内一時テーブルにマテリアライズ (実体化) される。
CTEは、ウィンドウ関数との組み合わせ、重複データの検出、階層データの探索等、実用的な場面で広く使用される。
基本構文
WITH句の構文
WITH句の基本構文を以下に示す。
WITH [RECURSIVE]
cte_name [(col_name [, col_name] ...)] AS (subquery)
SELECT ... FROM cte_name;
下表に、構文要素を示す。
| キーワード | 説明 |
|---|---|
RECURSIVE
|
|
cte_name
|
|
col_name
|
|
subquery
|
|
カラムリストは、明示的に指定することもできるし、サブクエリのSELECTリストから自動推論させることもできる。
以下の例では、cte1 という名前のCTEを定義し、users テーブルから status = 'active' の行を取得している。
WITH cte1 AS (
SELECT id, name FROM users WHERE status = 'active'
)
SELECT * FROM cte1;
複数CTEの定義
同一WITH句内で、複数のCTEをカンマ区切りで定義できる。
WITH
cte1 AS (SELECT ... ),
cte2 AS (SELECT ... ),
cte3 AS (SELECT ... )
SELECT ... FROM cte1, cte2, cte3;
CTE間の参照について以下に示す。
- 先行するCTEのみ参照可能
- cte2 は cte1 を参照できるが、cte3 を参照できない。
- 後方参照は不可
- cte1 は、cte2 または cte3 を参照できない。
- 相互再帰は不可
- cte1 と cte2 が互いに参照し合うことはできない。
複数CTEのサンプルクエリを以下に示す。
以下の例では、active_users CTEを定義し、その結果を user_orders CTEで参照している。
WITH
active_users AS (
SELECT id, name FROM users WHERE status = 'active'
),
user_orders AS (
SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users)
)
SELECT * FROM user_orders;
同じレベルで複数WITH句を並べることはできない。
-- エラー : 複数WITH句を並べることは不可
WITH cte1 AS (SELECT ...)
WITH cte2 AS (SELECT ...)
SELECT ...;
非再帰CTE
基本的な使用方法
非再帰CTEは、通常のサブクエリと同様に、単純な一時結果セットを定義するために使用される。
非再帰CTEの基本的な使用例を以下に示す。
以下の例では、sales_summary CTEを定義し、売上データの集計結果を保持している。
メインクエリでは、CTEの結果を products テーブルと結合して、商品名を含む集計結果を取得している。
WITH sales_summary AS (
SELECT
product_id,
SUM(quantity) AS total_quantity,
SUM(amount) AS total_amount
FROM sales
WHERE sale_date >= '2024-01-01'
GROUP BY product_id
)
SELECT
p.product_name,
s.total_quantity,
s.total_amount
FROM sales_summary s
JOIN products p ON s.product_id = p.id;
非再帰CTEは、SELECT文、UPDATE文、DELETE文の開始位置で使用可能である。
また、サブクエリ内でも使用可能である。
CTEは、同一クエリ内で複数回参照できる。
これは、派生テーブルとの大きな違いである。
複数回参照のサンプルクエリを以下に示す。
以下の例では、active_users CTEを2回参照している。
派生テーブルを使用する場合は、同じサブクエリを2回記述する必要があるが、CTEでは1回の定義で済む。
WITH active_users AS (
SELECT id, name, email FROM users WHERE status = 'active'
)
SELECT
(SELECT COUNT(*) FROM active_users) AS total_users,
(SELECT COUNT(DISTINCT email) FROM active_users) AS unique_emails;
JOINとの組み合わせ
CTEは、JOINと組み合わせて使用することができる。
WITH
recent_orders AS (
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY customer_id
),
high_value_customers AS (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 10000
)
SELECT
c.customer_name,
r.order_count,
h.total_amount
FROM customers c
LEFT JOIN recent_orders r ON c.id = r.customer_id
LEFT JOIN high_value_customers h ON c.id = h.customer_id;
上記の例では、2つのCTEを定義し、それぞれを customers テーブルと結合している。
recent_orders CTEは、過去30日間の注文数を集計している。
high_value_customers CTEは、合計購入金額が10000を超える顧客を抽出している。
DML文での使用
CTEは、INSERT文、UPDATE文、DELETE文でも使用できる。
CTEを使用することにより、DML文の可読性が向上する。
- INSERT文での使用例
WITH new_users AS ( SELECT id, name, email FROM staging_users WHERE status = 'approved' ) INSERT INTO users (id, name, email) SELECT id, name, email FROM new_users;
- UPDATE文での使用例
WITH inactive_users AS ( SELECT id FROM users WHERE last_login < DATE_SUB(NOW(), INTERVAL 90 DAY) ) UPDATE users SET status = 'inactive' WHERE id IN (SELECT id FROM inactive_users);
- DELETE文での使用例
WITH old_logs AS ( SELECT id FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR) ) DELETE FROM logs WHERE id IN (SELECT id FROM old_logs);
再帰CTE
再帰CTEの構造
再帰CTEは、自分自身を参照するCTEである。
再帰CTEは、階層データの探索、グラフ走査、数列生成等、再帰的な処理を行うために使用される。
再帰CTEの構造を以下に示す。
WITH RECURSIVE cte_name AS (
-- アンカーメンバ (非再帰部分)
SELECT ...
UNION ALL
-- 再帰メンバ (再帰部分)
SELECT ... FROM cte_name WHERE ...
)
SELECT * FROM cte_name;
再帰CTEは、アンカーメンバ (非再帰部分) / 再帰メンバ (再帰部分) の2つの部分から構成される。
| 要素 | 説明 | 役割 |
|---|---|---|
| アンカーメンバ (非再帰部分) | CTEの名前を参照しないSELECT文 | 初期行セットを返す 再帰処理の開始点 |
| 再帰メンバ (再帰部分) | CTEの名前をFROM句で参照するSELECT文 | 新しい行を生成 新しい行が生成されない場合に終了 |
アンカーメンバと再帰メンバは、UNION ALL または UNION DISTINCT で結合される。
通常は、UNION ALL を使用する。
再帰処理の流れを以下に示す。
- アンカーメンバを実行して、初期行セットを生成する。
- 初期行セットを再帰メンバに渡す。
- 再帰メンバが新しい行を生成する。
- 新しい行を再帰メンバに渡す。(ステップ3に戻る)
- 再帰メンバが新しい行を生成しなくなった場合、再帰処理を終了する。
再帰CTEを定義する場合は、必ず、WITH RECURSIVE キーワードを使用する。
数列の生成
再帰CTEを使用して、数列を生成することができる。
1からNまでの連番を生成するサンプルクエリを以下に示す。
WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;
上記のクエリの実行結果を以下に示す。
+----+ | n | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +----+
フィボナッチ数列を生成する例を以下に示す。
WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS (
SELECT 1, 0, 1
UNION ALL
SELECT n + 1, next_fib_n, fib_n + next_fib_n
FROM fibonacci
WHERE n < 10
)
SELECT n, fib_n FROM fibonacci;
上記のクエリの実行結果を以下に示す。
+----+-------+ | n | fib_n | +----+-------+ | 1 | 0 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 3 | | 6 | 5 | | 7 | 8 | | 8 | 13 | | 9 | 21 | | 10 | 34 | +----+-------+
日付系列を生成するサンプルクエリを以下に示す。
以下の例では、2024年1月1日から2024年1月31日までの日付系列を生成する。
WITH RECURSIVE date_series AS (
SELECT DATE('2024-01-01') AS dt
UNION ALL
SELECT DATE_ADD(dt, INTERVAL 1 DAY)
FROM date_series
WHERE dt < '2024-01-31'
)
SELECT * FROM date_series;
階層データの探索
再帰CTEは、階層データの探索に適している。
組織図の階層構造を持つテーブル例を以下に示す。
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
manager_id INT
);
INSERT INTO employees VALUES
(1, 'CEO', NULL),
(2, 'VP Engineering', 1),
(3, 'VP Sales', 1),
(4, 'Engineering Manager', 2),
(5, 'Sales Manager', 3),
(6, 'Engineer', 4),
(7, 'Sales Rep', 5);
CEOから始まる組織階層を探索するサンプルクエリを以下に示す。
WITH RECURSIVE org_chart AS (
-- アンカーメンバ: CEOを取得
SELECT id, name, manager_id, 1 AS level, CAST(name AS CHAR(200)) AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 再帰メンバ: 部下を取得
SELECT e.id, e.name, e.manager_id, o.level + 1, CONCAT(o.path, ' > ', e.name)
FROM employees e
JOIN org_chart o ON e.manager_id = o.id
)
SELECT level, name, path FROM org_chart ORDER BY level, id;
上記のクエリの実行結果を以下に示す。
+-------+---------------------+-------------------------------------------------------+ | level | name | path | +-------+---------------------+-------------------------------------------------------+ | 1 | CEO | CEO | | 2 | VP Engineering | CEO > VP Engineering | | 2 | VP Sales | CEO > VP Sales | | 3 | Engineering Manager | CEO > VP Engineering > Engineering Manager | | 3 | Sales Manager | CEO > VP Sales > Sales Manager | | 4 | Engineer | CEO > VP Engineering > Engineering Manager > Engineer | | 4 | Sales Rep | CEO > VP Sales > Sales Manager > Sales Rep | +-------+---------------------+-------------------------------------------------------+
上記の例では、level カラムで階層レベルを表現し、path カラムで階層パスを構築している。
CONCAT() 関数を使用して、パスを連結している。
カテゴリツリーの探索サンプルクエリを以下に示す。
WITH RECURSIVE category_tree AS (
-- アンカーメンバ: ルートカテゴリを取得
SELECT id, name, parent_id, 1 AS depth
FROM categories
WHERE parent_id IS NULL
UNION ALL
-- 再帰メンバ: 子カテゴリを取得
SELECT c.id, c.name, c.parent_id, ct.depth + 1
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT
CONCAT(REPEAT(' ', depth - 1), name) AS category_hierarchy
FROM category_tree
ORDER BY id;
上記のクエリは、カテゴリツリーをインデント付きで表示する。
REPEAT() 関数を使用して、階層レベルに応じたインデントを生成している。
再帰深度の制御
再帰CTEは、無限ループを防ぐために、再帰深度を制御する必要がある。
| 方法 | 説明 | デフォルト値 |
|---|---|---|
cte_max_recursion_depth システム変数 |
セッションまたはグローバルレベルで再帰深度の上限を設定 | 1000 |
max_execution_time システム変数 |
クエリ実行時間の上限を設定 (ミリ秒) | 0 (無制限) |
SET_VAR() オプティマイザーヒント |
クエリごとに再帰深度の上限を設定 | - |
LIMIT 句 |
再帰SELECT部で使用して、生成される行数を制限 | - |
cte_max_recursion_depth システム変数の設定例を以下に示す。
-- セッションレベルで設定
SET SESSION cte_max_recursion_depth = 5000;
-- グローバルレベルで設定
SET GLOBAL cte_max_recursion_depth = 10000;
デフォルト値は1000であり、再帰処理が1000回を超えるとエラーが発生する。
SET_VAR() オプティマイザーヒントの使用例を以下に示す。
WITH RECURSIVE cte AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM cte WHERE n < 1000000
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1000000) */ COUNT(*) FROM cte;
上記の例では、クエリごとに再帰深度の上限を1000000に設定している。
LIMIT 句の使用例を以下に示す。
WITH RECURSIVE cte AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM cte LIMIT 100
)
SELECT * FROM cte;
LIMIT 句は、MySQL 8.0.19以降で再帰SELECT部で使用可能である。
MySQL 8.0.18以前では、再帰SELECT部で LIMIT 句を使用できない。
CTEとサブクエリの比較
CTEとサブクエリ (派生テーブル) の比較を以下に示す。
| 項目 | CTE | サブクエリ (派生テーブル) | 一時テーブル |
|---|---|---|---|
| 複数回参照 | 可能 (効率的、マテリアライズ) | 不可 (定義を繰り返す必要) | 可能 |
| 再帰処理 | WITH RECURSIVE対応 | 不可 | 不可 |
| 可読性 | 優秀 (名前付き、先頭で定義) | ネストが深くなりがち | 優秀 (名前付き) |
| 自己参照 | 再帰CTEで可能 | 不可 | 不可 |
| 有効期間 | 単一ステートメント | 単一ステートメント | セッションまたは明示削除まで |
| スコープ | ステートメント内に限定 | サブクエリ内に限定 | セッション全体 |
| インデックス作成 | 不可 | 不可 | 可能 |
| テーブル作成権限 | 不要 | 不要 | 必要 |
| 定義方法 | WITH句 | FROM (SELECT ...) AS alias | CREATE TEMPORARY TABLE |
下表に、CTEの主なメリットを示す。
| メリット | 説明 |
|---|---|
| 複数回参照が可能 | 派生テーブルでは、同じサブクエリを複数回記述する必要があるが、CTEでは1回の定義で済む |
| 再帰処理が可能 | 階層データの探索、グラフ走査等、再帰的な処理を行うことができる |
| 可読性が高い | CTEは名前付きであり、クエリの先頭で定義されるため、クエリ全体の構造が理解しやすい |
| 自己参照が可能 | 再帰CTEでは、CTE自身を参照することができる |
下表に、CTEの主なデメリットを示す。
| デメリット | 説明 |
|---|---|
| 有効期間が短い | CTEは単一ステートメント内でのみ有効であり、複数ステートメントにまたがって使用できない |
| インデックスを作成できない | CTEには、インデックスを作成できない (一時テーブルでは可能) |
CTEと派生テーブルの比較例を以下に示す。
CTEを使用することにより、可読性が向上し、定義の重複を避けることができる。
- 派生テーブルを使用した例 (複数回参照のために定義を繰り返す)
SELECT (SELECT COUNT(*) FROM (SELECT id FROM users WHERE status = 'active') t1) AS total, (SELECT COUNT(DISTINCT email) FROM (SELECT id, email FROM users WHERE status = 'active') t2) AS unique_emails;
- CTEを使用した例 (1回の定義で複数回参照)
WITH active_users AS ( SELECT id, email FROM users WHERE status = 'active' ) SELECT (SELECT COUNT(*) FROM active_users) AS total, (SELECT COUNT(DISTINCT email) FROM active_users) AS unique_emails;
CTEの制限事項
CTE定義には、以下に示すような制限事項がある。
- 同じWITH句内で同じ名前のCTEを複数定義することはできない
- 前に定義されたCTEのみ参照可能 (後方参照は不可)
- 同一ステートメント内で複数WITH句を並べることはできない
- CTEは単一ステートメント内でのみ有効
再帰CTEの制限を以下に示す。
| 制限項目 | 説明 | 対応バージョン |
|---|---|---|
| 集約関数 | SUM, COUNT, AVG等の集約関数は使用不可 | 全バージョン |
| ウィンドウ関数 | ROW_NUMBER, RANK等のウィンドウ関数は使用不可 | 全バージョン |
| GROUP BY | GROUP BY句は使用不可 | 全バージョン |
| ORDER BY | ORDER BY句は使用不可 | 全バージョン |
| DISTINCT | DISTINCT句は使用不可 (UNION DISTINCTは可) | 全バージョン |
| LIMIT / OFFSET | 8.0.18まで禁止、8.0.19以降許可 | 8.0.19以降で使用可能 |
| LEFT JOINの右側 | CTEをLEFT JOINの右側に置けない | 全バージョン |
| 自己参照回数 | 自己参照は1回のみ | 全バージョン |
| サブクエリ内での参照 | CTEをサブクエリ内で参照不可 (FROM句でのみ参照可能) | 全バージョン |
列型決定の注意事項を以下に示す。
再帰CTEの列型は、アンカーメンバのみから推定される。
非再帰部の列幅が小さいと、再帰メンバで生成される値が切り詰められる可能性がある。
列型決定の問題例を以下に示す。
WITH RECURSIVE cte AS (
SELECT 1 AS n, 'a' AS str
UNION ALL
SELECT n + 1, CONCAT(str, 'a') FROM cte WHERE n < 10
)
SELECT * FROM cte;
上記のクエリでは、str カラムの型がアンカーメンバの 'a' から推定され、CHAR(1) となる。
再帰メンバで生成される CONCAT(str, 'a') の結果は、CHAR(1) に切り詰められる。
解決方法として、CAST() 関数を使用して、明示的に列型を指定する。
WITH RECURSIVE cte AS (
SELECT 1 AS n, CAST('a' AS CHAR(100)) AS str
UNION ALL
SELECT n + 1, CONCAT(str, 'a') FROM cte WHERE n < 10
)
SELECT * FROM cte;
上記のクエリでは、str カラムの型を CHAR(100) に明示指定している。
サンプルクエリ
ウィンドウ関数との組み合わせ
CTEは、ウィンドウ関数と組み合わせて使用することができる。
ランキングを計算する例を以下に示す。
WITH sales_ranked AS (
SELECT
product_id,
product_name,
sales_amount,
ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS rank_row,
RANK() OVER (ORDER BY sales_amount DESC) AS rank_rank,
DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS rank_dense
FROM products
)
SELECT * FROM sales_ranked WHERE rank_row <= 10;
上記の例では、sales_ranked CTEでウィンドウ関数を使用してランキングを計算し、メインクエリで上位10件を抽出している。
カテゴリ別ランキングを計算する例を以下に示す。
WITH category_sales AS (
SELECT
category_id,
product_id,
product_name,
sales_amount,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales_amount DESC) AS rank_in_category
FROM products
)
SELECT * FROM category_sales WHERE rank_in_category <= 3;
上記の例では、カテゴリごとに売上上位3件の商品を抽出している。
移動平均を計算する例を以下に示す。
WITH daily_sales AS (
SELECT
sale_date,
SUM(amount) AS total_amount,
AVG(SUM(amount)) OVER (
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7days
FROM sales
GROUP BY sale_date
)
SELECT * FROM daily_sales ORDER BY sale_date;
上記の例では、7日間の移動平均を計算している。
重複データの検出と削除
CTEを使用して、重複データを検出し、削除することができる。
重複データを検出する例を以下に示す。
以下の例では、email カラムで重複しているレコードを検出する。
WITH duplicates AS (
SELECT
email,
COUNT(*) AS count,
GROUP_CONCAT(id ORDER BY id) AS ids
FROM users
GROUP BY email
HAVING COUNT(*) > 1
)
SELECT * FROM duplicates;
重複データを削除する例を以下に示す。
以下の例では、email カラムで重複しているレコードのうち、最初のレコード以外を削除する。
ROW_NUMBER() ウィンドウ関数を使用して、重複レコードに順位を付けている。
WITH duplicates AS (
SELECT
id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM duplicates WHERE rn > 1);
重複データを一時テーブルに移動する例を以下に示す。
WITH duplicates AS (
SELECT
id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM users
)
INSERT INTO users_duplicates
SELECT u.* FROM users u
JOIN duplicates d ON u.id = d.id
WHERE d.rn > 1;
上記のクエリは、重複データを users_duplicates テーブルに移動する。
パフォーマンス
CTEのパフォーマンスに関する特性を以下に示す。
CTEの最適化方式を以下に示す。
- Merging (マージ)
- CTE定義を外部クエリにインライン化する。
- 単一参照時に適用される。
- Materialization (マテリアライゼーション)
- 結果を一時テーブルとして作成する。
- 複数参照時に適用される。
MySQL 8.0.16以降では、MERGE / NO_MERGE オプティマイザーヒントを使用して、最適化方式を制御できる。
MERGEヒントの使用例WITH cte AS ( SELECT /*+ MERGE */ * FROM large_table WHERE condition ) SELECT * FROM cte;
NO_MERGEヒントの使用例WITH cte AS ( SELECT /*+ NO_MERGE */ * FROM large_table WHERE condition ) SELECT * FROM cte;
EXPLAINでCTEの実行計画を確認する例EXPLAIN WITH RECURSIVE cte AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM cte WHERE n < 100 ) SELECT * FROM cte;
再帰CTEの場合、Extra カラムに Recursive が表示される。
パフォーマンスに関する注意事項を以下に示す。
- 大規模CTE結果セットはディスク上一時テーブルに変換される可能性がある
tmp_table_sizeおよびmax_heap_table_sizeシステム変数の調整で改善可能
- 再帰深度が深いとメモリ消費が増加する
cte_max_recursion_depthで制御する
- 複数回参照時のマテリアライゼーションは効率的
- 1回実行で結果を再利用できる
システム変数の調整例を以下に示す。
-- 一時テーブルのメモリサイズを増加
SET SESSION tmp_table_size = 64 * 1024 * 1024; -- 64MB
SET SESSION max_heap_table_size = 64 * 1024 * 1024; -- 64MB
-- 再帰深度の上限を増加
SET SESSION cte_max_recursion_depth = 10000;
コスト推定について以下に示す。
再帰CTEのコスト推定は、通常のクエリとは異なる。
再帰CTEは、1回の反復あたりのコストとして表示される。
関連ページ
- MySQL - 副問い合わせ
- サブクエリと派生テーブルの詳細
- MySQL - ウィンドウ関数
- ウィンドウ関数の構文と使用例