【Oracle】WITH句（共通表式）の使い方完全ガイド｜サブクエリの可読性改善・再帰CTE・パフォーマンス制御まで解説

WITH cte_name AS (
    -- ここにサブクエリを書く
    SELECT col1, col2
    FROM   some_table
    WHERE  条件
)
SELECT *
FROM   cte_name;   -- CTEをテーブルのように参照する

SELECT dept_id, avg_salary
FROM (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM   employees
    GROUP BY dept_id
) dept_avg
WHERE avg_salary > (
    SELECT AVG(salary)
    FROM   employees
);

WITH
dept_avg AS (
    -- 部署別平均給与
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM   employees
    GROUP BY dept_id
),
company_avg AS (
    -- 全社平均給与
    SELECT AVG(salary) AS avg_salary
    FROM   employees
)
SELECT d.dept_id, d.avg_salary
FROM   dept_avg d, company_avg c
WHERE  d.avg_salary > c.avg_salary
ORDER BY d.avg_salary DESC;

WITH
-- ステップ1: 注文金額を集計する
order_summary AS (
    SELECT
        customer_id,
        COUNT(*)       AS order_count,
        SUM(amount)    AS total_amount
    FROM   orders
    WHERE  order_date >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12)
    GROUP BY customer_id
),
-- ステップ2: 顧客情報と結合してランク付け
ranked_customers AS (
    SELECT
        c.customer_name,
        o.order_count,
        o.total_amount,
        RANK() OVER (ORDER BY o.total_amount DESC) AS sales_rank
    FROM   order_summary o
    JOIN   customers c ON o.customer_id = c.customer_id
)
-- ステップ3: 上位10社だけを抽出
SELECT customer_name, order_count, total_amount, sales_rank
FROM   ranked_customers
WHERE  sales_rank <= 10;

-- 12c以降: WITH句をUPDATEでも使える
WITH obsolete_products AS (
    SELECT product_id
    FROM   products
    WHERE  last_sold_date < ADD_MONTHS(SYSDATE, -24)
      AND  stock_qty = 0
)
UPDATE products
SET    status = 'DISCONTINUED'
WHERE  product_id IN (SELECT product_id FROM obsolete_products);

WITH cte_name (col1, col2, ...) AS (
    -- アンカーメンバー（再帰の起点）
    SELECT ...
    FROM   ...
    WHERE  ...      -- 最上位データを取得

    UNION ALL

    -- 再帰メンバー（自身を参照して繰り返す）
    SELECT ...
    FROM   some_table t
    JOIN   cte_name c ON t.parent_id = c.col1
)
SELECT * FROM cte_name;

WITH org_tree (emp_id, emp_name, mgr_id, dept_name, level_num, path) AS (
    -- アンカー：最上位（manager_idがNULL）の従業員
    SELECT
        emp_id,
        emp_name,
        manager_id,
        dept_name,
        1               AS level_num,
        emp_name        AS path
    FROM   employees
    WHERE  manager_id IS NULL

    UNION ALL

    -- 再帰：一つ下の階層を結合
    SELECT
        e.emp_id,
        e.emp_name,
        e.manager_id,
        e.dept_name,
        t.level_num + 1,
        t.path || ' > ' || e.emp_name
    FROM   employees e
    JOIN   org_tree  t ON e.manager_id = t.emp_id
)
SELECT
    LPAD(' ', (level_num - 1) * 4) || emp_name AS indented_name,
    level_num,
    dept_name,
    path
FROM   org_tree
ORDER BY path;

WITH nums (n) AS (
    SELECT 1 FROM dual          -- アンカー：最初の値
    UNION ALL
    SELECT n + 1 FROM nums      -- 再帰：1ずつ増やす
    WHERE  n < 30               -- 終了条件
)
SELECT n FROM nums;

WITH org_tree (emp_id, emp_name, mgr_id, level_num) AS (
    SELECT emp_id, emp_name, manager_id, 1
    FROM   employees
    WHERE  manager_id IS NULL
    UNION ALL
    SELECT e.emp_id, e.emp_name, e.manager_id, t.level_num + 1
    FROM   employees e
    JOIN   org_tree  t ON e.manager_id = t.emp_id
)
CYCLE emp_id SET is_cycle TO '1' DEFAULT '0'   -- emp_idが循環したらis_cycle='1'
SELECT emp_id, emp_name, level_num, is_cycle
FROM   org_tree
WHERE  is_cycle = '0';    -- 循環していない行だけ取得

WITH expensive_cte AS (
    /*+ MATERIALIZE */                -- 一度だけ実行して一時領域に保存
    SELECT dept_id, SUM(salary) AS total_sal
    FROM   employees
    GROUP BY dept_id
)
-- expensive_cteを2回参照しても実行は1回
SELECT e.dept_id, e.total_sal
FROM   expensive_cte e
WHERE  e.total_sal > 500000
UNION ALL
SELECT e.dept_id, e.total_sal
FROM   expensive_cte e
WHERE  e.total_sal <= 500000;

WITH simple_filter AS (
    /*+ INLINE */          -- 参照箇所ごとにインライン展開（物化しない）
    SELECT * FROM products WHERE status = 'ACTIVE'
)
SELECT * FROM simple_filter WHERE price > 1000;

WITH monthly_sales AS (
    SELECT
        TO_CHAR(order_date, 'YYYY-MM')  AS month,
        SUM(amount)                      AS monthly_amount
    FROM   orders
    WHERE  order_date >= TRUNC(SYSDATE, 'YYYY')   -- 今年のデータ
    GROUP BY TO_CHAR(order_date, 'YYYY-MM')
)
SELECT
    month,
    monthly_amount,
    SUM(monthly_amount) OVER (ORDER BY month
                               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS cumulative_amount    -- 累計売上
FROM   monthly_sales
ORDER BY month;

WITH
-- ステップ1: 重複レコードを除外
deduped AS (
    SELECT DISTINCT
        customer_id,
        TRIM(customer_name)  AS customer_name,   -- 前後の空白を除去
        email,
        phone
    FROM   raw_customers
),
-- ステップ2: メールアドレスの形式チェック
validated AS (
    SELECT
        customer_id,
        customer_name,
        CASE
            WHEN REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
            THEN email
            ELSE NULL
        END  AS email,      -- 不正な形式はNULLに置き換え
        phone
    FROM deduped
),
-- ステップ3: 電話番号から数字以外を除去
cleaned AS (
    SELECT
        customer_id,
        customer_name,
        email,
        REGEXP_REPLACE(phone, '[^0-9]', '') AS phone  -- ハイフン等を除去
    FROM validated
    WHERE customer_name IS NOT NULL
)
-- 最終結果
SELECT * FROM cleaned;

WITH monthly AS (
    SELECT
        TO_CHAR(order_date, 'YYYYMM')   AS ym,
        SUM(amount)                      AS sales
    FROM   orders
    GROUP BY TO_CHAR(order_date, 'YYYYMM')
)
SELECT
    cur.ym,
    cur.sales                                    AS current_sales,
    prev.sales                                   AS prev_month_sales,
    ROUND((cur.sales - prev.sales) / NULLIF(prev.sales, 0) * 100, 1)
        AS mom_growth_pct,   -- 前月比（%）
    ly.sales                                     AS last_year_sales,
    ROUND((cur.sales - ly.sales) / NULLIF(ly.sales, 0) * 100, 1)
        AS yoy_growth_pct    -- 前年同月比（%）
FROM
    monthly cur
    LEFT JOIN monthly prev
        ON prev.ym = TO_CHAR(ADD_MONTHS(TO_DATE(cur.ym, 'YYYYMM'), -1), 'YYYYMM')
    LEFT JOIN monthly ly
        ON ly.ym   = TO_CHAR(ADD_MONTHS(TO_DATE(cur.ym, 'YYYYMM'), -12), 'YYYYMM')
ORDER BY cur.ym;

WITH dept_ranking AS (
    SELECT
        dept_id,
        emp_name,
        salary,
        RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
    FROM employees
)
SELECT dept_id, emp_name, salary, rk
FROM   dept_ranking
WHERE  rk <= 3           -- 部署ごと上位3名
ORDER BY dept_id, rk;