DS検定で頻出のウィンドウ関数。集計と詳細を1クエリで両立できる強力ツール。
白峰 リリ
ウィンドウ関数って、どんな時に使うの?
神楽 モニカ 先生
集計値を出しつつ、元の各行もそのまま残したいときね。 例えば『各注文に、その顧客の累計購入額を並べたい』みたいなときよ。
紅林 かえで
代表的なウィンドウ関数: ROW_NUMBER(連番)、RANK(順位、同値はタイ)、DENSE_RANK(同値は同順位、次は連続)、LAG(前の行)、LEAD(次の行)。
-- 各カテゴリ内で売上トップ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;
藍沢 しずく
OVER の中の PARTITION BYって、なんですかぁ?
紅林 かえで
GROUP BY のウィンドウ版だね。 『カテゴリごとに区切って、その中で順位を計算する』っていう指定。 ORDER BY と組み合わせると、区切りごとの並び順で順位がつくよ。
白峰 リリ
LAG・LEADは?
-- 各注文と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;
神楽 モニカ 先生
LAG で『前の行の値』を取ってきて、今の行との差分を計算しているのよ。 時系列データの分析でよく出てくるパターンね。
紅林 かえで
SUM OVER も便利で、累計を計算できるよ。 SUM(amount) OVER (PARTITION BY customer_id ORDER BY ordered_at) って書けば、『その顧客の累計購入額』が出せる。
藍沢 しずく
RANK と DENSE_RANK の違いはなんですかぁ?
神楽 モニカ 先生
1位が2人いた場合ね。 RANK は次が3位(1, 1, 3, ...)、DENSE_RANK は次が2位(1, 1, 2, ...)になるのよ。 ROW_NUMBER は重複なしで一意(1, 2, 3, ...)ね。
紅林 かえで
DS検定★では、この3つの違いと、PARTITION BY の意味、それに LAG/LEAD のパターンが頻出だよ。
確認クイズ
ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC) の結果について正しいのはどれか。
- 全注文を金額降順で連番付け
- カテゴリごとに金額降順で連番付け
- カテゴリの数だけグループを作って合計を出す
- 注文を3つに分割する
こたえを見る
正解: 2. カテゴリごとに金額降順で連番付け
カテゴリごとに金額降順で連番付け。 PARTITION BY category でカテゴリで区切り、各カテゴリ内で amount 降順に1, 2, 3...と番号がつきます。 これがウィンドウ関数の典型的な使い方です。
