【SQL】WITH句（CTE）完全ガイド｜複数CTE・UPDATE/DELETE・WITH RECURSIVE・連番/カレンダー生成・パフォーマンスまで解説

-- 基本形: WITH句で名前付き結果セットを定義
WITH cte_name AS (
    SELECT ...
    FROM   ...
    WHERE  ...
)
SELECT *
FROM   cte_name
WHERE  ...;

-- 複数CTE: カンマ区切りで連続定義
WITH
    first_cte AS (
        SELECT ...
    ),
    second_cte AS (
        SELECT ...
        FROM   first_cte    -- 前に定義したCTEを参照可能
        WHERE  ...
    )
SELECT *
FROM   second_cte;

-- サブクエリを使うと読みにくい例
SELECT e.name, e.salary,
       (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e.dept_id) AS dept_avg
FROM employees e;

-- CTEを使うとロジックが分かれて読みやすい
WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM   employees
    GROUP BY dept_id
)
SELECT
    e.name,
    e.salary,
    d.avg_salary,
    e.salary - d.avg_salary AS diff_from_avg
FROM   employees e
JOIN   dept_avg  d ON e.dept_id = d.dept_id
ORDER BY diff_from_avg DESC;

WITH
    -- Step1: 直近90日の注文を絞り込む
    recent_orders AS (
        SELECT order_id, customer_id, amount, order_date
        FROM   orders
        WHERE  order_date >= CURRENT_DATE - INTERVAL '90 days'  -- PostgreSQL
        -- WHERE order_date >= DATE_SUB(NOW(), INTERVAL 90 DAY) -- MySQL
    ),
    -- Step2: 顧客ごとの集計（Step1の結果を使う）
    customer_summary AS (
        SELECT
            customer_id,
            COUNT(*)    AS order_count,
            SUM(amount) AS total_amount
        FROM   recent_orders    -- ← Step1のCTEを参照
        GROUP BY customer_id
    ),
    -- Step3: 優良顧客に絞り込む（Step2の結果を使う）
    top_customers AS (
        SELECT customer_id, order_count, total_amount
        FROM   customer_summary  -- ← Step2のCTEを参照
        WHERE  total_amount >= 50000
    )
-- 最終的なSELECT（Step3の結果に顧客マスタをJOIN）
SELECT
    c.customer_name,
    t.order_count,
    t.total_amount
FROM   top_customers t
JOIN   customers c ON t.customer_id = c.customer_id
ORDER BY t.total_amount DESC;

-- PostgreSQL / SQL Server: WITH句をUPDATEの前に置く
WITH order_totals AS (
    SELECT customer_id, SUM(amount) AS total
    FROM   orders
    GROUP BY customer_id
)
UPDATE customers c
SET    total_purchase = ot.total
FROM   order_totals ot
WHERE  c.customer_id = ot.customer_id;

-- SQL Server も同じ構文でOK
WITH avg_by_dept AS (
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM   employees
    GROUP BY dept_id
)
UPDATE e
SET    bonus = CASE WHEN e.salary > a.avg_sal THEN e.salary * 0.1 ELSE 0 END
FROM   employees e
JOIN   avg_by_dept a ON e.dept_id = a.dept_id;

-- MySQL: WITH句はUPDATE/DELETEにも使える（MySQL 8.0+）
WITH inactive_customers AS (
    SELECT customer_id
    FROM   customers
    WHERE  last_login_date < DATE_SUB(NOW(), INTERVAL 365 DAY)
)
UPDATE customers
SET    status = 'inactive'
WHERE  customer_id IN (SELECT customer_id FROM inactive_customers);

-- Oracle: MERGE 文または インラインビューUPDATE が主流
-- CTEをインラインビューとして使うUPDATE
UPDATE (
    SELECT e.salary, a.avg_sal
    FROM   employees e
    JOIN  (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) a
           ON e.dept_id = a.dept_id
)
SET salary = salary * 1.05
WHERE salary < avg_sal;

-- Oracle 12c以降: WITH句 + MERGE が可能
WITH high_earners AS (
    SELECT employee_id FROM employees WHERE salary > 100000
)
MERGE INTO bonuses b
USING high_earners h ON (b.employee_id = h.employee_id)
WHEN MATCHED THEN UPDATE SET b.amount = b.amount * 1.2
WHEN NOT MATCHED THEN INSERT (employee_id, amount) VALUES (h.employee_id, 10000);

-- 重複行のうち最小IDを残して他を削除（PostgreSQL）
WITH duplicates AS (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM   users
)
DELETE FROM users
WHERE  id IN (SELECT id FROM duplicates WHERE rn > 1);

-- SQL Server も同様の構文
WITH ranked AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM   users
)
DELETE FROM ranked WHERE rn > 1;   -- SQL Server独自: CTEから直接DELETE

WITH RECURSIVE cte_name AS (
    -- (1) アンカーメンバー: 再帰のスタート地点（1回だけ実行）
    SELECT 初期値
    FROM   ...
    WHERE  開始条件

    UNION ALL   -- UNION ALL を使う（UNIONは重複排除コストが高い）

    -- (2) 再帰メンバー: cte_nameを参照して繰り返す（終了条件まで）
    SELECT ...
    FROM   ...
    JOIN   cte_name ON 結合条件   -- ← 自分自身を参照
    WHERE  終了条件              -- ← ここでFALSEになったら停止
)
SELECT * FROM cte_name;

-- サンプル: employees (employee_id, name, manager_id)
-- manager_id = NULL がトップ（社長）

WITH RECURSIVE org_tree AS (
    -- アンカー: トップから開始
    SELECT
        employee_id,
        name,
        manager_id,
        0     AS depth,          -- 階層の深さ
        name  AS path            -- 組織パス
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 再帰: 直属の部下を取得
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        ot.depth + 1,
        ot.path || ' > ' || e.name    -- PostgreSQL: || で文字列結合
        -- ot.path + ' > ' + e.name   -- SQL Server
        -- CONCAT(ot.path, ' > ', e.name)  -- MySQL
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT
    REPEAT('  ', depth) || name AS indented_name,  -- 階層インデント
    path,
    depth
FROM org_tree
ORDER BY path;

-- MySQL / PostgreSQL / SQL Server
WITH RECURSIVE numbers AS (
    SELECT 1 AS n         -- アンカー: 1からスタート
    UNION ALL
    SELECT n + 1          -- 再帰: +1ずつ増やす
    FROM   numbers
    WHERE  n < 100        -- 終了条件: 100まで
)
SELECT n FROM numbers;

-- Oracle: CONNECT BY LEVELが簡潔（Oracle独自）
SELECT LEVEL AS n
FROM   DUAL
CONNECT BY LEVEL <= 100;

-- 2024-01-01 〜 2024-03-31 の全日付を生成（MySQL）
WITH RECURSIVE calendar AS (
    SELECT DATE('2024-01-01') AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM   calendar
    WHERE  dt < DATE('2024-03-31')
)
SELECT dt, DAYNAME(dt) AS day_name   -- MySQL
FROM   calendar;

-- PostgreSQL
WITH RECURSIVE calendar AS (
    SELECT DATE '2024-01-01' AS dt
    UNION ALL
    SELECT dt + INTERVAL '1 day'
    FROM   calendar
    WHERE  dt < DATE '2024-03-31'
)
SELECT dt, TO_CHAR(dt, 'Day') AS day_name
FROM   calendar;

-- 活用例: カレンダー LEFT JOIN で実績がない日も0件で表示
SELECT
    c.dt,
    COALESCE(SUM(s.amount), 0) AS daily_sales
FROM   calendar c
LEFT JOIN sales s ON s.sale_date = c.dt
GROUP BY c.dt
ORDER BY c.dt;

-- parts(part_id, part_name)
-- bom(parent_id, child_id, quantity) -- 親部品→子部品

WITH RECURSIVE bom_tree AS (
    -- アンカー: 完成品（親なし）からスタート
    SELECT
        b.parent_id,
        b.child_id,
        b.quantity,
        1           AS level,
        b.quantity  AS total_qty   -- 累積数量
    FROM bom b
    WHERE b.parent_id = 1          -- 製品ID=1の部品表

    UNION ALL

    -- 再帰: さらにその子部品を展開
    SELECT
        b.parent_id,
        b.child_id,
        b.quantity,
        bt.level + 1,
        bt.total_qty * b.quantity   -- 上位階層の数量を掛け合わせ
    FROM bom b
    JOIN bom_tree bt ON b.parent_id = bt.child_id
)
SELECT
    bt.level,
    p.part_name,
    bt.total_qty
FROM bom_tree bt
JOIN parts p ON bt.child_id = p.part_id
ORDER BY bt.level, p.part_name;

-- 深さ制限（安全策として必ず入れる）
WITH RECURSIVE safe_tree AS (
    SELECT employee_id, manager_id, 1 AS depth
    FROM   employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, st.depth + 1
    FROM   employees e
    JOIN   safe_tree st ON e.manager_id = st.employee_id
    WHERE  st.depth < 20   -- ← 最大20階層まで（安全策）
)
SELECT * FROM safe_tree;

-- 訪問済みIDを配列で追跡（循環参照チェック）- PostgreSQL
WITH RECURSIVE path_check AS (
    SELECT employee_id, ARRAY[employee_id] AS visited
    FROM   employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, pc.visited || e.employee_id
    FROM   employees e
    JOIN   path_check pc ON e.manager_id = pc.employee_id
    WHERE  NOT (e.employee_id = ANY(pc.visited))  -- 訪問済みなら停止
)
SELECT * FROM path_check;

-- Oracle: CTEを強制的にマテリアライズ（一時ストアに格納）
WITH /*+ MATERIALIZE */ expensive_cte AS (
    SELECT customer_id, SUM(amount) AS total
    FROM   orders
    GROUP BY customer_id
)
SELECT c.name, e.total
FROM   customers c
JOIN   expensive_cte e ON c.customer_id = e.customer_id;

-- Oracle: インライン化を強制（マテリアライズを避ける）
WITH /*+ INLINE */ simple_cte AS (
    SELECT * FROM customers WHERE active = 1
)
SELECT * FROM simple_cte;

-- PostgreSQL 12以降: CTEのマテリアライズを明示指定
WITH MATERIALIZED expensive_cte AS (
    SELECT customer_id, SUM(amount) AS total
    FROM   orders
    GROUP BY customer_id
)
SELECT * FROM expensive_cte;

-- マテリアライズを禁止（インライン化を強制）
WITH NOT MATERIALIZED simple_cte AS (
    SELECT * FROM customers WHERE active = 1
)
SELECT * FROM simple_cte;

WITH ranked_employees AS (
    SELECT
        employee_id,
        name,
        dept_id,
        salary,
        RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank
    FROM employees
)
SELECT employee_id, name, dept_id, salary, dept_rank
FROM   ranked_employees
WHERE  dept_rank <= 3   -- 各部門の上位3名
ORDER BY dept_id, dept_rank;

WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m') AS month,   -- MySQL
        SUM(amount)                      AS total
    FROM   orders
    GROUP BY month
),
with_prev AS (
    SELECT
        month,
        total,
        LAG(total) OVER (ORDER BY month) AS prev_total
    FROM   monthly_sales
)
SELECT
    month,
    total,
    prev_total,
    total - prev_total                              AS diff,
    ROUND(100.0 * (total - prev_total) / prev_total, 1) AS growth_pct
FROM   with_prev
WHERE  prev_total IS NOT NULL
ORDER BY month;

WITH
    -- Step1: 対象データを絞り込む
    target_records AS (
        SELECT * FROM customers WHERE created_at >= '2024-01-01'
    ),
    -- Step2: 各種バリデーション
    validation_results AS (
        SELECT
            customer_id,
            name,
            email,
            phone,
            CASE WHEN email IS NULL OR email = '' THEN 1 ELSE 0 END AS no_email,
            CASE WHEN phone IS NULL OR phone = '' THEN 1 ELSE 0 END AS no_phone,
            CASE WHEN LENGTH(name) < 2              THEN 1 ELSE 0 END AS short_name
        FROM target_records
    ),
    -- Step3: 1件以上のエラーがある行だけ
    error_records AS (
        SELECT * FROM validation_results
        WHERE no_email + no_phone + short_name > 0
    )
SELECT
    customer_id,
    name,
    CASE WHEN no_email  = 1 THEN 'メール未登録 ' ELSE '' END ||
    CASE WHEN no_phone  = 1 THEN '電話未登録 '   ELSE '' END ||
    CASE WHEN short_name= 1 THEN '名前が短すぎ ' ELSE '' END AS error_detail
FROM error_records
ORDER BY customer_id;