PostgreSQL再帰CTE入門！階層構造データをSQLで扱う方法を徹底解説

1. PostgreSQLの再帰CTEとは何か？

1. PostgreSQLの再帰CTEとは何か？

PostgreSQL（ポストグレスキューエル）は、世界中で使われている非常に高性能なデータベース管理システムです。その中でも「CTE（共通テーブル式）」という機能は、複雑な指示を小分けにして整理するための道具です。

そして今回の主役である「再帰CTE（さいきシーティーイー）」は、そのCTEをさらにパワーアップさせたものです。「再帰」とは、簡単に言うと「自分自身をもう一度繰り返す」という意味です。例えば、合わせ鏡の中に自分が映り、その鏡の中にまた鏡がある状態をイメージしてみてください。

この仕組みを使うと、家系図の「親、子、孫、ひ孫……」といった終わりが見えない繋がりや、会社の「社長、部長、課長、社員……」といった組織の上下関係を、一つの命令で一気に取り出すことができるようになります。通常、このようなデータは「どこまで続くか分からない」ため、普通のSQLでは何度も命令を書かなければなりませんが、再帰CTEを使えば、たった一度の命令で一番下まで辿り着くことができるのです。

2. 階層データとは？身近な例で考えよう

2. 階層データとは？身近な例で考えよう

データベースの世界で「階層データ（かいそうだーた）」とは、親と子の関係を持っているデータの集まりを指します。これを理解するために、まずはパソコンのフォルダ構成を想像してみてください。「書類」というフォルダの中に「2025年」というフォルダがあり、その中にさらに「請求書」というフォルダがある。このように、一つの項目がさらに細かい項目を含んでいる構造が階層データです。

これをデータベースの「テーブル（表）」で表現する場合、それぞれのデータに「自分の番号（ID）」と「親の番号（親ID）」を持たせます。これだけで、無限に続くツリー構造を表現できるのです。

ここでは、ある会社の組織図を例にして考えてみましょう。社長の下に部長がいて、その下にチームリーダーがいるという構造です。まずは、元のデータとなる「組織テーブル（departments）」を見てみましょう。

id | name         | parent_id
---+--------------+-----------
1  | 社長室        | NULL
2  | 営業部        | 1
3  | 開発部        | 1
4  | 営業一課      | 2
5  | 営業二課      | 2
6  | システム開発課 | 3
7  | デザイン課    | 3

この表では、「parent_id」が「親の番号」を表しています。例えば、営業部（ID:2）の親は社長室（ID:1）です。営業一課（ID:4）の親は営業部（ID:2）となっていますね。このように、データ同士が親子関係で繋がっています。

3. 再帰CTEの基本構文と仕組み

3. 再帰CTEの基本構文と仕組み

再帰CTEを書くときは、WITH RECURSIVE という決まった言葉を使います。この中身は、大きく分けて二つのパーツで構成されています。

• アンカー部（起点）： 最初にどこからスタートするかを決める部分です。例えば「社長室から始める」という指示です。
• 再帰部（繰り返し）： 「前の結果の部下を探してくる」という処理を繰り返す部分です。

この二つを UNION ALL（ユニオン・オール）という言葉で合体させるのが、再帰CTEの決まり文句です。それでは、実際に社長室から下の階層をすべて表示するSQLを見てみましょう。

WITH RECURSIVE org_chart AS (
  -- 1. アンカー部：ここがスタート地点（社長室）
  SELECT id, name, parent_id, 1 AS depth
  FROM departments
  WHERE id = 1

  UNION ALL

  -- 2. 再帰部：見つかった親に紐付く子を順番に探していく
  SELECT d.id, d.name, d.parent_id, oc.depth + 1
  FROM departments d
  INNER JOIN org_chart oc ON d.parent_id = oc.id
)
SELECT * FROM org_chart;

このSQLを実行すると、以下のような結果が得られます。階層の深さを表す「depth」という項目も追加しています。

id | name         | parent_id | depth
---+--------------+-----------+-------
1  | 社長室        | NULL      | 1
2  | 営業部        | 1         | 2
3  | 開発部        | 1         | 2
4  | 営業一課      | 2         | 3
5  | 営業二課      | 2         | 3
6  | システム開発課 | 3         | 3
7  | デザイン課    | 3         | 3

4. 実践：特定の場所から下のデータだけを取り出す

4. 実践：特定の場所から下のデータだけを取り出す

次に、すべてのデータではなく「開発部（ID:3）より下の組織だけを知りたい」という場合を考えてみましょう。再帰CTEを使えば、スタート地点を変えるだけで、特定の部分ツリーを簡単に抜き出すことができます。

これは、大規模なサイトのカテゴリー検索などでよく使われる技術です。例えば、「家電」カテゴリーを選んだら、その下にある「キッチン家電」「掃除機」「冷蔵庫」といった子カテゴリーをすべて表示するような仕組みです。

WITH RECURSIVE dev_team AS (
  -- 開発部（ID=3）を起点にする
  SELECT id, name, parent_id
  FROM departments
  WHERE id = 3

  UNION ALL

  -- 開発部の部下を次々に探す
  SELECT d.id, d.name, d.parent_id
  FROM departments d
  JOIN dev_team dt ON d.parent_id = dt.id
)
SELECT * FROM dev_team;

実行結果は以下の通りです。開発部とその配下の課だけが抽出されているのがわかります。

id | name         | parent_id
---+--------------+-----------
3  | 開発部        | 1
6  | システム開発課 | 3
7  | デザイン課    | 3

5. 経路（パス）を表示して分かりやすくする

5. 経路（パス）を表示して分かりやすくする

再帰CTEの応用編として、「自分がどこを通ってきたか」という経路を表示する方法を解説します。例えば、「社長室 ＞ 営業部 ＞ 営業一課」という風に表示されると、データの繋がりが一目でわかりますよね。

PostgreSQLでは、文字列を連結させる機能を使って、この「パス」を作ることができます。これを「パンくずリスト」のように利用することで、ユーザーにとって非常にわかりやすい画面を作ることが可能になります。

WITH RECURSIVE path_list AS (
  -- スタート地点：名前をテキスト形式で保持する
  SELECT id, name, parent_id, name::text AS full_path
  FROM departments
  WHERE parent_id IS NULL

  UNION ALL

  -- 子のデータに、親のパスを付け足していく
  SELECT d.id, d.name, d.parent_id, pl.full_path || ' > ' || d.name
  FROM departments d
  JOIN path_list pl ON d.parent_id = pl.id
)
SELECT id, full_path FROM path_list;

実行結果を見てみましょう。「full_path」という列に、組織の階層構造が分かりやすく積み上げられています。

id | full_path
---+---------------------------------
1  | 社長室
2  | 社長室 > 営業部
3  | 社長室 > 開発部
4  | 社長室 > 営業部 > 営業一課
5  | 社長室 > 営業部 > 営業二課
6  | 社長室 > 開発部 > システム開発課
7  | 社長室 > 開発部 > デザイン課

このように、再帰CTEは単にデータを並べるだけでなく、データの歴史や繋がりを加工して表現するのにも非常に適しています。プログラミング未経験の方には少し呪文のように見えるかもしれませんが、やっていることは「親を見つけて、その子を探す」という単純な作業の繰り返しなのです。

6. 注意点：無限ループに気をつけよう

6. 注意点：無限ループに気をつけよう

再帰CTEを使うときに、一つだけ注意しなければならないことがあります。それは「無限ループ」です。例えば、Aさんの親がBさんで、Bさんの親がAさんという設定になっていたらどうなるでしょうか？

データベースは永遠に「A→B→A→B……」と探し続けてしまい、パソコンがフリーズしたり、エラーが出てしまったりします。これを防ぐためには、データを作る段階で親子関係が逆転しないように気をつけるか、SQLの中で「最大何回まで繰り返すか」という制限をかけることが大切です。初心者のうちは、まずはシンプルな親子関係のデータから練習を始めるのがおすすめです。

PostgreSQLには、JSONBやウィンドウ関数といった他にも強力な機能がたくさんありますが、この再帰CTEをマスターできれば、エンジニアとしてのデータ操作能力は飛躍的に向上します。最初は難しく感じるかもしれませんが、自分で表を書いて、一段ずつ辿っていく練習をすれば必ず身につきますよ。