SQL読解問題(ウィンドウ関数・CTE)

SQL読解の応用編。ウィンドウ関数とCTEを素早く読み解くための実践問題。

-- ▼ 問題例1: 各カテゴリ内での売上順位
SELECT
  product_id,
  category,
  total_sales,
  ROW_NUMBER() OVER (
    PARTITION BY category
    ORDER BY total_sales DESC
  ) AS rank_in_cat
FROM product_summary;

-- 解読:
-- - category で区切り
-- - その中で total_sales 降順で並べ
-- - 1位から順に番号 (rank_in_cat=1, 2, 3, ...)

-- ▼ 問題例2: 累計と前月比
SELECT
  month,
  monthly_sales,
  SUM(monthly_sales) OVER (
    ORDER BY month
  ) AS cumulative,
  monthly_sales - LAG(monthly_sales) OVER (
    ORDER BY month
  ) AS diff_vs_prev
FROM monthly_summary
ORDER BY month;

-- 解読:
-- - SUM(...) OVER (ORDER BY ...) で累計
-- - LAG(...) で前月の値を取得
-- - 当月 - 前月 で前月比 (最初の月は NULL)

-- ▼ 問題例3: CTE で多段クエリ
WITH monthly AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', ordered_at) AS month,
    SUM(amount) AS monthly_amt
  FROM orders
  GROUP BY 1, 2
),
ranked AS (
  SELECT
    *,
    RANK() OVER (PARTITION BY month ORDER BY monthly_amt DESC) AS rk
  FROM monthly
)
SELECT month, customer_id, monthly_amt
FROM ranked
WHERE rk <= 3
ORDER BY month, rk;

-- 解読:
-- 1段目 monthly: 顧客×月の売上集計
-- 2段目 ranked:  各月内の売上順位を付与
-- 最終: 各月のトップ3顧客を抽出

確認クイズ

RANK() OVER (PARTITION BY category ORDER BY price DESC) で、同カテゴリ内に同価格が2件並んだ場合の順位はどうなるか。

1. 1, 2, 3, 4, 5(重複なし)
2. 1, 1, 3, 4, 5(同値同順位、次は飛ぶ)
3. 1, 1, 2, 3, 4(同値同順位、次も連続)
4. ランダム順

こたえを見る

正解: 2. 1, 1, 3, 4, 5(同値同順位、次は飛ぶ)

1, 1, 3, 4, 5(同値同順位、次は飛ぶ)がRANK()の挙動。 順位が1位の人が2人いれば次は3位。 重複なしならROW_NUMBER、連続させたいならDENSE_RANK を使います。