MySQL WITH句を徹底解説｜初心者から実務で使える応用例まで

1. はじめに

2. WITH句（共通テーブル式）の基礎知識

WITH句とは何か？

WITH句の基本構文

WITH テーブル名 AS (
  SELECT 列1, 列2
  FROM 元のテーブル
  WHERE 条件
)
SELECT 列1, 列2
FROM テーブル名;

サブクエリやビューとの違い

WITH句を使用するメリット

1. クエリの可読性向上 サブクエリが複数ある場合でも、WITH句で整理することで構造が明確になります。
2. 再利用性の向上 一時的な結果セットを定義することで、クエリ内で何度も参照できます。
3. 保守性の向上 クエリを論理的に分割できるため、修正や拡張が容易になります。

3. MySQL WITH句の基本的な使い方

サブクエリの置き換え

SELECT AVG(sales.total) AS average_sales
FROM (
  SELECT SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
) AS sales;

WITH sales AS (
  SELECT SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT AVG(sales.total) AS average_sales
FROM sales;

複数の共通テーブル式（CTE）の定義

WITH 
  sales_per_customer AS (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ),
  high_value_customers AS (
    SELECT customer_id
    FROM sales_per_customer
    WHERE total_sales > 10000
  )
SELECT customer_id
FROM high_value_customers;

ネストされたCTEの利用方法

WITH 
  sales_data AS (
    SELECT product_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY product_id
  ),
  ranked_sales AS (
    SELECT product_id, total_sales,
           RANK() OVER (ORDER BY total_sales DESC) AS rank
    FROM sales_data
  )
SELECT product_id, total_sales
FROM ranked_sales
WHERE rank <= 5;

実務での基本的な使い方のポイント

1. クエリを分割して考える CTEを段階的に構築することで、クエリを読みやすくし、デバッグを容易にします。
2. 一時的な計算結果の保存 複数回使用する計算結果やフィルタリング条件をCTEにまとめることで、コードの重複を削減できます。
3. 大規模データでの注意 CTEは一時的な結果セットを生成するため、大量のデータを扱う場合はパフォーマンスへの影響を考慮する必要があります。

4. 再帰的なWITH句の応用例

再帰的なWITH句とは？

再帰CTEの基本構文

WITH RECURSIVE 再帰テーブル名 AS (
  初期クエリ -- 再帰の開始点
  UNION ALL
  再帰クエリ -- 再帰的に呼び出されるクエリ
)
SELECT * FROM 再帰テーブル名;

• 初期クエリ: 再帰処理の最初のデータセットを取得します。
• 再帰クエリ: 初期クエリまたは前回の結果を基に新しいデータを生成します。
• UNION ALL: 初期クエリと再帰クエリの結果を結合します。

階層データを処理する例

WITH RECURSIVE employee_hierarchy AS (
  -- 初期クエリ: トップレベルの社員を取得
  SELECT employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- 再帰クエリ: 直属の部下を取得
  SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  INNER JOIN employee_hierarchy eh
  ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

再帰CTEの制限と注意点

1. 再帰の終了条件が必要 再帰クエリが終了条件を満たさない場合、無限ループが発生する可能性があります。適切な条件を設けて無限ループを防ぎましょう。
2. パフォーマンスへの影響 再帰CTEは大量のデータに対して多くの計算を伴うため、クエリの実行時間が長くなる場合があります。LIMIT句やフィルタリング条件を活用して効率化を図りましょう。
3. 再帰深度の制限 MySQLでは、再帰の深度に制限があり、非常に深い再帰処理を行う場合には注意が必要です。この制限はmax_recursive_iterationsパラメータで設定可能です。

再帰CTEを活用するシナリオ

• フォルダ構造のトラバース: フォルダやサブフォルダを再帰的に検索する。
• 組織図の作成: 上司から部下への階層を視覚化。
• カテゴリツリーの表示: 商品カテゴリやタグの階層を取得。

5. WITH句の使用時の注意点

パフォーマンスへの影響と最適化

1. CTEの再計算 WITH句で定義されたCTEは、基本的にクエリが参照されるたびに再計算されます。したがって、同じCTEを複数回利用すると、クエリの実行時間が長くなる可能性があります。 例:

   WITH sales AS (
     SELECT product_id, SUM(amount) AS total_sales
     FROM orders
     GROUP BY product_id
   )
   SELECT * FROM sales WHERE total_sales > 1000;
   SELECT COUNT(*) FROM sales;

   CREATE TEMPORARY TABLE temp_sales AS
   SELECT product_id, SUM(amount) AS total_sales
   FROM orders
   GROUP BY product_id;

   SELECT * FROM temp_sales WHERE total_sales > 1000;
   SELECT COUNT(*) FROM temp_sales;

2. 複雑なCTEの分割 WITH句がネストされすぎると、クエリ全体が複雑化し、デバッグが難しくなる場合があります。一つのCTE内での処理が過剰に複雑にならないように、適切に分割することが重要です。

大量データでの使用

• WHERE句でデータを絞る CTE内で不要なデータをフィルタリングすることで、計算量を削減できます。

  WITH filtered_orders AS (
    SELECT *
    FROM orders
    WHERE order_date > '2023-01-01'
  )
  SELECT customer_id, SUM(amount)
  FROM filtered_orders
  GROUP BY customer_id;

• LIMIT句の活用 データ量が多い場合は、必要なデータのみを抽出するためにLIMIT句を使用します。

MySQLバージョンの互換性

• サブクエリの利用 WITH句の代わりにサブクエリを直接使用します。

  SELECT AVG(total_sales)
  FROM (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ) AS sales;

• ビューの作成 再利用可能なクエリが必要な場合は、ビューを使用するのも有効です。

  CREATE VIEW sales_view AS
  SELECT customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY customer_id;

  SELECT AVG(total_sales) FROM sales_view;

WITH句の適切な使い方

1. 読みやすさを重視 WITH句を使う目的はクエリを整理し、可読性を向上させることです。無理に使いすぎると却ってクエリが複雑化するため、必要な場合にのみ使用しましょう。
2. パフォーマンスの検証 実行計画（EXPLAINコマンド）を確認し、パフォーマンスを最適化する方法を検討します。

   EXPLAIN
   WITH sales AS (
     SELECT product_id, SUM(amount) AS total_sales
     FROM orders
     GROUP BY product_id
   )
   SELECT * FROM sales WHERE total_sales > 1000;

6. 実務での応用例

売上データの集計

WITH monthly_sales AS (
  SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS sales_month,
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY sales_month
)
SELECT 
  sales_month, 
  total_sales,
  AVG(total_sales) OVER () AS average_sales
FROM monthly_sales;

特定の条件に基づくデータのフィルタリング

WITH customer_totals AS (
  SELECT 
    customer_id, 
    SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT 
  customer_id, 
  total_spent
FROM customer_totals
WHERE total_spent > 100000;

階層構造データの分析

WITH RECURSIVE employee_hierarchy AS (
  SELECT 
    employee_id, 
    name, 
    manager_id, 
    1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT 
    e.employee_id, 
    e.name, 
    e.manager_id, 
    eh.level + 1
  FROM employees e
  INNER JOIN employee_hierarchy eh
  ON e.manager_id = eh.employee_id
)
SELECT 
  employee_id, 
  name, 
  manager_id, 
  level
FROM employee_hierarchy
ORDER BY level, manager_id;

複数の共通テーブル式を活用した分析

WITH category_sales AS (
  SELECT 
    category_id, 
    product_id, 
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY category_id, product_id
),
ranked_sales AS (
  SELECT 
    category_id, 
    product_id, 
    total_sales,
    RANK() OVER (PARTITION BY category_id ORDER BY total_sales DESC) AS rank
  FROM category_sales
)
SELECT 
  category_id, 
  product_id, 
  total_sales
FROM ranked_sales
WHERE rank <= 3;

実務での応用時のポイント

1. クエリの分割設計 WITH句を活用してクエリを分割し、段階的にデータを処理することで、可読性を保ちながら複雑な分析を行えます。
2. 必要なデータのみを抽出 WHERE句やLIMIT句を活用して、無駄なデータを処理しないようにすることで、効率的なクエリ設計が可能です。
3. 業務での柔軟な活用 売上分析、顧客セグメンテーション、在庫管理など、業務に応じた柔軟な活用が可能です。

7. FAQ（よくある質問）

Q1: WITH句はどのような場合に使用すべきですか？

• 複雑なサブクエリを簡潔に記述したい場合。
• 複数のクエリで同じデータセットを繰り返し使用する場合。
• クエリを論理的に分割して可読性を向上させたい場合。

Q2: 再帰CTEはどのような場合に役立ちますか？

• 階層データの処理（例: 組織ツリー、カテゴリ構造の展開）。
• フォルダやファイルの階層表示。
• 数値や期間の逐次計算（例: フィボナッチ数列の計算）。

Q3: WITH句を使用したクエリはビューよりも効率的ですか？

• WITH句は一時的な結果セットを作成し、そのクエリ内でのみ使用されます。頻繁に再利用する必要がないデータに適しています。
• ビューはデータベース内に永続的に保存され、他のクエリで再利用可能です。繰り返し使用するクエリに適しています。

Q4: WITH句の使用でパフォーマンスが低下する原因は何ですか？

• CTEの再計算: WITH句の結果が複数回参照されるたびに再計算が行われるため、処理時間が増加します。
• 大量データの処理: WITH句内で大量のデータを生成すると、メモリ使用量が増え、パフォーマンスが低下します。
• 適切なインデックスがない: WITH句内のクエリに適切なインデックスがない場合、処理速度が遅くなる可能性があります。

• 再利用頻度が高い場合は一時テーブルやビューを検討する。
• WHERE句やLIMIT句を使用してデータを適切に絞り込む。

Q5: WITH句をサポートしていないMySQLバージョンで代替手段はありますか？

• サブクエリの利用 WITH句の代わりに、サブクエリを直接使用します。

  SELECT AVG(total_sales)
  FROM (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ) AS sales;

• 一時テーブルの利用 再利用するデータセットを一時テーブルに保存して対応します。

  CREATE TEMPORARY TABLE temp_sales AS
  SELECT customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY customer_id;

  SELECT AVG(total_sales) FROM temp_sales;

Q6: WITH句を使う際のベストプラクティスは何ですか？

• 簡潔さを重視: 複雑なクエリを無理にWITH句に詰め込まず、適切に分割して整理する。
• パフォーマンスの検証: 実行計画（EXPLAINコマンド）を確認し、必要に応じてクエリを最適化する。
• 再利用性の検討: 再利用頻度が高い場合はビューや一時テーブルを活用する。

8. まとめ

WITH句の主なメリット

1. クエリの可読性向上 複雑なサブクエリを簡潔に整理することで、SQLコードの読みやすさと保守性を向上させます。
2. クエリの再利用性 同じデータセットを複数回参照する場合に効率的に処理できます。
3. 再帰的なデータ操作が可能 再帰CTEを活用することで、階層データや繰り返し計算をシンプルに処理できます。

実務での活用ポイント

• 売上や顧客データの分析に役立ち、結果を段階的に集計できます。
• 階層構造のデータ処理（組織図やカテゴリ構造など）では再帰CTEを効果的に使用可能です。
• 必要に応じてビューや一時テーブルと組み合わせることで、柔軟かつ効率的なデータベース操作が実現します。

使用時の注意点

• WITH句は便利ですが、適切に使用しなければパフォーマンスが低下する可能性があります。
• 再利用性やパフォーマンスを考慮して、ケースバイケースでビューや一時テーブルとの使い分けを検討しましょう。
• 実行計画（EXPLAINコマンド）を確認してクエリの効率を検証することが重要です。

次のステップ

1. シンプルなクエリから始めて、WITH句を使った構造化を練習する。
2. 再帰CTEを使って階層データや複雑なシナリオに挑戦する。
3. パフォーマンスの最適化を意識して、SQLスキルをさらに向上させる。