階層構造を扱うための再帰的CTEの例

想像してみて：君のネットショップには何千もの商品があって、ちゃんとカテゴリ、サブカテゴリ、サブサブカテゴリに分かれてる。サイト上ではキレイなドロップダウンメニューだけど、DBだと超ややこしい。たとえば「エレクトロニクス → スマートフォン → アクセサリー」みたいな枝を一発で取り出したい時、どうする？各カテゴリのネストレベルを数えたい時は？普通のJOINじゃ無理ゲー — ここで再帰の出番だ！

再帰的CTEで商品カテゴリ構造を作る

リレーショナルDBでよくある課題の一つが、階層構造の扱い。たとえば、商品カテゴリのツリー：メインカテゴリ、サブカテゴリ、さらにその下…みたいな感じ。例を挙げると：

エレクトロニクス
  └── スマートフォン
      └── アクセサリー
  └── ノートパソコン
      └── ゲーミング
  └── フォトとビデオ

この構造はネットショップのUIでは簡単だけど、DBでどう保存してどう取り出す？そこで再帰的CTEの出番！

カテゴリの元テーブル

まずはcategoriesテーブルを作るよ。ここに商品カテゴリのデータを入れる：

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,       -- カテゴリのユニークID
    category_name TEXT NOT NULL,          -- カテゴリ名
    parent_category_id INT                -- 親カテゴリ（メインカテゴリはNULL）
);

このテーブルに追加するデータ例：

INSERT INTO categories (category_name, parent_category_id) VALUES
    ('エレクトロニクス', NULL),
    ('スマートフォン', 1),
    ('アクセサリー', 2),
    ('ノートパソコン', 1),
    ('ゲーミング', 4),
    ('フォトとビデオ', 1);

ここで何が起きてるか：

• エレクトロニクス — これはメインカテゴリ（親なし、parent_category_id = NULL）。
• スマートフォンはエレクトロニクスの中。
• アクセサリーはスマートフォンの中。
• 他のカテゴリも同じ感じ。

今のcategoriesテーブルのデータ構造はこんな感じ：

再帰的CTEでカテゴリツリーを作る

今度は、カテゴリの階層とネストレベルを全部取り出したい。そこで再帰的CTEを使うよ。

WITH RECURSIVE category_tree AS (
    -- ベースクエリ：親がNULLのルートカテゴリを選ぶ
    SELECT
        category_id,
        category_name,
        parent_category_id,
        1 AS depth -- 最初のネストレベル
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    -- 再帰クエリ：各カテゴリのサブカテゴリを探す
    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1 AS depth -- ネストレベルを増やす
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)
-- 最終クエリ：CTEから結果を取り出す
SELECT
    category_id,
    category_name,
    parent_category_id,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

結果：

ここで何が起きてる？

1. まずベースクエリ（SELECT … FROM categories WHERE parent_category_id IS NULL）でメインカテゴリを選ぶ。今回はエレクトロニクスだけでdepth = 1。
2. 次に再帰クエリでINNER JOINを使ってサブカテゴリを追加、ネストレベル（depth + 1）を増やす。
3. この処理を、全てのレベルのサブカテゴリが見つかるまで繰り返す。

便利なアレンジ

基本の例は動くけど、実際のプロジェクトだともっと色々必要。たとえばパンくずリストを作りたいとか、どのカテゴリにサブカテゴリが一番多いかマネージャーに見せたいとか。いくつか実用的な改良例を見てみよう。

1. カテゴリのフルパスを追加

たとえばエレクトロニクス > スマートフォン > アクセサリーみたいに、カテゴリのフルパスを表示したい時がある。これは文字列の連結で実現できる：

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        category_name AS full_path,
        1 AS depth
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.full_path || ' > ' || c.category_name AS full_path, -- 文字列を連結
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    category_id,
    category_name,
    parent_category_id,
    full_path,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

結果：

これで各カテゴリにネストを示すフルパスが付いた！

2. サブカテゴリ数のカウント

各カテゴリにいくつサブカテゴリがあるか知りたい時は？

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        parent_category_id
    FROM categories

    UNION ALL

    SELECT
        c.category_id,
        c.parent_category_id
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    parent_category_id,
    COUNT(*) AS subcategory_count
FROM category_tree
WHERE parent_category_id IS NOT NULL
GROUP BY parent_category_id
ORDER BY parent_category_id;

結果：

このテーブルを見ると、エレクトロニクスには3つ（スマートフォン、ノートパソコン、フォトとビデオ）、スマートフォンとノートパソコンには1つずつサブカテゴリがある。

再帰的CTEを使う時の注意点とよくあるミス

無限再帰：もしデータにループ（たとえばカテゴリが自分自身を親にしてる）があると、クエリが無限ループになる。これを防ぐにはWHERE depth < Nとかリミットを使おう。

パフォーマンス最適化：再帰的CTEは大量データだと遅くなることも。parent_category_idにインデックスを貼ると速くなるよ。

UNIONとUNION ALLの間違い：再帰的CTEでは必ずUNION ALLを使おう。UNIONだとPostgreSQLが重複排除しようとして遅くなる。

この例で、再帰的CTEが階層構造の扱いにどれだけ便利かわかったよね。DBから階層を取り出すスキルは、サイトのメニュー作りや組織構造の分析、グラフ処理など色んな現場で役立つ。これでどんな課題もバッチリ対応できるはず！

1

アンケート/クイズ

CTEへのイントロダクション、レベル 27、レッスン 4

使用不可

CTEへのイントロダクション

CTEへのイントロダクション

オープン