ウィンドウ関数(ROW_NUMBER・RANK・LAG・LEAD)

DS検定で頻出のウィンドウ関数。集計と詳細を1クエリで両立できる強力ツール。

-- 各カテゴリ内で売上トップ3を抽出
WITH ranked AS (
  SELECT
    order_id,
    category,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY amount DESC
    ) AS rn
  FROM orders
)
SELECT order_id, category, amount
FROM ranked
WHERE rn <= 3;

-- 各注文と1つ前の注文の差額を計算
SELECT
  order_id,
  customer_id,
  amount,
  LAG(amount) OVER (
    PARTITION BY customer_id
    ORDER BY ordered_at
  ) AS prev_amount,
  amount - LAG(amount) OVER (
    PARTITION BY customer_id
    ORDER BY ordered_at
  ) AS diff
FROM orders;

確認クイズ

ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC) の結果について正しいのはどれか。

1. 全注文を金額降順で連番付け
2. カテゴリごとに金額降順で連番付け
3. カテゴリの数だけグループを作って合計を出す
4. 注文を3つに分割する

こたえを見る

正解: 2. カテゴリごとに金額降順で連番付け

カテゴリごとに金額降順で連番付け。 PARTITION BY category でカテゴリで区切り、各カテゴリ内で amount 降順に1, 2, 3...と番号がつきます。 これがウィンドウ関数の典型的な使い方です。