-- CTEの基本構文
WITH department_stats AS (
    SELECT
        department_id,
        COUNT(*) AS emp_count,
        AVG(salary) AS avg_salary,
        MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
)
SELECT
    d.name AS department,
    ds.emp_count,
    ds.avg_salary,
    ds.max_salary
FROM department_stats ds
    INNER JOIN departments d ON d.id = ds.department_id
WHERE ds.avg_salary > 500000
ORDER BY ds.avg_salary DESC;
 
-- 複数CTEの連鎖
WITH
active_employees AS (
    SELECT * FROM employees WHERE status = 'active'
),
dept_summary AS (
    SELECT
        department_id,
        COUNT(*) AS cnt,
        AVG(salary) AS avg_sal
    FROM active_employees
    GROUP BY department_id
),
company_avg AS (
    SELECT AVG(avg_sal) AS overall_avg FROM dept_summary
)
SELECT
    d.name,
    ds.cnt,
    ds.avg_sal,
    ca.overall_avg,
    ds.avg_sal - ca.overall_avg AS diff
FROM dept_summary ds
    CROSS JOIN company_avg ca
    INNER JOIN departments d ON d.id = ds.department_id
ORDER BY diff DESC;

-- PostgreSQL 12+: 明示的な実体化制御
 
-- (1) 明示的に実体化（複数回参照するCTEに有効）
WITH MATERIALIZED heavy_computation AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent,
        COUNT(DISTINCT product_id) AS unique_products,
        AVG(amount) AS avg_order
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
    GROUP BY customer_id
)
SELECT hc.*, c.name, c.email
FROM heavy_computation hc
    JOIN customers c ON c.id = hc.customer_id
WHERE hc.total_spent > 100000
UNION ALL
SELECT hc.*, c.name, c.email
FROM heavy_computation hc  -- 2回目の参照でも再計算不要
    JOIN customers c ON c.id = hc.customer_id
WHERE hc.unique_products > 50;
 
-- (2) インライン展開を強制（WHERE条件のプッシュダウンを期待）
WITH NOT MATERIALIZED filtered_orders AS (
    SELECT * FROM orders WHERE status = 'completed'
)
SELECT * FROM filtered_orders
WHERE customer_id = 42;
-- → WHERE status = 'completed' AND customer_id = 42 として
--   customer_idのインデックスが活用される
 
-- (3) 実行計画の確認方法
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
WITH department_stats AS (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
)
SELECT * FROM department_stats WHERE avg_sal > 500000;

-- 例: 古い注文をアーカイブしつつ削除し、削除件数をログに記録
WITH deleted_orders AS (
    DELETE FROM orders
    WHERE order_date < CURRENT_DATE - INTERVAL '5 years'
    RETURNING *
),
archived AS (
    INSERT INTO orders_archive
    SELECT * FROM deleted_orders
    RETURNING order_id
)
SELECT COUNT(*) AS archived_count FROM archived;
 
-- 例: UPSERTの結果をCTEで活用
WITH upserted AS (
    INSERT INTO product_inventory (product_id, quantity)
    VALUES (101, 50)
    ON CONFLICT (product_id)
    DO UPDATE SET quantity = product_inventory.quantity + EXCLUDED.quantity
    RETURNING product_id, quantity
)
SELECT p.name, u.quantity
FROM upserted u JOIN products p ON p.id = u.product_id;

-- 再帰CTEの構造
WITH RECURSIVE cte_name AS (
    -- ベースケース（非再帰項）: 初期行を生成
    SELECT initial_columns
    FROM initial_table
    WHERE initial_condition
 
    UNION ALL  -- または UNION（重複排除）
 
    -- 再帰ケース（再帰項）: 前回の結果を使って次の行を生成
    SELECT next_columns
    FROM cte_name  -- 自己参照
        JOIN other_table ON ...
    WHERE termination_condition  -- 終了条件
)
SELECT * FROM cte_name;

-- 1から5までの連番生成で内部動作を追跡
WITH RECURSIVE nums AS (
    SELECT 1 AS n        -- ベースケース
    UNION ALL
    SELECT n + 1         -- 再帰ケース
    FROM nums
    WHERE n < 5          -- 終了条件
)
SELECT n FROM nums;

-- UNION ALL: 重複を許可（高速、一般的な選択）
WITH RECURSIVE tree AS (
    SELECT id, parent_id, name FROM nodes WHERE id = 1
    UNION ALL
    SELECT n.id, n.parent_id, n.name
    FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;
 
-- UNION: 重複を排除（循環グラフで自然な終了条件になる）
-- ただし行全体の比較コストが高い
WITH RECURSIVE reachable AS (
    SELECT node_id FROM edges WHERE source = 'A'
    UNION  -- 既に到達済みのノードは追加しない → 自然に終了
    SELECT e.node_id
    FROM edges e JOIN reachable r ON e.source = r.node_id
)
SELECT * FROM reachable;

-- 組織ツリーの探索
CREATE TABLE org_chart (
    id         INTEGER PRIMARY KEY,
    name       VARCHAR(100),
    manager_id INTEGER REFERENCES org_chart(id),
    title      VARCHAR(100)
);
 
-- サンプルデータ
INSERT INTO org_chart VALUES
    (1, '田中太郎', NULL, 'CEO'),
    (2, '鈴木花子', 1, 'CTO'),
    (3, '佐藤次郎', 1, 'CFO'),
    (4, '高橋美咲', 2, 'VP Engineering'),
    (5, '伊藤健一', 2, 'VP Product'),
    (6, '渡辺真理', 4, 'Sr. Engineer'),
    (7, '山本大輔', 4, 'Sr. Engineer'),
    (8, '中村優子', 6, 'Engineer');
 
-- 特定の社員から上位の全管理職を取得（ボトムアップ）
WITH RECURSIVE management_chain AS (
    -- ベースケース: 起点の社員
    SELECT id, name, manager_id, title, 0 AS depth
    FROM org_chart
    WHERE id = 42
 
    UNION ALL
 
    -- 再帰ケース: 上司を辿る
    SELECT o.id, o.name, o.manager_id, o.title, mc.depth + 1
    FROM org_chart o
        INNER JOIN management_chain mc ON o.id = mc.manager_id
)
SELECT
    REPEAT('  ', depth) || name AS hierarchy,
    title,
    depth
FROM management_chain
ORDER BY depth;
 
-- 部門長から配下の全社員を取得（トップダウン）
WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id, title, 0 AS depth,
           ARRAY[name] AS path
    FROM org_chart
    WHERE id = 1  -- CEO
 
    UNION ALL
 
    SELECT o.id, o.name, o.manager_id, o.title, s.depth + 1,
           s.path || o.name
    FROM org_chart o
        INNER JOIN subordinates s ON o.manager_id = s.id
    WHERE s.depth < 10  -- 無限再帰防止
)
SELECT
    REPEAT('  ', depth) || name AS tree,
    title,
    array_to_string(path, ' > ') AS full_path
FROM subordinates
ORDER BY path;

-- カテゴリの階層構造
CREATE TABLE categories (
    id        INTEGER PRIMARY KEY,
    name      VARCHAR(100),
    parent_id INTEGER REFERENCES categories(id)
);
 
INSERT INTO categories VALUES
    (1, '家電', NULL),
    (2, 'パソコン', 1),
    (3, 'ノートPC', 2),
    (4, '13インチ', 3),
    (5, '15インチ', 3),
    (6, 'デスクトップPC', 2),
    (7, 'スマートフォン', 1),
    (8, 'iPhone', 7),
    (9, 'Android', 7);
 
-- パンくずリストの生成（ボトムアップ）
WITH RECURSIVE breadcrumb AS (
    SELECT id, name, parent_id, name AS path, 0 AS depth
    FROM categories
    WHERE id = 4  -- 現在のカテゴリ: 13インチ
 
    UNION ALL
 
    SELECT c.id, c.name, c.parent_id,
           c.name || ' > ' || b.path,
           b.depth + 1
    FROM categories c
        INNER JOIN breadcrumb b ON c.id = b.parent_id
)
SELECT path
FROM breadcrumb
WHERE parent_id IS NULL;  -- ルートまで辿った結果
-- → "家電 > パソコン > ノートPC > 13インチ"
 
-- 全カテゴリのパンくずリストを一括生成（トップダウン）
WITH RECURSIVE category_tree AS (
    -- ルートカテゴリ
    SELECT id, name, parent_id,
           name::TEXT AS breadcrumb,
           0 AS depth,
           ARRAY[id] AS id_path
    FROM categories
    WHERE parent_id IS NULL
 
    UNION ALL
 
    -- 子カテゴリ
    SELECT c.id, c.name, c.parent_id,
           ct.breadcrumb || ' > ' || c.name,
           ct.depth + 1,
           ct.id_path || c.id
    FROM categories c
        JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT
    REPEAT('  ', depth) || name AS tree_view,
    breadcrumb,
    depth
FROM category_tree
ORDER BY id_path;
 
-- カテゴリとその全子孫の商品数を集計
WITH RECURSIVE category_descendants AS (
    SELECT id, id AS root_id FROM categories
    UNION ALL
    SELECT c.id, cd.root_id
    FROM categories c
        JOIN category_descendants cd ON c.parent_id = cd.id
)
SELECT
    cat.name,
    COUNT(DISTINCT p.id) AS product_count
FROM category_descendants cd
    JOIN categories cat ON cat.id = cd.root_id
    LEFT JOIN products p ON p.category_id = cd.id
GROUP BY cat.id, cat.name
ORDER BY product_count DESC;

-- 1〜100の連番を生成
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT n FROM numbers;
 
-- 日付シーケンスの生成（カレンダー）
WITH RECURSIVE calendar AS (
    SELECT DATE '2024-01-01' AS dt
    UNION ALL
    SELECT dt + INTERVAL '1 day'
    FROM calendar
    WHERE dt < '2024-12-31'
)
SELECT
    dt,
    EXTRACT(DOW FROM dt) AS day_of_week,
    TO_CHAR(dt, 'YYYY-MM') AS month
FROM calendar;
 
-- ※ PostgreSQLではgenerate_series()の方が効率的
SELECT generate_series('2024-01-01'::DATE, '2024-12-31'::DATE, '1 day') AS dt;
 
-- 実用例: 日付ごとの売上を0埋めで表示（欠損日も含める）
WITH RECURSIVE date_range AS (
    SELECT DATE '2024-01-01' AS dt
    UNION ALL
    SELECT dt + INTERVAL '1 day' FROM date_range WHERE dt < '2024-01-31'
)
SELECT
    dr.dt,
    COALESCE(SUM(o.amount), 0) AS daily_revenue,
    COUNT(o.id) AS order_count
FROM date_range dr
    LEFT JOIN orders o ON o.order_date = dr.dt
GROUP BY dr.dt
ORDER BY dr.dt;
 
-- 応用: 時間帯別スロット生成（予約システム）
WITH RECURSIVE time_slots AS (
    SELECT TIME '09:00' AS slot_start, TIME '09:30' AS slot_end
    UNION ALL
    SELECT
        slot_start + INTERVAL '30 minutes',
        slot_end + INTERVAL '30 minutes'
    FROM time_slots
    WHERE slot_start < TIME '17:00'
)
SELECT
    ts.slot_start,
    ts.slot_end,
    CASE WHEN r.id IS NOT NULL THEN '予約済' ELSE '空き' END AS status
FROM time_slots ts
    LEFT JOIN reservations r
        ON r.start_time <= ts.slot_start AND r.end_time > ts.slot_start
ORDER BY ts.slot_start;

-- グラフ構造（路線図）
CREATE TABLE routes (
    from_station VARCHAR(50),
    to_station   VARCHAR(50),
    distance     INTEGER,
    line_name    VARCHAR(50)
);
 
INSERT INTO routes VALUES
    ('東京', '品川', 6, '東海道線'),
    ('品川', '横浜', 22, '東海道線'),
    ('東京', '上野', 3, '山手線'),
    ('上野', '大宮', 26, '京浜東北線'),
    ('東京', '新宿', 10, '中央線'),
    ('新宿', '池袋', 5, '山手線'),
    ('池袋', '大宮', 30, '埼京線');
 
-- 東京から各駅への最短経路
WITH RECURSIVE shortest_path AS (
    SELECT
        from_station,
        to_station,
        distance,
        ARRAY[from_station, to_station] AS path,
        ARRAY[line_name] AS lines,
        1 AS hops
    FROM routes
    WHERE from_station = '東京'
 
    UNION ALL
 
    SELECT
        sp.from_station,
        r.to_station,
        sp.distance + r.distance,
        sp.path || r.to_station,
        sp.lines || r.line_name,
        sp.hops + 1
    FROM shortest_path sp
        INNER JOIN routes r ON sp.to_station = r.from_station
    WHERE NOT r.to_station = ANY(sp.path)  -- 循環防止
      AND sp.hops < 10                     -- 深さ制限
)
SELECT DISTINCT ON (to_station)
    to_station,
    distance,
    array_to_string(path, ' → ') AS route,
    array_to_string(lines, ', ') AS via_lines,
    hops
FROM shortest_path
ORDER BY to_station, distance;

-- 部品表テーブル
CREATE TABLE bom (
    parent_part_id  INTEGER,
    child_part_id   INTEGER,
    quantity        DECIMAL(10,2),
    PRIMARY KEY (parent_part_id, child_part_id)
);
 
CREATE TABLE parts (
    id    INTEGER PRIMARY KEY,
    name  VARCHAR(100),
    unit_cost DECIMAL(10,2)
);
 
INSERT INTO parts VALUES
    (1, '自転車', 0),
    (2, 'フレーム', 15000),
    (3, '前輪', 5000),
    (4, '後輪', 5000),
    (5, 'タイヤ', 2000),
    (6, 'リム', 1500),
    (7, 'スポーク', 50),
    (8, 'ハブ', 800);
 
INSERT INTO bom VALUES
    (1, 2, 1),    -- 自転車 = フレーム x1
    (1, 3, 1),    -- 自転車 = 前輪 x1
    (1, 4, 1),    -- 自転車 = 後輪 x1
    (3, 5, 1),    -- 前輪 = タイヤ x1
    (3, 6, 1),    -- 前輪 = リム x1
    (3, 7, 36),   -- 前輪 = スポーク x36
    (3, 8, 1),    -- 前輪 = ハブ x1
    (4, 5, 1),    -- 後輪 = タイヤ x1
    (4, 6, 1),    -- 後輪 = リム x1
    (4, 7, 36),   -- 後輪 = スポーク x36
    (4, 8, 1);    -- 後輪 = ハブ x1
 
-- BOM展開: 自転車に必要な全部品と数量・コスト
WITH RECURSIVE bom_explosion AS (
    -- ベースケース: トップレベル製品
    SELECT
        b.parent_part_id,
        b.child_part_id,
        b.quantity,
        b.quantity AS total_quantity,
        1 AS level,
        ARRAY[b.parent_part_id, b.child_part_id] AS path
    FROM bom b
    WHERE b.parent_part_id = 1
 
    UNION ALL
 
    -- 再帰ケース: 子部品をさらに展開
    SELECT
        b.parent_part_id,
        b.child_part_id,
        b.quantity,
        be.total_quantity * b.quantity,  -- 累積数量
        be.level + 1,
        be.path || b.child_part_id
    FROM bom b
        JOIN bom_explosion be ON b.parent_part_id = be.child_part_id
    WHERE be.level < 10
)
SELECT
    REPEAT('  ', be.level - 1) || p.name AS part_tree,
    be.total_quantity,
    p.unit_cost,
    be.total_quantity * p.unit_cost AS total_cost,
    be.level
FROM bom_explosion be
    JOIN parts p ON p.id = be.child_part_id
ORDER BY be.path;

-- カンマ区切り文字列を行に分割（再帰CTEによる実装）
WITH RECURSIVE split_string AS (
    SELECT
        1 AS idx,
        CASE
            WHEN POSITION(',' IN 'apple,banana,cherry,date') > 0
            THEN LEFT('apple,banana,cherry,date', POSITION(',' IN 'apple,banana,cherry,date') - 1)
            ELSE 'apple,banana,cherry,date'
        END AS token,
        CASE
            WHEN POSITION(',' IN 'apple,banana,cherry,date') > 0
            THEN SUBSTRING('apple,banana,cherry,date' FROM POSITION(',' IN 'apple,banana,cherry,date') + 1)
            ELSE ''
        END AS remainder
 
    UNION ALL
 
    SELECT
        idx + 1,
        CASE
            WHEN POSITION(',' IN remainder) > 0
            THEN LEFT(remainder, POSITION(',' IN remainder) - 1)
            ELSE remainder
        END,
        CASE
            WHEN POSITION(',' IN remainder) > 0
            THEN SUBSTRING(remainder FROM POSITION(',' IN remainder) + 1)
            ELSE ''
        END
    FROM split_string
    WHERE remainder <> ''
)
SELECT idx, token FROM split_string;
-- 結果:
-- idx | token
-- ----+--------
--   1 | apple
--   2 | banana
--   3 | cherry
--   4 | date
 
-- ※ PostgreSQL/MySQL 8.0+ ではstring_to_table()やregexp_split_to_table()を使う方が効率的
SELECT unnest(string_to_array('apple,banana,cherry,date', ',')) AS token;

-- フィボナッチ数列の生成
WITH RECURSIVE fibonacci AS (
    SELECT 1 AS n, 1::BIGINT AS fib, 0::BIGINT AS prev_fib
 
    UNION ALL
 
    SELECT
        n + 1,
        fib + prev_fib,  -- F(n) = F(n-1) + F(n-2)
        fib
    FROM fibonacci
    WHERE n < 50
)
SELECT n, fib FROM fibonacci;
 
-- 階乗の計算
WITH RECURSIVE factorial AS (
    SELECT 1 AS n, 1::BIGINT AS fact
    UNION ALL
    SELECT n + 1, (n + 1) * fact
    FROM factorial
    WHERE n < 20
)
SELECT n, fact FROM factorial;
 
-- 複利計算（年利5%、30年）
WITH RECURSIVE compound_interest AS (
    SELECT
        0 AS year,
        1000000.00::DECIMAL(15,2) AS principal,
        0.00::DECIMAL(15,2) AS interest_earned
    UNION ALL
    SELECT
        year + 1,
        (principal * 1.05)::DECIMAL(15,2),
        (principal * 1.05 - 1000000)::DECIMAL(15,2)
    FROM compound_interest
    WHERE year < 30
)
SELECT year, principal, interest_earned
FROM compound_interest;

-- PostgreSQL: WITH RECURSIVE 必須
WITH RECURSIVE tree AS (
    SELECT id, parent_id, name, 0 AS depth FROM nodes WHERE parent_id IS NULL
    UNION ALL
    SELECT n.id, n.parent_id, n.name, t.depth + 1
    FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;
 
-- SQL Server: RECURSIVE キーワード不要、MAXRECURSION で制御
WITH tree AS (
    SELECT id, parent_id, name, 0 AS depth FROM nodes WHERE parent_id IS NULL
    UNION ALL
    SELECT n.id, n.parent_id, n.name, t.depth + 1
    FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree
OPTION (MAXRECURSION 200);  -- デフォルト100を引き上げ
 
-- MySQL 8.0: WITH RECURSIVE 必須、最大深度はシステム変数
SET SESSION cte_max_recursion_depth = 5000;
WITH RECURSIVE tree AS (
    SELECT id, parent_id, name, 0 AS depth FROM nodes WHERE parent_id IS NULL
    UNION ALL
    SELECT n.id, n.parent_id, n.name, t.depth + 1
    FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;
 
-- Oracle: RECURSIVE不要、CONNECT BY も使える（レガシー）
-- CTE方式（推奨）
WITH tree (id, parent_id, name, depth) AS (
    SELECT id, parent_id, name, 0 FROM nodes WHERE parent_id IS NULL
    UNION ALL
    SELECT n.id, n.parent_id, n.name, t.depth + 1
    FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;
 
-- CONNECT BY方式（Oracle固有、レガシー）
SELECT
    id, parent_id, name,
    LEVEL - 1 AS depth,
    SYS_CONNECT_BY_PATH(name, '/') AS path
FROM nodes
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id
ORDER SIBLINGS BY name;

-- CYCLE句: 循環検出を宣言的に記述
WITH RECURSIVE graph_search AS (
    SELECT id, linked_id, name FROM graph WHERE id = 1
    UNION ALL
    SELECT g.id, g.linked_id, g.name
    FROM graph g JOIN graph_search gs ON g.id = gs.linked_id
)
CYCLE id SET is_cycle USING path  -- 循環検出
SELECT * FROM graph_search WHERE NOT is_cycle;
 
-- SEARCH句: 探索順序の制御
-- 深さ優先探索（DFS）
WITH RECURSIVE tree AS (
    SELECT id, parent_id, name FROM nodes WHERE parent_id IS NULL
    UNION ALL
    SELECT n.id, n.parent_id, n.name
    FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SEARCH DEPTH FIRST BY id SET ordercol
SELECT * FROM tree ORDER BY ordercol;
 
-- 幅優先探索（BFS）
WITH RECURSIVE tree AS (
    SELECT id, parent_id, name FROM nodes WHERE parent_id IS NULL
    UNION ALL
    SELECT n.id, n.parent_id, n.name
    FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SEARCH BREADTH FIRST BY id SET ordercol
SELECT * FROM tree ORDER BY ordercol;

-- 階層データの場合: parent_id と id のペアにインデックス
CREATE INDEX idx_org_chart_manager ON org_chart (manager_id);
CREATE INDEX idx_categories_parent ON categories (parent_id);
CREATE INDEX idx_bom_parent ON bom (parent_part_id);
 
-- グラフデータの場合: 開始ノードと終了ノードの両方
CREATE INDEX idx_routes_from ON routes (from_station);
CREATE INDEX idx_routes_to ON routes (to_station);
 
-- 実行計画の確認
EXPLAIN (ANALYZE, BUFFERS)
WITH RECURSIVE subordinates AS (
    SELECT id, manager_id FROM org_chart WHERE id = 1
    UNION ALL
    SELECT o.id, o.manager_id
    FROM org_chart o JOIN subordinates s ON o.manager_id = s.id
)
SELECT * FROM subordinates;
-- CTE Scan の下に Index Scan が出ていることを確認

-- テクニック1: 再帰深度の動的制御
-- 必要な深さだけ探索して早期終了
WITH RECURSIVE tree AS (
    SELECT id, parent_id, name, 0 AS depth FROM nodes WHERE id = 1
    UNION ALL
    SELECT n.id, n.parent_id, n.name, t.depth + 1
    FROM nodes n JOIN tree t ON n.parent_id = t.id
    WHERE t.depth < 3  -- 3階層までに限定
)
SELECT * FROM tree;
 
-- テクニック2: 必要なカラムだけを持ち回る
-- NG: 全カラムを再帰で持ち回る（メモリ消費大）
WITH RECURSIVE tree AS (
    SELECT * FROM large_table WHERE id = 1  -- 全カラム
    UNION ALL
    SELECT lt.* FROM large_table lt JOIN tree t ON lt.parent_id = t.id
)
SELECT * FROM tree;
 
-- OK: IDだけ再帰で辿り、最後にJOINして全カラム取得
WITH RECURSIVE tree_ids AS (
    SELECT id FROM large_table WHERE id = 1  -- IDのみ
    UNION ALL
    SELECT lt.id FROM large_table lt JOIN tree_ids t ON lt.parent_id = t.id
)
SELECT lt.* FROM large_table lt JOIN tree_ids ti ON lt.id = ti.id;
 
-- テクニック3: Closure Table パターン（再帰CTEの代替）
-- 頻繁に階層クエリを実行する場合、事前にClosure Tableを構築
CREATE TABLE category_closure (
    ancestor_id   INTEGER,
    descendant_id INTEGER,
    depth         INTEGER,
    PRIMARY KEY (ancestor_id, descendant_id)
);
 
-- Closure Tableの構築（1回だけ実行）
WITH RECURSIVE tree AS (
    SELECT id AS ancestor_id, id AS descendant_id, 0 AS depth
    FROM categories
    UNION ALL
    SELECT t.ancestor_id, c.id, t.depth + 1
    FROM tree t JOIN categories c ON c.parent_id = t.descendant_id
)
INSERT INTO category_closure
SELECT * FROM tree;
 
-- Closure Tableを使えば再帰なしで子孫を取得
SELECT c.* FROM categories c
    JOIN category_closure cc ON c.id = cc.descendant_id
WHERE cc.ancestor_id = 1 AND cc.depth <= 3;

-- ベースケースが該当なし → 再帰も実行されず、空の結果
WITH RECURSIVE tree AS (
    SELECT id, parent_id FROM nodes WHERE id = 99999  -- 存在しないID
    UNION ALL
    SELECT n.id, n.parent_id FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;
-- → 0行（エラーにはならない）
 
-- 対策: COALESCE やデフォルト値で空結果に対応
SELECT COALESCE(
    (SELECT COUNT(*) FROM tree WHERE depth <= 5),
    0
) AS descendant_count;

-- NG: ベースケースと再帰ケースでデータ型が異なる
WITH RECURSIVE path AS (
    SELECT id, name FROM nodes WHERE id = 1  -- name: VARCHAR(100)
    UNION ALL
    SELECT n.id, p.name || ' > ' || n.name   -- 結合で長さが不定
    FROM nodes n JOIN path p ON n.parent_id = p.id
)
SELECT * FROM path;
-- → 深い階層で文字列がVARCHAR(100)を超える可能性
 
-- OK: 明示的にCAST
WITH RECURSIVE path AS (
    SELECT id, name::TEXT AS path_string FROM nodes WHERE id = 1
    UNION ALL
    SELECT n.id, (p.path_string || ' > ' || n.name)::TEXT
    FROM nodes n JOIN path p ON n.parent_id = p.id
)
SELECT * FROM path;

-- parent_id が NULL のノードはルート
-- しかし、NULLの比較はIS NULLを使う必要がある
-- NG: NULL = NULL は FALSE なので結合に失敗
SELECT * FROM nodes n1 JOIN nodes n2 ON n1.parent_id = n2.id;
-- parent_id が NULL の行は結合されない（正しい動作だが注意）
 
-- ルートノードの検出
WITH RECURSIVE tree AS (
    SELECT id, parent_id, name, 0 AS depth
    FROM nodes
    WHERE parent_id IS NULL  -- ルートの条件
 
    UNION ALL
 
    SELECT n.id, n.parent_id, n.name, t.depth + 1
    FROM nodes n JOIN tree t ON n.parent_id = t.id
    WHERE t.depth < 100
)
SELECT * FROM tree;
 
-- 孤児ノード（親が存在しないが parent_id が NULL でもない）の検出
SELECT n.*
FROM nodes n
    LEFT JOIN nodes parent ON n.parent_id = parent.id
WHERE n.parent_id IS NOT NULL AND parent.id IS NULL;

-- 組織に複数のルート（例: グループ会社の各CEO）がある場合
WITH RECURSIVE full_tree AS (
    -- 全ルートから開始
    SELECT id, name, manager_id, 0 AS depth, id AS root_id
    FROM org_chart
    WHERE manager_id IS NULL
 
    UNION ALL
 
    SELECT o.id, o.name, o.manager_id, ft.depth + 1, ft.root_id
    FROM org_chart o JOIN full_tree ft ON o.manager_id = ft.id
)
SELECT
    root_id,
    REPEAT('  ', depth) || name AS tree,
    depth
FROM full_tree
ORDER BY root_id, depth, name;

-- NG: 無限再帰（DBがハングする）
WITH RECURSIVE infinite AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM infinite  -- 終了条件なし！
)
SELECT * FROM infinite;
 
-- OK: 終了条件を明示
WITH RECURSIVE safe AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM safe WHERE n < 1000  -- 明示的な上限
)
SELECT * FROM safe;
 
-- 安全策: PostgreSQLではstatement_timeoutを設定
SET statement_timeout = '5s';
 
-- MySQL: 最大再帰深度を設定
SET SESSION cte_max_recursion_depth = 5000;
 
-- SQL Server: OPTION (MAXRECURSION N) を指定
-- OPTION (MAXRECURSION 0) で無制限（危険、本番では避ける）

-- NG（PostgreSQL 11以前のデフォルト動作）: CTEが必ず実体化される
WITH expensive_cte AS (
    SELECT * FROM huge_table WHERE category = 'A'
)
SELECT * FROM expensive_cte WHERE id = 42;
-- → huge_tableのcategory='A'全行を実体化してからid=42をフィルタ
 
-- OK（PostgreSQL 12+）: NOT MATERIALIZEDでインライン展開を強制
WITH expensive_cte AS NOT MATERIALIZED (
    SELECT * FROM huge_table WHERE category = 'A'
)
SELECT * FROM expensive_cte WHERE id = 42;
-- → WHERE category = 'A' AND id = 42 として最適化される

-- NG: 再帰項でSUM/COUNT/AVG等は使えない（SQLエラー）
WITH RECURSIVE running_total AS (
    SELECT id, amount, amount AS total FROM orders WHERE id = 1
    UNION ALL
    SELECT o.id, o.amount, SUM(o.amount) OVER ()  -- エラー!
    FROM orders o JOIN running_total rt ON o.id = rt.id + 1
)
SELECT * FROM running_total;
 
-- OK: 集約はCTEの外で行う
WITH RECURSIVE order_chain AS (
    SELECT id, amount FROM orders WHERE id = 1
    UNION ALL
    SELECT o.id, o.amount
    FROM orders o JOIN order_chain oc ON o.id = oc.id + 1
    WHERE o.id <= 100
)
SELECT id, amount, SUM(amount) OVER (ORDER BY id) AS running_total
FROM order_chain;

-- NG: 再帰CTEで単純な連番生成（PostgreSQL）
WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10000
)
SELECT n FROM nums;
 
-- OK: generate_series()を使う（遥かに高速）
SELECT generate_series(1, 10000) AS n;
 
-- NG: 再帰CTEでカレンダー生成（PostgreSQL）
WITH RECURSIVE dates AS (
    SELECT CURRENT_DATE AS dt
    UNION ALL
    SELECT dt + 1 FROM dates WHERE dt < CURRENT_DATE + 365
)
SELECT dt FROM dates;
 
-- OK: generate_series()を使う
SELECT generate_series(
    CURRENT_DATE,
    CURRENT_DATE + INTERVAL '365 days',
    INTERVAL '1 day'
)::DATE AS dt;

CREATE TABLE employees_ex (
    id         INTEGER PRIMARY KEY,
    name       VARCHAR(100),
    manager_id INTEGER,
    salary     INTEGER
);
 
INSERT INTO employees_ex VALUES
    (1, 'Alice', NULL, 1000000),
    (2, 'Bob', 1, 800000),
    (3, 'Charlie', 1, 750000),
    (4, 'David', 2, 600000),
    (5, 'Eve', 2, 650000),
    (6, 'Frank', 3, 500000),
    (7, 'Grace', 4, 450000),
    (8, 'Heidi', 4, 480000);

CREATE TABLE filesystem (
    id        INTEGER PRIMARY KEY,
    name      VARCHAR(255),
    parent_id INTEGER,
    is_dir    BOOLEAN,
    size_bytes BIGINT  -- ディレクトリは0、ファイルは実サイズ
);
 
INSERT INTO filesystem VALUES
    (1, '/', NULL, true, 0),
    (2, 'home', 1, true, 0),
    (3, 'usr', 1, true, 0),
    (4, 'alice', 2, true, 0),
    (5, 'bob', 2, true, 0),
    (6, 'readme.txt', 4, false, 1024),
    (7, 'photo.jpg', 4, false, 5242880),
    (8, 'notes.md', 5, false, 2048),
    (9, 'bin', 3, true, 0),
    (10, 'python3', 9, false, 15728640);

CREATE TABLE city_connections (
    city_from VARCHAR(50),
    city_to   VARCHAR(50),
    cost      INTEGER
);
 
INSERT INTO city_connections VALUES
    ('東京', '大阪', 13000),
    ('東京', '名古屋', 10000),
    ('名古屋', '大阪', 5000),
    ('大阪', '福岡', 15000),
    ('東京', '仙台', 10000),
    ('仙台', '札幌', 16000),
    ('名古屋', '福岡', 18000);