SQL Academy · レッスン

再帰CTEの仕組み

Q: 「再帰CTEの仕組み」レッスンは無料ですか？

はい。「再帰CTEの仕組み」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

Q: 「再帰CTEの仕組み」で何を学びますか？

基底ケースと再帰ステップを組み合わせます。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

Q: SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン1/4です。

Q: 「再帰CTEの仕組み」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

Q: このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

基底ケースと再帰ステップを組み合わせます。

「再帰CTEの仕組み」はCoddyKit上の無料SQL Academyレッスンです。これはレッスン1/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

再帰CTEとは

再帰CTEとは、自身を参照する共通テーブル式です。純粋なSQLで記述されたループのように、条件が満たされるまでステップを繰り返すクエリを作成できます。

再帰CTEはWITH RECURSIVEキーワードで定義し、組織図、フォルダツリー、部品表の構造など、階層データやグラフに似たデータをたどるのに適しています。

2つの部分からなる構造

すべての再帰CTEは、UNION ALLで区切られた正確に2つの部分から構成されます。

1. 基底ケース — 開始行を返す、再帰しないSELECTです。

2. 再帰ステップ — CTEを自身に結合し、次のレベルの行を生成するSELECTです。

エンジンは、結果として新しい行が0件になるまで再帰ステップを実行し続け、結果を蓄積します。

WITH RECURSIVE cte_name AS (
  -- Base case
  SELECT ...
  UNION ALL
  -- Recursive step (references cte_name)
  SELECT ... FROM source JOIN cte_name ON ...
)
SELECT * FROM cte_name;

1から5まで数える

最も単純な再帰CTEは、数値を数えるものです。基底ケースで値1を初期値として設定します。再帰ステップでは、反復ごとに1を加えます。再帰ステップ内のWHERE句が終了条件として機能します。これがなければ、クエリは永遠に実行され続けます。

WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;

ステップごとの実行

カウンターCTEが反復ごとにエンジンによって処理される流れは次のとおりです。

反復0（基底ケース）：{1}を返します。

反復1：{1}に再帰ステップを適用し、{2}を返します。

反復2：{2}に再帰ステップを適用し、{3}を返します。

反復3、4：{4}、続いて{5}を返します。

反復5：WHERE n < 5 は n=5 では偽になるため、0行が返されます。クエリは終了します。

蓄積されたすべての行（1、2、3、4、5）が最終結果です。

階層テーブルの準備

再帰CTEは、自己参照テーブルで力を発揮します。ここでは、各従業員が同じテーブルを参照する任意のmanager_idを持つemployeesテーブルを作成します。

CREATE TABLE employees (
  id       INTEGER PRIMARY KEY,
  name     VARCHAR(50),
  manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
  (1, 'Alice',   NULL),
  (2, 'Bob',     1),
  (3, 'Carol',   1),
  (4, 'Dave',    2),
  (5, 'Eve',     2),
  (6, 'Frank',   3);

階層をたどる

これで、CEO（Alice, id=1）から始めて、報告関係の全体をたどれるようになります。基底ケースではAliceを選択し、再帰ステップでは、CTE内にすでに存在するidとmanager_idが一致するすべての従業員を見つけます。

結果には、ツリーの深さに関係なく、Aliceから到達可能なすべての従業員が含まれます。

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT depth, name FROM org_tree ORDER BY depth, name;

パスを追跡する

よくある拡張として、ルートから各ノードまでの完全な連鎖を示すパス文字列を構築します。再帰で下位へ進みながら、名前を' -> 'で区切って連結します。

これにより、パンくず形式のナビゲーションを表示したり、深い階層をデバッグしたりすることが簡単になります。

WITH RECURSIVE org_tree AS (
  SELECT id, name, name AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, ot.path || ' -> ' || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, path FROM org_tree ORDER BY path;

再帰の深さを制限する

深いデータや循環したデータでは、再帰CTEの実行に非常に長い時間がかかることがあります。安全のために、次の2つを実践してください。

1. 深さを追跡し、WHERE句を追加する — WHERE depth < 10により、10レベルを超えないようにします。

2. 循環検出用の列を使用する — 一部のデータベース（PostgreSQL 14以降）では、CYCLE構文を使用して同じノードへの再訪を自動的に検出できます。

WITH RECURSIVE org_tree AS (
  SELECT id, name, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
  WHERE ot.depth < 10
)
SELECT depth, name FROM org_tree;

再帰CTEにおけるUNIONとUNION ALL

再帰ステップでは、ほとんどの場合UNIONではなくUNION ALLを使用します。その理由は次のとおりです。

UNIONは、結果セット全体を比較して反復ごとに行の重複を排除します。これは非常にコストが高く、同じノードに複数の経路から正当に到達できるグラフでは、意味が変わる可能性があります。

UNION ALLは重複排除を行わずにすべての行を保持するため、ツリーの走査ではより高速で、正しい結果になります。重複を排除する明確な必要があり、パフォーマンスへのコストを理解している場合にのみ、UNIONを使用してください。

日付系列を生成する

再帰CTEは、日付の系列を生成する場合にも便利です。この例では、指定した1週間のすべての日を生成します。これは、カレンダーレポートを作成したり、時系列データの欠損を埋めたりする際によく使われるパターンです。

WITH RECURSIVE date_series AS (
  SELECT DATE '2024-01-01' AS day
  UNION ALL
  SELECT day + INTERVAL '1 day'
  FROM date_series
  WHERE day < DATE '2024-01-07'
)
SELECT day FROM date_series;

ある上司の部下をすべて見つける

基底ケースには、ルートだけでなく、任意の特定のノードを指定できます。ここではBob（id=2）から開始し、直属または間接的に彼へ報告する全員を見つけます。

このパターンは、権限チェック、サブツリーの集計、または1つの部署に対象を限定したダッシュボードに役立ちます。

WITH RECURSIVE subordinates AS (
  SELECT id, name
  FROM employees
  WHERE id = 2
  UNION ALL
  SELECT e.id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT name FROM subordinates;

理解度チェック

再帰CTEの仕組みについて理解度を確認します。

レッスンのまとめ

このレッスンでは、再帰CTEの仕組みを学びました。

構造：すべての再帰CTEは、UNION ALLで再帰ステップ（自己参照するSELECT）に結合された基底ケース（開始行）で構成されます。

終了：エンジンは、再帰ステップが0行を返すまで、そのステップを繰り返して結果を蓄積します。

一般的な用途：組織図やフォルダツリーをたどる、数値や日付の系列を生成する、パスを計算する、サブツリー内のすべてのノードを見つけるといった用途があります。

安全のためのポイント：必ず終了条件（深さの上限または循環ガード）を含め、パフォーマンスのためにUNIONよりもUNION ALLを優先してください。

よくある質問