MySQL - CTEのソースを表示

== 概要 ==
CTEは、Common Table Expressionsの略であり、WITH句を使用して定義される名前付き一時結果セットである。<br>
MySQL 8.0で導入された機能であり、MySQL 5.7以前のバージョンでは使用できない。<br>
<br>
CTEは、複雑なクエリをより可読性の高い形で記述するための機能であり、サブクエリや派生テーブルの代替として使用できる。<br>
CTEは、SELECT文、UPDATE文、DELETE文およびサブクエリ内で使用可能である。<br>
<br>
CTEには、非再帰CTEと再帰CTEの2タイプが存在する。<br>
非再帰CTEは、通常のサブクエリと同様に、単純な一時結果セットを定義するために使用される。<br>
再帰CTEは、階層データの探索、グラフ走査、数列生成等、再帰的な処理を行うために使用される。<br>
<br>
CTEは、同一クエリ内で複数回参照できるため、派生テーブルよりも効率的で可読性が高い。<br>
複数回参照される場合、CTE結果はメモリ内一時テーブルにマテリアライズ (実体化) される。<br>
<br>
CTEは、ウィンドウ関数との組み合わせ、重複データの検出、階層データの探索等、実用的な場面で広く使用される。<br>
<br><br>

== 基本構文 ==
==== WITH句の構文 ====
WITH句の基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 WITH [RECURSIVE]
    cte_name [(col_name [, col_name] ...)] AS (subquery)
 SELECT ... FROM cte_name;
 </syntaxhighlight>
<br>
下表に、構文要素を示す。<br>
<br>
<center>
{| class="wikitable"
|+ WITH句の構成要素
|-
! キーワード !! 説明
|-
| <code>RECURSIVE</code>
|
* 再帰CTEを定義する場合に指定する。
* 複数CTEのうち1つでも再帰的ならば <code>WITH RECURSIVE</code> を指定する。
|-
| <code>cte_name</code>
|
* CTEに付ける名前 (識別子)
|-
| <code>col_name</code>
|
* CTEのカラム名リスト (オプション)
* 省略した場合は、サブクエリのSELECTリストから自動推論される。
|-
| <code>subquery</code>
|
* CTEの結果セットを生成するサブクエリ
|}
</center>
<br>
カラムリストは、明示的に指定することもできるし、サブクエリのSELECTリストから自動推論させることもできる。<br>
<br>
以下の例では、<u>cte1</u> という名前のCTEを定義し、<u>users</u> テーブルから <u>status = 'active'</u> の行を取得している。<br>
<br>
 <syntaxhighlight lang="mysql">
 WITH cte1 AS (
    SELECT id, name FROM users WHERE status = 'active'
 )
 SELECT * FROM cte1;
 </syntaxhighlight>
<br>
==== 複数CTEの定義 ====
同一WITH句内で、複数のCTEをカンマ区切りで定義できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 WITH
    cte1 AS (SELECT ... ),
    cte2 AS (SELECT ... ),
    cte3 AS (SELECT ... )
 SELECT ... FROM cte1, cte2, cte3;
 </syntaxhighlight>
<br>
CTE間の参照について以下に示す。<br>
* 先行するCTEのみ参照可能
*: <u>cte2</u> は <u>cte1</u> を参照できるが、<u>cte3</u> を参照できない。
* 後方参照は不可
*: <u>cte1</u> は、<u>cte2</u> または <u>cte3</u> を参照できない。
* 相互再帰は不可
*: <u>cte1</u> と <u>cte2</u> が互いに参照し合うことはできない。
<br>
複数CTEのサンプルクエリを以下に示す。<br>
以下の例では、<u>active_users</u> CTEを定義し、その結果を <u>user_orders</u> CTEで参照している。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
同じレベルで複数WITH句を並べることはできない。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- エラー : 複数WITH句を並べることは不可
 WITH cte1 AS (SELECT ...)
 WITH cte2 AS (SELECT ...)
 SELECT ...;
 </syntaxhighlight>
<br><br>

== 非再帰CTE ==
==== 基本的な使用方法 ====
非再帰CTEは、通常のサブクエリと同様に、単純な一時結果セットを定義するために使用される。<br>
<br>
非再帰CTEの基本的な使用例を以下に示す。<br>
以下の例では、<code>sales_summary</code> CTEを定義し、売上データの集計結果を保持している。<br>
メインクエリでは、CTEの結果を <code>products</code> テーブルと結合して、商品名を含む集計結果を取得している。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
非再帰CTEは、SELECT文、UPDATE文、DELETE文の開始位置で使用可能である。<br>
また、サブクエリ内でも使用可能である。<br>
<br>
CTEは、同一クエリ内で複数回参照できる。<br>
これは、派生テーブルとの大きな違いである。<br>
<br>
複数回参照のサンプルクエリを以下に示す。<br>
以下の例では、<u>active_users</u> CTEを2回参照している。<br>
派生テーブルを使用する場合は、同じサブクエリを2回記述する必要があるが、CTEでは1回の定義で済む。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
==== JOINとの組み合わせ ====
CTEは、JOINと組み合わせて使用することができる。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
上記の例では、2つのCTEを定義し、それぞれを <u>customers</u> テーブルと結合している。<br>
<u>recent_orders</u> CTEは、過去30日間の注文数を集計している。<br>
<u>high_value_customers</u> CTEは、合計購入金額が10000を超える顧客を抽出している。<br>
<br>
==== DML文での使用 ====
CTEは、INSERT文、UPDATE文、DELETE文でも使用できる。<br>
<br>
CTEを使用することにより、DML文の可読性が向上する。<br>
<br>
* INSERT文での使用例
*: <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
*: <br>
* UPDATE文での使用例
*: <syntaxhighlight lang="mysql">
 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);
 </syntaxhighlight>
*: <br>
* DELETE文での使用例
*: <syntaxhighlight lang="mysql">
 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);
 </syntaxhighlight>
<br><br>

== 再帰CTE ==
==== 再帰CTEの構造 ====
再帰CTEは、自分自身を参照するCTEである。<br>
再帰CTEは、階層データの探索、グラフ走査、数列生成等、再帰的な処理を行うために使用される。<br>
<br>
再帰CTEの構造を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 WITH RECURSIVE cte_name AS (
    -- アンカーメンバ (非再帰部分)
    SELECT ...
 
    UNION ALL
 
    -- 再帰メンバ (再帰部分)
    SELECT ... FROM cte_name WHERE ...
 )
 SELECT * FROM cte_name;
 </syntaxhighlight>
<br>
再帰CTEは、<u>アンカーメンバ (非再帰部分)</u> / <u>再帰メンバ (再帰部分)</u> の2つの部分から構成される。<br>
<br>
<center>
{| class="wikitable"
|+ 再帰CTEの構造要素
! 要素 !! 説明 !! 役割
|-
| アンカーメンバ (非再帰部分) || CTEの名前を参照しないSELECT文 || 初期行セットを返す<br>再帰処理の開始点
|-
| 再帰メンバ (再帰部分) || CTEの名前をFROM句で参照するSELECT文 || 新しい行を生成<br>新しい行が生成されない場合に終了
|}
</center>
<br>
アンカーメンバと再帰メンバは、<code>UNION ALL</code> または <code>UNION DISTINCT</code> で結合される。<br>
通常は、<code>UNION ALL</code> を使用する。<br>
<br>
再帰処理の流れを以下に示す。<br>
# アンカーメンバを実行して、初期行セットを生成する。
# 初期行セットを再帰メンバに渡す。
# 再帰メンバが新しい行を生成する。
# 新しい行を再帰メンバに渡す。(ステップ3に戻る)
# 再帰メンバが新しい行を生成しなくなった場合、再帰処理を終了する。
<br>
<u>再帰CTEを定義する場合は、必ず、<code>WITH RECURSIVE</code> キーワードを使用する。</u><br>
<br>
==== 数列の生成 ====
再帰CTEを使用して、数列を生成することができる。<br>
<br>
1からNまでの連番を生成するサンプルクエリを以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 WITH RECURSIVE numbers AS (
    SELECT 1 AS n
 
    UNION ALL
 
    SELECT n + 1 FROM numbers WHERE n < 10
 )
 SELECT * FROM numbers;
 </syntaxhighlight>
<br>
上記のクエリの実行結果を以下に示す。<br>
 +----+
 | n  |
 +----+
 |  1 |
 |  2 |
 |  3 |
 |  4 |
 |  5 |
 |  6 |
 |  7 |
 |  8 |
 |  9 |
 | 10 |
 +----+
<br>
フィボナッチ数列を生成する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
上記のクエリの実行結果を以下に示す。<br>
 +----+-------+
 | n  | fib_n |
 +----+-------+
 |  1 |     0 |
 |  2 |     1 |
 |  3 |     1 |
 |  4 |     2 |
 |  5 |     3 |
 |  6 |     5 |
 |  7 |     8 |
 |  8 |    13 |
 |  9 |    21 |
 | 10 |    34 |
 +----+-------+
<br>
日付系列を生成するサンプルクエリを以下に示す。<br>
以下の例では、2024年1月1日から2024年1月31日までの日付系列を生成する。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
==== 階層データの探索 ====
再帰CTEは、階層データの探索に適している。<br>
<br>
組織図の階層構造を持つテーブル例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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);
 </syntaxhighlight>
<br>
CEOから始まる組織階層を探索するサンプルクエリを以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
上記のクエリの実行結果を以下に示す。<br>
 +-------+---------------------+-------------------------------------------------------+
 | 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            |
 +-------+---------------------+-------------------------------------------------------+
<br>
上記の例では、<code>level</code> カラムで階層レベルを表現し、<code>path</code> カラムで階層パスを構築している。<br>
<code>CONCAT()</code> 関数を使用して、パスを連結している。<br>
<br>
カテゴリツリーの探索サンプルクエリを以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
上記のクエリは、カテゴリツリーをインデント付きで表示する。<br>
<code>REPEAT()</code> 関数を使用して、階層レベルに応じたインデントを生成している。<br>
<br>
==== 再帰深度の制御 ====
再帰CTEは、無限ループを防ぐために、再帰深度を制御する必要がある。<br>
<br>
<center>
{| class="wikitable"
|+ 再帰深度の制御方法
! 方法 !! 説明 !! デフォルト値
|-
| <code>cte_max_recursion_depth</code> システム変数 || セッションまたはグローバルレベルで再帰深度の上限を設定 || 1000
|-
| <code>max_execution_time</code> システム変数 || クエリ実行時間の上限を設定 (ミリ秒) || 0 (無制限)
|-
| <code>SET_VAR()</code> オプティマイザーヒント || クエリごとに再帰深度の上限を設定 || -
|-
| <code>LIMIT</code> 句 || 再帰SELECT部で使用して、生成される行数を制限 || -
|}
</center>
<br>
<code>cte_max_recursion_depth</code> システム変数の設定例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- セッションレベルで設定
 SET SESSION cte_max_recursion_depth = 5000;
 
 -- グローバルレベルで設定
 SET GLOBAL cte_max_recursion_depth = 10000;
 </syntaxhighlight>
<br>
デフォルト値は1000であり、再帰処理が1000回を超えるとエラーが発生する。<br>
<br>
<code>SET_VAR()</code> オプティマイザーヒントの使用例を以下に示す。<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
上記の例では、クエリごとに再帰深度の上限を1000000に設定している。<br>
<br>
<code>LIMIT</code> 句の使用例を以下に示す。<br>
 <syntaxhighlight lang="mysql">
 WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte LIMIT 100
 )
 SELECT * FROM cte;
 </syntaxhighlight>
<br>
<code>LIMIT</code> 句は、MySQL 8.0.19以降で再帰SELECT部で使用可能である。<br>
MySQL 8.0.18以前では、再帰SELECT部で <code>LIMIT</code> 句を使用できない。<br>
<br><br>

== CTEとサブクエリの比較 ==
CTEとサブクエリ (派生テーブル) の比較を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ CTE vs サブクエリ vs 一時テーブル
! 項目 !! CTE !! サブクエリ (派生テーブル) !! 一時テーブル
|-
| 複数回参照 || 可能 (効率的、マテリアライズ) || 不可 (定義を繰り返す必要) || 可能
|-
| 再帰処理 || WITH RECURSIVE対応 || 不可 || 不可
|-
| 可読性 || 優秀 (名前付き、先頭で定義) || ネストが深くなりがち || 優秀 (名前付き)
|-
| 自己参照 || 再帰CTEで可能 || 不可 || 不可
|-
| 有効期間 || 単一ステートメント || 単一ステートメント || セッションまたは明示削除まで
|-
| スコープ || ステートメント内に限定 || サブクエリ内に限定 || セッション全体
|-
| インデックス作成 || 不可 || 不可 || 可能
|-
| テーブル作成権限 || 不要 || 不要 || 必要
|-
| 定義方法 || WITH句 || FROM (SELECT ...) AS alias || CREATE TEMPORARY TABLE
|}
</center>
<br>
下表に、CTEの主なメリットを示す。<br>
<center>
{| class="wikitable"
|+ CTEの主なメリット
|-
! メリット !! 説明
|-
| 複数回参照が可能
| 派生テーブルでは、同じサブクエリを複数回記述する必要があるが、CTEでは1回の定義で済む
|-
| 再帰処理が可能
| 階層データの探索、グラフ走査等、再帰的な処理を行うことができる
|-
| 可読性が高い
| CTEは名前付きであり、クエリの先頭で定義されるため、クエリ全体の構造が理解しやすい
|-
| 自己参照が可能
| 再帰CTEでは、CTE自身を参照することができる
|}
</center>
<br>
下表に、CTEの主なデメリットを示す。<br>
<center>
{| class="wikitable"
|+ CTEの主なデメリット
|-
! デメリット !! 説明
|-
| 有効期間が短い
| CTEは単一ステートメント内でのみ有効であり、複数ステートメントにまたがって使用できない
|-
| インデックスを作成できない
| CTEには、インデックスを作成できない (一時テーブルでは可能)
|}
</center>
<br>
CTEと派生テーブルの比較例を以下に示す。<br>
CTEを使用することにより、可読性が向上し、定義の重複を避けることができる。<br>
<br>
* 派生テーブルを使用した例 (複数回参照のために定義を繰り返す)
*: <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
*: <br>
* CTEを使用した例 (1回の定義で複数回参照)
*: <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br><br>

== CTEの制限事項 ==
CTE定義には、以下に示すような制限事項がある。<br>
<br>
* 同じWITH句内で同じ名前のCTEを複数定義することはできない
* 前に定義されたCTEのみ参照可能 (後方参照は不可)
* 同一ステートメント内で複数WITH句を並べることはできない
* CTEは単一ステートメント内でのみ有効
<br>
再帰CTEの制限を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ 再帰CTEの制限事項 (再帰SELECT部)
! 制限項目 !! 説明 !! 対応バージョン
|-
| 集約関数 || 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句でのみ参照可能) || 全バージョン
|}
</center>
<br>
列型決定の注意事項を以下に示す。<br>
再帰CTEの列型は、アンカーメンバのみから推定される。<br>
非再帰部の列幅が小さいと、再帰メンバで生成される値が切り詰められる可能性がある。<br>
<br>
列型決定の問題例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
上記のクエリでは、<code>str</code> カラムの型がアンカーメンバの <code>'a'</code> から推定され、<code>CHAR(1)</code> となる。<br>
再帰メンバで生成される <code>CONCAT(str, 'a')</code> の結果は、<code>CHAR(1)</code> に切り詰められる。<br>
<br>
解決方法として、<code>CAST()</code> 関数を使用して、明示的に列型を指定する。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
上記のクエリでは、<code>str</code> カラムの型を <code>CHAR(100)</code> に明示指定している。<br>
<br><br>

== サンプルクエリ ==
==== ウィンドウ関数との組み合わせ ====
CTEは、ウィンドウ関数と組み合わせて使用することができる。<br>
<br>
ランキングを計算する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
上記の例では、<code>sales_ranked</code> CTEでウィンドウ関数を使用してランキングを計算し、メインクエリで上位10件を抽出している。<br>
<br>
カテゴリ別ランキングを計算する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
上記の例では、カテゴリごとに売上上位3件の商品を抽出している。<br>
<br>
移動平均を計算する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
上記の例では、7日間の移動平均を計算している。<br>
<br>
==== 重複データの検出と削除 ====
CTEを使用して、重複データを検出し、削除することができる。<br>
<br>
重複データを検出する例を以下に示す。<br>
以下の例では、<u>email</u> カラムで重複しているレコードを検出する。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
重複データを削除する例を以下に示す。<br>
以下の例では、<u>email</u> カラムで重複しているレコードのうち、最初のレコード以外を削除する。<br>
<code>ROW_NUMBER()</code> ウィンドウ関数を使用して、重複レコードに順位を付けている。<br>
<br>
 <syntaxhighlight lang="mysql">
 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);
 </syntaxhighlight>
<br>
重複データを一時テーブルに移動する例を以下に示す。<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
上記のクエリは、重複データを <u>users_duplicates</u> テーブルに移動する。<br>
<br><br>

== パフォーマンス ==
CTEのパフォーマンスに関する特性を以下に示す。<br>
<br>
CTEの最適化方式を以下に示す。<br>
* Merging (マージ)
*: CTE定義を外部クエリにインライン化する。
*: 単一参照時に適用される。
*: <br>
* Materialization (マテリアライゼーション)
*: 結果を一時テーブルとして作成する。
*: 複数参照時に適用される。
<br>
MySQL 8.0.16以降では、<code>MERGE</code> / <code>NO_MERGE</code> オプティマイザーヒントを使用して、最適化方式を制御できる。<br>
<br>
* <code>MERGE</code> ヒントの使用例
*: <syntaxhighlight lang="mysql">
 WITH cte AS (
    SELECT /*+ MERGE */ * FROM large_table WHERE condition
 )
 SELECT * FROM cte;
 </syntaxhighlight>
*: <br>
* <code>NO_MERGE</code> ヒントの使用例
*: <br>
*: <syntaxhighlight lang="mysql">
 WITH cte AS (
    SELECT /*+ NO_MERGE */ * FROM large_table WHERE condition
 )
 SELECT * FROM cte;
 </syntaxhighlight>
*: <br>
* <code>EXPLAIN</code> でCTEの実行計画を確認する例
*: <br>
*: <syntaxhighlight lang="mysql">
 EXPLAIN WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 100
 )
 SELECT * FROM cte;
 </syntaxhighlight>
<br>
再帰CTEの場合、<u>Extra</u> カラムに <u>Recursive</u> が表示される。<br>
<br>
パフォーマンスに関する注意事項を以下に示す。<br>
* 大規模CTE結果セットはディスク上一時テーブルに変換される可能性がある
*: <code>tmp_table_size</code> および <code>max_heap_table_size</code> システム変数の調整で改善可能
*: <br>
* 再帰深度が深いとメモリ消費が増加する
*: <code>cte_max_recursion_depth</code> で制御する
*: <br>
* 複数回参照時のマテリアライゼーションは効率的
*: 1回実行で結果を再利用できる
<br>
システム変数の調整例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 一時テーブルのメモリサイズを増加
 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;
 </syntaxhighlight>
<br>
コスト推定について以下に示す。<br>
再帰CTEのコスト推定は、通常のクエリとは異なる。<br>
再帰CTEは、1回の反復あたりのコストとして表示される。<br>
<br><br>

== 関連ページ ==
* [[MySQL - 副問い合わせ]]
*: サブクエリと派生テーブルの詳細
* [[MySQL - ウィンドウ関数]]
*: ウィンドウ関数の構文と使用例
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,MySQL,CTE,WITH句,Common Table Expressions,再帰CTE,非再帰CTE,階層データ,SQL,データベース,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - MySQLのCTE (WITH句) の構文、使用方法、実用例 | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:MySQL]]