ウィンドウ関数とは

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)