ウィンドウ関数とは
OVER (...) を付けて使う関数のことです。
SUM(amount) OVER (PARTITION BY name) -
SUM(amount) OVER (...)全体が ウィンドウ関数 -
PARTITION BYは、その中に書く 「どのグループ単位で計算するか」の指定
PARTITION BY は省略もでき、省略するとテーブル全体が1つのグループとして扱われます。
※ テーブルを物理的に分割する「テーブルパーティショニング」(
CREATE TABLE ... PARTITION BY)にも同じ単語が出てきますが、ウィンドウ関数とは別物です。
GROUP BY と PARTITION BY の違い
結論から言うと、違いはこれだけです。
- GROUP BY:グループごとに分けて、1行にまとめる
- PARTITION BY:グループごとに分けるけど、まとめない(各行に計算結果を付ける)
元のテーブル
sales テーブル
| name | amount |
|---|---|
| 田中 | 100 |
| 田中 | 200 |
| 鈴木 | 500 |
GROUP BY の場合
SELECT name, SUM(amount) FROM sales GROUP BY name; | name | sum |
|---|---|
| 田中 | 300 |
| 鈴木 | 500 |
名前ごとに 1行にまとまります。元の「100」「200」という行は消えます。
PARTITION BY の場合
SELECT name, amount, SUM(amount) OVER (PARTITION BY name) AS total FROM sales; | name | amount | total |
|---|---|---|
| 田中 | 100 | 300 |
| 田中 | 200 | 300 |
| 鈴木 | 500 | 500 |
行は 3行のまま で、各行に「名前ごとの合計」が付きます。
何が嬉しいの?
行が残るので、元の値と合計を 同じ行で並べて計算できます。
たとえば「その人の売上のうち、この1件が何割か」も1回のクエリで出せます。
SELECT name, amount, amount / SUM(amount) OVER (PARTITION BY name) AS ratio FROM sales; | name | amount | ratio |
|---|---|---|
| 田中 | 100 | 0.33 |
| 田中 | 200 | 0.67 |
| 鈴木 | 500 | 1.00 |
ROW_NUMBER:グループの中で連番を振る
ROW_NUMBER() は、グループの中で並び順に 1, 2, 3… と番号を振る ウィンドウ関数です。
OVER (...) の中に ORDER BY を書くと、グループの中の並び順 を指定できます。
SELECT name, amount, ROW_NUMBER() OVER (PARTITION BY name ORDER BY amount DESC) AS row_num FROM sales; | name | amount | row_num |
|---|---|---|
| 田中 | 200 | 1 |
| 田中 | 100 | 2 |
| 鈴木 | 500 | 1 |
名前ごとに、金額の大きい順で番号が振られます。
よく使うパターン:各グループの1位だけ取る
row_num = 1 で絞ると「各人の最高額の1件」が取れます。
SELECT * FROM ( SELECT name, amount, ROW_NUMBER() OVER (PARTITION BY name ORDER BY amount DESC) AS row_num FROM sales ) t WHERE row_num = 1; | name | amount | row_num |
|---|---|---|
| 田中 | 200 | 1 |
| 鈴木 | 500 | 1 |
row_num は SELECT で作った列なので、同じクエリの WHERE では使えません。そのためサブクエリで包んでから絞っています。
RANK との違い
同じ値が並んだときの番号の振り方が違います。
| amount | ROW_NUMBER | RANK |
|---|---|---|
| 500 | 1 | 1 |
| 500 | 2 | 1 |
| 300 | 3 | 3 |
-
ROW_NUMBER:同点でも必ず別の番号(1, 2, 3) -
RANK:同点は同じ番号で、次の番号は飛ぶ(1, 1, 3)