【PostgreSQL】再帰CTEで階層データを扱う

PostgreSQL
2022.12.27

こんにちは、かつコーチです。

組織図、カテゴリのツリー構造、コメントの返信ネストなど、階層データはWebアプリの定番テーマです。

前回はCTEの基本を扱いましたが、今回はCTE自身を参照して繰り返し処理を行う再帰CTEを掘り下げます。

CTEの基本文法・WITH句の書き方は前回記事を前提に進めるので、未読の方はそちらを先にどうぞ。

再帰CTEの構文

基本形（アンカー部と再帰部）

再帰CTEはUNION（またはUNION ALL）を挟んで、初期条件を返す「アンカー部」と、自分自身を参照して繰り返す「再帰部」の2つで構成します。

WITH RECURSIVE category_tree AS (
    -- アンカー部：ルート（親を持たないカテゴリ）
    SELECT category_id, category_name, parent_id, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 再帰部：自分自身（category_tree）を参照して子を辿る
    SELECT c.category_id, c.category_name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree
ORDER BY depth, category_id;

RECURSIVEキーワードを付け忘れると単なる非再帰CTEとして扱われ、category_treeを再帰部で参照した瞬間に構文エラーになります。

再帰部の結合条件（c.parent_id = ct.category_id）が、階層をどう辿るかを決める心臓部です。

パス表示・深さ制限を組み込む

実務では「何階層目か」「ルートからのパス」も一緒に持ちたいケースが多いです。

depthのほかに、array_appendでパスを配列として積み上げる方法をよく使います。

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_id,
        0 AS depth,
        ARRAY[category_id] AS path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_id,
        ct.depth + 1,
        ct.path || c.category_id
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.category_id
    WHERE ct.depth < 10
)
SELECT category_id, category_name, depth, path
FROM category_tree
ORDER BY path;

WHERE ct.depth < 10は無限ループ防止の深さ制限です。

循環参照のデータが紛れ込んだ場合の安全弁として、実務では必ず入れておくことをおすすめします。

つまずきやすいポイント・エラー対処

Before/After：循環データで無限ループに陥る

自己参照テーブルは、データ不整合で循環（AがBの親、BがAの親）が発生することがあります。

深さ制限なしの再帰CTEは、これを検知できずに無限ループしてPostgreSQLがメモリを食いつぶします。

-- ❌ Before：深さ制限もサイクル検知もない
WITH RECURSIVE category_tree AS (
    SELECT category_id, parent_id, 0 AS depth
    FROM categories
    WHERE category_id = 1

    UNION ALL

    SELECT c.category_id, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree;

-- ✅ After：CYCLE句でサイクルを検知し、実行を止める（PostgreSQL 16以降）
WITH RECURSIVE category_tree AS (
    SELECT category_id, parent_id, 0 AS depth
    FROM categories
    WHERE category_id = 1

    UNION ALL

    SELECT c.category_id, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.category_id
)
SEARCH DEPTH FIRST BY category_id SET ordercol
CYCLE category_id SET is_cycle USING path
SELECT * FROM category_tree WHERE NOT is_cycle;

実際に私が本番データで踏んだのは、CSVインポートのミスでparent_idが自分自身を指してしまったレコードが1件だけ混ざっていたケースです。

深さ制限を入れていなかったため、管理画面のカテゴリ選択セレクトボックスが表示されるまで数十秒固まる不具合になりました。

CYCLE句はPostgreSQL 16で標準SQLに準拠する形で導入された機能なので、16系を使っているなら積極的に使う価値があります。

古いバージョンでは、先ほどのpath配列にcategory_idが含まれるかをWHERE NOT (c.category_id = ANY(ct.path))のように自前でチェックする方法が定番でした。

UNION ALLとUNIONの使い分け

再帰CTEでは基本的にUNION ALLを使います。

UNION（重複排除あり）を使うと、繰り返し処理のたびに結果全体の重複チェックが走り、パフォーマンスが大きく劣化します。

重複排除が必要なケースはCYCLE句や自前のパスチェックで対応し、UNION自体は避けるのがセオリーです。

応用・一歩先の使い方

集計と組み合わせて組織のコスト集計を行う

再帰CTEはただ階層を辿るだけでなく、集計処理と組み合わせることで真価を発揮します。

各部署の直接コストに、配下の全部署のコストを合算した「累計コスト」を求める例です。

WITH RECURSIVE dept_tree AS (
    SELECT department_id, department_name, parent_id, direct_cost
    FROM departments
    WHERE parent_id IS NULL

    UNION ALL

    SELECT d.department_id, d.department_name, d.parent_id, d.direct_cost
    FROM departments d
    JOIN dept_tree dt ON d.parent_id = dt.department_id
),
dept_with_ancestors AS (
    SELECT department_id AS descendant_id, department_id AS ancestor_id
    FROM departments

    UNION ALL

    SELECT dwa.descendant_id, d.parent_id
    FROM dept_with_ancestors dwa
    JOIN departments d ON d.department_id = dwa.ancestor_id
    WHERE d.parent_id IS NOT NULL
)
SELECT
    a.ancestor_id AS department_id,
    d.department_name,
    SUM(dep.direct_cost) AS total_cost
FROM dept_with_ancestors a
JOIN departments dep ON dep.department_id = a.descendant_id
JOIN departments d ON d.department_id = a.ancestor_id
GROUP BY a.ancestor_id, d.department_name
ORDER BY department_id;

祖先・子孫の関係をdept_with_ancestorsとして展開してから集計する方法は、階層集計の定番パターンとして覚えておくと応用が利きます。

大量データでは再帰CTE自体がボトルネックになりやすいので、頻繁に読み取る階層集計はマテリアライズドビューやバッチでの事前集計も検討してください。

まとめ

この記事のポイント

  • 再帰CTEはWITH RECURSIVEでアンカー部と再帰部をUNION ALLでつなぐ
  • 深さ制限かCYCLE句（PostgreSQL 16以降）で無限ループを防ぐのは必須
  • パス配列を持たせるとルートからの経路や祖先・子孫関係の集計に応用できる
  • 頻繁にアクセスする階層集計は、再帰CTEの都度実行ではなく事前集計も検討する

次に読むべき記事

階層データの次は、集計とランキングを両立させるウィンドウ関数に進むと、応用範囲がさらに広がります。

  • CTE（WITH句）の基本：クエリを読みやすく整理する
  • ウィンドウ関数の基本：ランキングと集計を両立する（近日公開）

タグ: PostgreSQL, 上級者向け, クエリ

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