[SQLのはじめ方] Part24: CTE（WITH句）と再帰CTE

2025.03.31
SQL
SQLプログラミング入門学習教科書

SQLクエリが複雑になってくると、読みにくくなったり、メンテナンスが大変になったりしますよね？🤔 そんな悩みを解決してくれるのが CTE（Common Table Expression：共通テーブル式） です。

このステップでは、CTEの基本的な使い方と、さらに強力な再帰CTEについて学んでいきましょう。これらをマスターすれば、より複雑なデータ操作もスッキリ記述できるようになります！

1. CTE（WITH句）とは？

CTEは、WITH句を使って定義される、一時的な名前付きの結果セットです。クエリ内で何度も同じサブクエリを使う場合や、複雑なクエリを小さな部品に分割したい場合に非常に役立ちます。

💡 CTEのメリット:
  • 可読性の向上： 複雑なクエリがステップに分割され、理解しやすくなります。
  • メンテナンス性の向上： ロジックの変更が容易になります。
  • 再利用性： 同じCTEをクエリ内で複数回参照できます（ただし、1つのWITH句で定義されたCTEはその直後の1つのステートメント内でのみ有効です）。

基本的な構文

CTEはWITHキーワードで始め、その後にCTE名とAS、そしてCTEを定義するクエリを括弧()で囲みます。複数のCTEを定義する場合はカンマ,で区切ります。


WITH cte_name1 AS (
  -- CTE1を定義するSELECT文
  SELECT column1, column2
  FROM some_table
  WHERE condition
), -- 複数のCTEを定義する場合はカンマで区切る
cte_name2 AS (
  -- CTE2を定義するSELECT文 (前のCTEを参照することも可能)
  SELECT columnA, columnB
  FROM another_table
  JOIN cte_name1 ON another_table.id = cte_name1.column1
)
-- メインクエリ (定義したCTEを利用する)
SELECT *
FROM cte_name1
JOIN cte_name2 ON cte_name1.column2 = cte_name2.columnA;

使用例：部署ごとの平均給与を超える従業員

例えば、「各部署の平均給与」を計算し、それを使って「各部署で平均給与を超える従業員」をリストアップしたい場合、CTEを使うと非常にわかりやすくなります。

テーブル構成（例）:

  • employees テーブル: id, name, department_id, salary
  • departments テーブル: id, name

WITH department_avg_salary AS (
  -- 部署ごとの平均給与を計算するCTE
  SELECT
    department_id,
    AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
)
-- メインクエリ: 従業員情報と部署の平均給与を結合し、条件に合う従業員を選択
SELECT
  e.name AS employee_name,
  e.salary,
  d.name AS department_name,
  das.avg_salary AS department_average_salary
FROM employees AS e
JOIN departments AS d ON e.department_id = d.id
JOIN department_avg_salary AS das ON e.department_id = das.department_id
WHERE e.salary > das.avg_salary -- 自分の部署の平均給与より高い給与をもらっている
ORDER BY
  d.name,
  e.salary DESC;

この例では、まずdepartment_avg_salaryというCTEで部署ごとの平均給与を計算しています。その後のメインクエリでは、このCTEを通常のテーブルのようにJOINして利用し、条件に合う従業員を抽出しています。CTEを使わない場合、サブクエリが複雑に絡み合う可能性がありますが、CTEを使うことで処理の流れが明確になります。

2. 再帰CTEとは？ 🌳

再帰CTEは、CTEが自分自身を参照する特殊なタイプのCTEです。これは、階層構造（組織図、親子関係のあるカテゴリなど）やグラフ構造のデータを扱う際に非常に強力です。

例えば、「ある従業員の部下全員（孫部下、ひ孫部下も含む）」や、「あるカテゴリとその全てのサブカテゴリ」をリストアップするような場合に活躍します。

再帰CTEの構造

再帰CTEは、主に2つの部分から構成されます。

  1. アンカーメンバー (Anchor Member): 再帰の開始点となる最初の結果セットを定義する非再帰的なSELECT文です。
  2. 再帰メンバー (Recursive Member): CTE自身を参照し、アンカーメンバーまたは直前の再帰ステップの結果と結合して次の結果セットを生成するSELECT文です。

これら2つのメンバーはUNION ALL（またはUNION）で結合されます。再帰メンバーが新しい行を生成しなくなるまで、再帰が繰り返されます。

基本的な構文

多くのRDBMS（PostgreSQL, MySQL 8.0以降など）ではWITH RECURSIVEを使いますが、SQL Serverなど一部のシステムではRECURSIVEキーワードは不要です。


-- PostgreSQL, MySQL 8.0+ など
WITH RECURSIVE recursive_cte_name (column_list) AS (
  -- 1. アンカーメンバー: 再帰の起点
  SELECT initial_values
  FROM source_table
  WHERE start_condition

  UNION ALL -- アンカーと再帰メンバーを結合

  -- 2. 再帰メンバー: CTE自身を参照
  SELECT next_values
  FROM source_table s
  JOIN recursive_cte_name r ON s.recursive_column = r.column -- 再帰的な結合条件
  WHERE termination_condition -- (任意) 再帰を続ける条件
)
-- メインクエリ: 再帰CTEの結果を利用
SELECT * FROM recursive_cte_name;
⚠️ 注意点:
  • 再帰メンバーは、必ず最終的に結果を返さなくなるような終了条件（明示的または暗黙的）を持つ必要があります。そうでないと無限ループに陥る可能性があります。
  • UNION ALLを使うのが一般的です。UNIONを使うと各ステップで重複排除が行われ、パフォーマンスに影響する可能性があります。

使用例：従業員の階層構造（部下一覧）

特定のマネージャー（例：IDが1の社長）の配下にいる全ての従業員（直接の部下、孫部下など）をリストアップしてみましょう。

テーブル構成（例）:

  • employees テーブル: id, name, manager_id (上司のID、社長などトップはNULL)

WITH RECURSIVE employee_hierarchy (employee_id, employee_name, manager_id, level) AS (
  -- アンカーメンバー: 最上位のマネージャー（ここではID=1）を選択
  SELECT
    id,
    name,
    manager_id,
    0 AS level -- 階層レベル (0から開始)
  FROM employees
  WHERE id = 1 -- 開始点となる従業員のID

  UNION ALL

  -- 再帰メンバー: 直属の部下を検索
  SELECT
    e.id,
    e.name,
    e.manager_id,
    eh.level + 1 -- 階層レベルを1増やす
  FROM employees e
  JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id -- 部下のmanager_idが上司(前の階層)のemployee_idと一致
)
-- メインクエリ: 階層情報を表示
SELECT
  employee_id,
  employee_name,
  manager_id,
  level
FROM employee_hierarchy
ORDER BY level, manager_id, employee_id;

このクエリでは：

  1. アンカーメンバーがid = 1の従業員（社長）を取得し、レベルを0とします。
  2. 再帰メンバーが、直前のステップで見つかった従業員（employee_hierarchy）をマネージャーとして持つ従業員（e.manager_id = eh.employee_id）を検索し、レベルを1増やします。
  3. 部下が見つからなくなるまで、ステップ2が繰り返されます。
  4. 最後に、employee_hierarchy CTEから全結果を取得し、レベル順に表示します。

このように、再帰CTEを使うことで、通常は複雑な手続きが必要な階層データの問い合わせを、宣言的なSQLだけでエレガントに記述できます。

まとめ

今回は、SQLの強力な機能であるCTE（共通テーブル式）と再帰CTEについて学びました。

✅ 今回のポイント:
  • CTE (WITH句): クエリを分割し、可読性とメンテナンス性を向上させるための一時的な名前付き結果セット。
  • 再帰CTE (WITH RECURSIVE): 自分自身を参照することで、階層構造やグラフ構造のデータを効率的に問い合わせることができる。アンカーメンバーと再帰メンバーで構成される。

CTEを使いこなすことで、複雑なSQLクエリもよりシンプルに、そして効率的に書けるようになります。特に再帰CTEは、最初は少し難しく感じるかもしれませんが、慣れると非常に便利です。ぜひ実際のデータで試してみてくださいね！🚀

コメント

タイトルとURLをコピーしました