SQL CTE・WITH句 — クエリ整理とリファクタリングの基礎

-- 1つ目のCTE: プレミアムユーザーを抽出
WITH premium_users AS (
  SELECT user_id, name
  FROM users
  WHERE rank = 'premium'
),                        -- ← カンマで繋ぐ！（超重要）
high_orders AS (          -- 2つ目のCTE: 5000円以上の高額注文を抽出
  SELECT user_id, amount
  FROM orders
  WHERE amount >= 5000
)

-- メインクエリ: 作成した2つのCTEを結合する
SELECT
  p.name,
  h.amount
FROM premium_users p
JOIN high_orders h
  ON p.user_id = h.user_id
ORDER BY p.user_id, h.amount;

/*
  実行順序:
  1. premium_users を評価  → メモリに一時保持
  2. high_orders を評価    → メモリに一時保持
  3. メインクエリで JOIN       → user_id で結合
  */

-- ① CTE: orders テーブルからユーザーごとの合計金額を集計
WITH user_sales AS (
  SELECT
    user_id,
    SUM(amount) AS total_amount
  FROM orders
  GROUP BY user_id
)

-- ② メインクエリ: 集計結果(CTE) と users を結合して名前を取得
SELECT
  u.name,
  s.total_amount
FROM users u
JOIN user_sales s
  ON u.user_id = s.user_id
ORDER BY s.total_amount DESC;

/*
  実行順序:
  1. user_sales 評価              → orders を user_id で集計
  2. users と user_sales を JOIN  → user_id で結合
  3. ORDER BY                   → 合計金額の降順で並べ替え
  */

-- ① 1つ目のCTE: キャンセルされた注文を除外
WITH valid_orders AS (
  SELECT user_id, amount
  FROM orders
  WHERE status != 'cancelled'
),
user_totals AS (               -- ② 2つ目のCTE: 1つ目のCTE(valid_orders)を参照して集計
  SELECT
    user_id,
    SUM(amount) AS total
  FROM valid_orders            -- 先に定義したCTEを参照
  GROUP BY user_id
)

-- ③ メインクエリ: 2つ目のCTE(user_totals)を参照してフィルタリング
SELECT
  user_id,
  total
FROM user_totals
WHERE total >= 10000  -- 合計1万円以上のユーザーのみ
ORDER BY user_id;

/*
  実行順序:
  1. orders から cancelled を除外  → valid_orders を作成
  2. user_id でグループ化           → user_totals を作成
  3. total のしきい値で抽出           → 条件を満たす行を残す
  */

-- ① CTE: 各ユーザーの「一番新しい注文日」を特定する
WITH latest_dates AS (
  SELECT
    user_id,
    MAX(ordered_at) AS max_date  -- MAX で最新の日付を取得
  FROM orders
  GROUP BY user_id
)

-- ② メインクエリ: 元のテーブルとCTEを結合し、最新の行だけを拾い上げる
SELECT
  o.user_id,
  o.order_id,
  o.amount,
  o.ordered_at
FROM orders o
JOIN latest_dates ld
  ON o.user_id = ld.user_id       -- 複合条件での JOIN: ユーザーIDが同じ かつ 注文日が MAX日付と同じ 行
  AND o.ordered_at = ld.max_date
ORDER BY o.user_id;

/*
  実行順序:
  1. latest_dates を作成
  2. orders(o) と latest_dates(ld) を結合
  */