SQL CTE・WITH句 — 事前集計・ウィンドウ関数連携の基礎

-- ① 1月の購入者リストを作成
WITH jan_users AS (
  SELECT DISTINCT user_id           -- 1月に複数回買った場合も1行にする
  FROM orders
  WHERE ordered_at >= '2024-01-01'
    AND ordered_at <  '2024-02-01'
),
feb_users AS (                      -- ② 2月の購入者リストを作成
  SELECT DISTINCT user_id
  FROM orders
  WHERE ordered_at >= '2024-02-01'
    AND ordered_at <  '2024-03-01'
)

-- ③ メインクエリで両者を結合し、両月に存在するユーザーを特定
SELECT
  j.user_id
FROM jan_users j
JOIN feb_users f            -- INNER JOIN: 両方に存在するIDのみが残る
  ON j.user_id = f.user_id;

/*
  実行順序:
  1. jan_users   → 1月の注文から user_id を抽出
  2. feb_users   → 2月の注文から user_id を抽出
  3. INNER JOIN  → 両方に存在する user_id だけ残す
  */

-- ① CTE: ウィンドウ関数を使って、グループごとの連番を付与した一時テーブルを作る
WITH ranked_orders AS (
  SELECT
    user_id,
    order_id,
    amount,
    ordered_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ordered_at DESC) AS rn  -- ユーザーごとに新しい順で連番
  FROM orders
)

-- ② メインクエリ: 連番(rn)が 1 の行 ＝ 各ユーザーの最新行 だけを抽出
SELECT
  user_id,
  order_id,
  amount,
  ordered_at
FROM ranked_orders
WHERE rn = 1        -- ここで最新行に絞り込む（ウィンドウ関数はWHEREに直接書けないためCTEが必須）
ORDER BY user_id;

/*
  実行順序:
  1. ranked_orders (CTE)  → user_id ごとに順位(rn)を付与
  2. メインクエリ WHERE rn=1    → 各ユーザーの最新行だけ残す
  */

-- ① CTE: カテゴリごとの平均価格を計算
WITH category_avgs AS (
  SELECT
    category_id,
    AVG(price) AS avg_price
  FROM products
  GROUP BY category_id
)

-- ② メインクエリ: 商品データと、CTEで計算したカテゴリ平均を結合し比較
SELECT
  p.id,
  p.name,
  p.price,
  c.avg_price
FROM products p
JOIN category_avgs c
  ON p.category_id = c.category_id
WHERE p.price >= c.avg_price      -- 商品価格がカテゴリ平均以上かどうか判定
ORDER BY p.id;

/*
  実行順序:
  1. category_avgs  → カテゴリごとの平均価格を集計
  2. JOIN           → 各商品に自カテゴリの平均を結合
  3. WHERE          → 平均以上の商品だけ残す
  */

-- RECURSIVE をつけることで、CTEの中で自分自身(category_tree)を呼び出せるようになる
WITH RECURSIVE category_tree AS (

  -- 【ステップ1：非再帰項（出発点）】
  -- まず、起点となる id = 1（家電）の行だけを取得する
  SELECT id, name, parent_id
  FROM categories
  WHERE id = 1

  UNION ALL  -- 上の結果と、下の繰り返しの結果を合体させる

  -- 【ステップ2：再帰項（繰り返し処理）】
  -- 直前に見つかった階層(ct) を親(parent_id)として持つ 子(c) を探し出す
  SELECT
    c.id, c.name, c.parent_id
  FROM categories c
  JOIN category_tree ct        -- 自分自身のCTEを JOIN する！
    ON c.parent_id = ct.id     -- 子の parent_id が、親の id と一致するものを探す
)

-- 【ステップ3：最終出力】
SELECT * FROM category_tree
ORDER BY id;

/*
  実行のシミュレーション:
  1. 初期: WHERE id = 1 により「家電(id=1)」が抽出される。
  2. 1周目: 親(ct)が「家電(id=1)」のもの。→ parent_id=1 である「PC(id=2)」が抽出される。
  3. 2周目: 親(ct)が「PC(id=2)」のもの。→ parent_id=2 である「ノートPC(id=3), デスクトップ(id=4)」が抽出。
  4. 3周目: 親(ct)が「ノートPC, デスク」のもの。→ 見つからないため、ここで再帰ループが終了。
  5. 最終的に抽出された 1, 2, 3, 4 が合体して返される。（書籍=5 はツリーが違うため除外）
*/