SQL CTE・WITH句 — 複数CTE・再帰CTE・クエリ分割の応用

WITH
user_totals AS (                  -- ① ユーザーごとに注文を合計する（中間集計）
  SELECT                          -- SELECT: 取得する列を指定
    user_id,
    SUM(amount) AS total_amount   -- SUM: 複数注文を合算。ASで別名をつける
  FROM     orders                 -- FROM: 取得元テーブル
  GROUP BY user_id                -- GROUP BY: ユーザー単位でグループ化
),
user_ranked AS (                  -- ② user_totals を使って NTILE で4分位を付ける（前のCTEを参照）
  SELECT
    user_id,
    total_amount,
    NTILE(4) OVER (               -- NTILE(4): 全行を4等分してバケット番号を付与
      ORDER BY total_amount DESC  -- ORDER BY ... DESC: 購入額の降順(大きい順)で並べる
    ) AS quartile
  FROM   user_totals              -- FROM: 前のCTE(user_totals)をテーブルとして参照
)

-- ③ 本体: user_ranked に users テーブルを JOIN して名前を付加
SELECT
  r.user_id,
  u.user_name,                           -- users テーブルから名前を取得（結合して追加）
  r.total_amount,
  r.quartile
FROM       user_ranked r                 -- FROM: r は user_ranked のエイリアス（短縮名）
INNER JOIN users       u                 -- INNER JOIN: 両方のテーブルに存在する行のみ結合
        ON r.user_id = u.user_id         -- ON: 結合条件（user_idが一致する行を繋げる）
ORDER BY  r.quartile, r.total_amount DESC; -- ORDER BY: 四分位の昇順 → 購入額降順の順で並べる

/*
  実行順序:
  1. CTE user_totals: FROM orders                      → GROUP BY user_id
  2. CTE user_ranked: FROM user_totals
  3. 本体: FROM user_ranked INNER JOIN users ON user_id  → 名前付加
  4. 本体: ORDER BY quartile, total_amount DESC
  */

WITH ranked_products AS (

  -- ① 全商品に「カテゴリ内の売上順位」を付ける
  SELECT                     -- SELECT: 抽出する列を指定
    category,
    product_name,
    sales,
    ROW_NUMBER() OVER (      -- ROW_NUMBER(): 1から始まる連番を付けるウィンドウ関数
      PARTITION BY category  -- PARTITION BY: カテゴリごとに区切って番号をリセット
      ORDER BY sales DESC, product_id  -- 同額はproduct_id昇順で決定
    ) AS rn                  -- AS rn: row_number の略として別名を定義
  FROM product_sales         -- FROM: 対象テーブル
)                            -- ここでは rn を WHERE に使えない（同一SELECT内でウィンドウ関数結果は参照不可）

-- ② CTE の結果から rn が 2 以下（上位2件）だけを取り出す
SELECT
  category,
  product_name,
  sales,
  rn
FROM   ranked_products  -- FROM: 上で作ったCTEを参照
WHERE  rn <= 2            -- WHERE: 各カテゴリで順位が2以下（1位・2位）のみ残す
ORDER BY category, rn;  -- ORDER BY: カテゴリの昇順 → 順位の昇順で並べる

/*
  実行順序:
  1. CTE内 FROM product_sales      → 全商品行を読み込み
  2. CTE内 ROW_NUMBER() OVER(...)  → カテゴリごとに売上降順で番号付与
  3. 外側 FROM ranked_products      → CTE結果を参照
  4. 外側 WHERE rn <= 2             → 各カテゴリの上位2件を残す
  5. 外側 ORDER BY category, rn     → 整列して出力
  */

WITH sales_with_lag AS (

  -- ① LAG で「1行前の売上」を各行に追加する
  SELECT
    order_month,
    sales,
    LAG(sales, 1) OVER (    -- LAG(列, n): n行前の値を取得（なければNULL）
      ORDER BY order_month  -- ORDER BY: 月の昇順で並べ、何が「前」かを定義する
    ) AS prev_sales         -- AS: 列に名前をつける
  FROM monthly_sales        -- FROM: データ取得元テーブル

)

-- ② CTE を使って mom_rate（前月比）を計算する
SELECT
  order_month,
  sales,
  prev_sales,
  ROUND(                     -- ROUND(値, 桁数): 指定した桁数で四捨五入
    (sales - prev_sales)     -- 今月と前月の差分（増加分）
    * 100.0                  -- 100.0を掛けて%化（整数同士の除算を浮動小数点に変換）
    / NULLIF(prev_sales, 0)  -- NULLIF(a,b): aがbなら NULLを返す（0で割るエラーを防ぐ）
  , 1)                       -- 第2引数: 小数点以下1桁に丸める
  AS mom_rate                -- 計算結果に mom_rate という名前をつける
FROM     sales_with_lag      -- FROM: 上で作ったCTEを参照
ORDER BY order_month;        -- ORDER BY: 月の昇順で並べる

/*
  実行順序:
  1. CTE内 FROM monthly_sales      → 全行読み込み
  2. CTE内 LAG(sales,1) OVER(...)  → 前月の sales を付与
  3. 外側 FROM sales_with_lag       → CTE結果を参照
  4. 外側 ROUND(...)                → 前月比を計算
  5. 外側 ORDER BY order_month      → 月順で並べる
  */

-- 再帰CTEには WITH RECURSIVE を使う（PostgreSQL/BigQuery共通）
WITH RECURSIVE org_tree AS (

  -- ① アンカーメンバー: 再帰の「起点」となる行（社長）を選ぶ
  SELECT                    -- SELECT: 取得する列
    emp_id,
    name,
    manager_id,
    0    AS depth,          -- 起点の階層深さを0とする（ASで列名付与）
    name AS path            -- pathの初期値は自分の名前のみ
  FROM  employees           -- FROM: データ元
  WHERE manager_id IS NULL  -- WHERE ... IS NULL: 値が存在しない（上司がいない = トップ）行に絞る

  UNION ALL  -- UNION ALL: 複数のSELECT結果を縦にそのまま結合（重複含む）

  -- ② 再帰メンバー: org_tree の結果に「部下」を追加し続ける
  SELECT
    e.emp_id,
    e.name,
    e.manager_id,
    t.depth + 1,                      -- 親の階層(depth)に +1 して1段深くする
    t.path || '→' || e.name           -- ||: 文字列同士を連結する演算子（PostgreSQL等）
  FROM       employees  e             -- FROM: e は部下側（次の階層）
  INNER JOIN org_tree   t             -- INNER JOIN: t は前ステップで確定した親の行
          ON e.manager_id = t.emp_id  -- ON: 「部下の上司ID = 親のemp_id」という条件で結合
  WHERE t.depth < 10                   -- WHERE: 無限ループを防止するため、最大10階層までに制限

)

SELECT
  emp_id,
  name,
  manager_id,
  depth,
  path
FROM     org_tree        -- FROM: 完成した再帰CTEを参照
ORDER BY depth, emp_id;  -- ORDER BY: 階層(深さ)の昇順 → emp_idの昇順で並べる

/*
  実行順序（再帰の反復）:
  1. アンカー                  → 起点行を生成（社長）
  2. 再帰1回目                 → 子を展開
  3. 再帰2回目                 → 子を展開
  4. 再帰3回目                 → 子を展開
  5. 再帰4回目                 → 追加行なしで終了
  6. UNION ALL → ORDER BY  → 縦積みして整列
  */