SQL バッチ処理 — 累計・ピボット・再帰CTEの応用

WITH ranked_scores AS (
  -- ① ゲームごとにDENSE_RANKで順位を付ける
  SELECT
    game_id,
    player_name,
    score,
    DENSE_RANK() OVER (
      PARTITION BY game_id  -- ゲーム単位でグループ化
      ORDER BY score DESC   -- スコア降順で順位付け
    ) AS rank
  FROM game_scores
)

-- ② 3位以内のプレイヤーを抽出
SELECT
  game_id,
  player_name,
  score,
  rank
FROM ranked_scores
WHERE rank <= 3               -- 同率を含め上位3位までを抽出
ORDER BY game_id, rank, score DESC;

/*
  【実行順序】
  1. CTE内 → ウィンドウ関数を評価（DENSE_RANK、行数は保持）
  2. 本体  → 行を絞り込む（rank で上位を抽出）
  3. 本体  → 並び替えて出力
*/

WITH
monthly_agg AS (                 -- ① 売上がある月のみを集計
  SELECT
    order_month,
    SUM(amount) AS total_sales,
    COUNT(*)    AS order_count
  FROM orders
  GROUP BY order_month
)

-- ② 全月マスタに売上集計を結合（LEFT JOIN）
SELECT
  m.month,
  COALESCE(a.total_sales, 0) AS total_sales, -- NULLなら0に補完
  COALESCE(a.order_count, 0) AS order_count
FROM month_master m
LEFT JOIN monthly_agg a
  ON m.month = a.order_month                 -- 売上がない月は右側がNULLになる
ORDER BY m.month;

/*
  【実行順序】
  1. CTE内 → CTE を定義（月ごとに集計）
  2. 本体  → 結合（左表を全行保持）
  3. 本体  → 値を整形（COALESCE）
  4. 本体  → 並び替えて出力
*/

WITH purchase_ranked AS (
  -- ① 各行に初回購買・最終購買の情報を付加
  SELECT
    user_id,
    order_date,
    product,
    FIRST_VALUE(order_date) OVER (                              -- 初回購買情報
      PARTITION BY user_id
      ORDER BY order_date
    ) AS first_date,
    FIRST_VALUE(product) OVER (
      PARTITION BY user_id
      ORDER BY order_date
    ) AS first_product,
    LAST_VALUE(order_date) OVER (                               -- 最終購買情報（ROWS BETWEENで全行を対象にする）
      PARTITION BY user_id
      ORDER BY order_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_date,
    LAST_VALUE(product) OVER (
      PARTITION BY user_id
      ORDER BY order_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_product
  FROM purchase_history
)

-- ② ユーザーごとに1行だけ抽出
SELECT DISTINCT
  user_id,
  first_date,
  first_product,
  last_date,
  last_product
FROM purchase_ranked
ORDER BY user_id;

/*
  【実行順序】
  1. CTE内 → 行を読み込む
  2. CTE内 → ウィンドウ関数を評価（FIRST/LAST_VALUE、行数は保持）
  3. 本体  → 重複を除去（DISTINCT）
  4. 本体  → 並び替えて出力
*/

WITH RECURSIVE category_tree AS (
  -- ═══ アンカー部: ルートノードを取得 ═══
  SELECT
    id,
    parent_id,
    name,
    0    AS depth,
    name AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- ═══ 再帰部: 親から子を繰り返し取得 ═══
  SELECT
    c.id,
    c.parent_id,
    c.name,
    t.depth + 1,               -- 階層を1つ深くする
    t.path || ' > ' || c.name  -- 親のパスに自分の名前を追加
  FROM categories c
  INNER JOIN category_tree t
    ON c.parent_id = t.id      -- 子のparent_idと親のidを結合
)

-- 再帰完了後、全結果を出力
SELECT
  id,
  name,
  depth,
  path
FROM category_tree
ORDER BY id;

/*
  【実行順序（再帰CTEの特殊フロー）】
  1. アンカー部を評価（起点）
  2. 再帰1回目（子を展開）
  3. 再帰2回目（子を展開）
  4. 追加行なしで停止
  5. 外側クエリを評価   → ORDER BY で並び替えて出力
*/