ウィンドウ関数の使い方
行を残したまま順位・累計
書き方
関数() OVER (PARTITION BY 列 ORDER BY 列) ROW_NUMBER() RANK() DENSE_RANK() SUM(列) OVER (…) LAG(列) LEAD(列)
GROUP BY と違い行はまとまりません。PARTITION BY でグループ、ORDER BY で順番を決めます。
よく使うオプション
| オプション | 意味 |
|---|---|
OVER () | 全体を 1 つの窓として計算する |
PARTITION BY 列 | グループごとに分けて計算する（行はまとまらない） |
ORDER BY 列 | 窓の中の順番を決める（順位・累計に要る） |
ROW_NUMBER / RANK / DENSE_RANK | 通し番号 / 同点は同じ順位で次を飛ばす / 飛ばさない |
LAG / LEAD | 前の行 / 次の行の値 |
例
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees; name | salary | rank
------+--------+------
鈴木 | 350000 | 1
佐藤 | 300000 | 2
高橋 | 300000 | 2
田中 | 280000 | 4
(4 rows)給与の高い順に順位をつけます。行はまとまらず、全員の行に順位が付きます。
SELECT name, dept_id, salary, AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg FROM employees;
社員の行ごとに、その部署の平均給与を並べて出します。GROUP BY と違い、社員の行はそのまま残ります。
SELECT ordered_on, total, SUM(total) OVER (ORDER BY ordered_on) AS running FROM orders;
日付の順に、その日までの金額の累計を出します。
SELECT ordered_on, total, total - LAG(total) OVER (ORDER BY ordered_on) AS diff FROM orders;
1 つ前の注文と比べて、金額がいくら増えたか減ったかを出します。最初の行は前が無いので NULL です。
よくある間違い・注意
- ウィンドウ関数は WHERE に書けません。順位で絞るときは、副問い合わせか WITH で先に計算してから WHERE で絞ります。
- ORDER BY を付けた SUM(…) OVER は、その行までの累計になります。全体の合計が欲しいときは ORDER BY を外します。
実際に打って練習する
SQL コースの Lv35「順位をつける ウィンドウ関数」で覚えます。ブラウザの中のシミュレーターで、本物と同じように打って確かめられます。
Lv1〜3 は、登録不要・無料で遊べます（それより先はブロンズ以上のプランで遊べます）。