MYSQL WINDOW関数を改めて学びなおす

WINDOW関数を簡単に説明

通常の集計関数は行をグループ化して集計するが
window関数を使用するとグループ化することなく行ごとに集計できる。

例

下記のような飲料テーブルみたいなデータがあるとする

SELECT * FROM test_drink;

id	type	price	date	maker
1	coffee	120	2022-01-10	inu
2	coffee	130	2022-01-10	neko
3	coffee	140	2022-01-10	uma
4	water	200	2022-01-10	neko
5	water	220	2022-01-10	inu
6	water	240	2022-01-10	uma
7	energy	300	2022-01-10	saru
8	energy	330	2022-01-10	saru
9	energy	350	2022-01-10	saru
10	tea	410	2022-01-10	inu
11	tea	420	2022-01-10	uma
12	tea	430	2022-01-10	neko

単純に集計してみる

値段の総合計

SELECT SUM(price) FROM test_drink;

SUM(price)
3290

type毎に集計

typeごとの合計

SELECT type, sum(price) FROM test_drink group by type;

type	sum(price)
coffee	390
water	660
energy	980
tea	1260

window関数を使用して行はそのままに前述の合計を出す

window関数を使用

SELECT 
	*,
    SUM(price) over() AS total_price,
    SUM(price) OVER(PARTITION BY type) AS type_total_price
FROM test_drink
order by id;

id	type	price	date	maker	total_price	type_total_price
1	coffee	120	2022-01-10	inu	3290	390
2	coffee	130	2022-01-10	neko	3290	390
3	coffee	140	2022-01-10	uma	3290	390
4	water	200	2022-01-10	neko	3290	660
5	water	220	2022-01-10	inu	3290	660
6	water	240	2022-01-10	uma	3290	660
7	energy	300	2022-01-10	saru	3290	980
8	energy	330	2022-01-10	saru	3290	980
9	energy	350	2022-01-10	saru	3290	980
10	tea	410	2022-01-10	inu	3290	1260
11	tea	420	2022-01-10	uma	3290	1260
12	tea	430	2022-01-10	neko	3290	1260

行はそのままに全price合計、type毎の合計が出せる

上記window関数の説明

OVER(PARTITION BY type)
OVER : クエリーの結果に対してoverを使用することでwindow関数が適用される。
PARTITION BY : 指定することによってパーティション化される
※パーティショニングとは別で行を単一にまとめないGROUP BYみたいなもの
前述total_price : OVER()※空なので結果セット全体に対しての合計
前述type_total_price : OVER(PARTITION BY type) ※typeでパーティション(group)化して合計

よく順位を出すときに使われるROW_NUMBER

こんなデータを用意

id	name	type	price
1	コーヒー牛乳	coffee	120
2	ブラックコーヒー	coffee	410
3	カフェラテ	coffee	140
4	硬水	water	150
5	軟水	water	220
6	南アルプス	water	410
7	モンスター	energy	300
8	デカビタ	energy	120
9	レッドブル	energy	350
10	お茶	tea	250
11	紅茶	tea	220
12	麦茶	tea	430

ここで順位を価格の安い順を出すためにいろんなwindow関数を使用

順位の出し方

SELECT
	id,
    name,
    type,
	price,
    ROW_NUMBER() OVER(ORDER BY price) AS `ROW_NUMBER`,
    RANK() OVER(ORDER BY price) AS `RANK`,
    DENSE_RANK() OVER(ORDER BY price) AS `DENSE_RANK`
FROM test_drink
order by price, id;

結果

id	name	type	price	ROW_NUMBER	RANK	DENSE_RANK
1	コーヒー牛乳	coffee	120	1	1	1
8	デカビタ	energy	120	2	1	1
3	カフェラテ	coffee	140	3	3	2
4	硬水	water	150	4	4	3
5	軟水	water	220	5	5	4
11	紅茶	tea	220	6	5	4
10	お茶	tea	250	7	7	5
7	モンスター	energy	300	8	8	6
9	レッドブル	energy	350	9	9	7
2	ブラックコーヒー	coffee	410	10	10	8
6	南アルプス	water	410	11	10	8
12	麦茶	tea	430	12	12	9

結果

ROW_NUMBER : あくまで現在の行数を出す関数のため、120円が2つあっても順位に差が出ている
RANK : 同率の順位がいた場合は次の順位がスキップされる　例だと2位がいない
DENSE_RANK : 同率の順位がいた場合、次の順位がスキップされない　例だと2位がいる
※順位を出すときは目的にあった関数を使う

WINDOW関数におけるORDER BY

order by と聞くと単純に並び替えを想像するが、並び替えかつ処理する順番が指定される
window関数ではパーティション化された先頭行から現在行までの対象を指定する

例

順位の出し方

SELECT
	id,
    name,
    type,
	price,
    SUM(price) OVER(PARTITION BY type ORDER BY price) AS sum_price
FROM test_drink

id	name	type	price	sum_price
1	コーヒー牛乳	coffee	120	120
3	カフェラテ	coffee	140	260
2	ブラックコーヒー	coffee	410	670
8	デカビタ	energy	120	120
7	モンスター	energy	300	420
9	レッドブル	energy	350	770
11	紅茶	tea	220	220
10	お茶	tea	250	470
12	麦茶	tea	430	900
4	硬水	water	150	150
5	軟水	water	220	370
6	南アルプス	water	410	780

上記はtypeでパーティション化し価格順で並び替え合計するという動きをする
coffee時はprice順で並び替え、先頭から現在の行までの合計をだしている
id = 2のブラックコーヒーはtypeでパーティションされprice順に並び変えたので
コーヒー牛乳の120 + カフェラテの140 + ブラックコーヒーの410を合計した数値となる

ORDER BYの範囲指定

ORDER BY で指定された範囲のことはフレーム呼ばれる。ROWSによりフレームの範囲も指定できる。

フレームの指定

SELECT
	id,
    name,
    type,
	price,
    SUM(price) OVER(PARTITION BY type ORDER BY price) AS sum_price,
    SUM(price) OVER(PARTITION BY type ORDER BY price ROWS 1 PRECEDING) AS before_total_price
FROM test_drink

id	name	type	price	sum_price	before_total_price
1	コーヒー牛乳	coffee	120	120	120
3	カフェラテ	coffee	140	260	260
2	ブラックコーヒー	coffee	410	670	550
8	デカビタ	energy	120	120	120
7	モンスター	energy	300	420	420
9	レッドブル	energy	350	770	650
11	紅茶	tea	220	220	220
10	お茶	tea	250	470	470
12	麦茶	tea	430	900	680
4	硬水	water	150	150	150
5	軟水	water	220	370	370
6	南アルプス	water	410	780	630

先ほどの例にbefore_total_priceを加えた例だが、rowsに1 PRECEDINGを指定している。
N PRECEDINGはN個前のデータを開始位置にしているため、1つ前のpriceと現在のpriceの合計になっている。
このデータだと使い道がなさそうだが、例えば時間差を出すときとかに使えそう。
他のフレーム操作関数
CURRENT ROW : 現在行
UNBOUNDED PRECEDING : パーティションの先頭
UNBOUNDED FOLLOWING : パーティションの終端
N PRECEDING : 現在行のN行前
N FOLLOWING : 現在行のN行後

その他window関数の使い方

# id	name	type	price	date	maker
1	コーヒー牛乳	coffee	120	2022-01-10	inu
2	ブラックコーヒー	coffee	410	2022-01-10	neko
3	カフェラテ	coffee	140	2022-01-13	uma
4	硬水	water	150	2022-01-12	neko
5	軟水	water	220	2022-01-15	inu
6	南アルプス	water	410	2022-01-12	uma
7	モンスター	energy	300	2022-01-12	saru
8	デカビタ	energy	120	2022-01-14	saru
9	レッドブル	energy	350	2022-01-15	saru
10	お茶	tea	250	2022-01-16	inu
11	紅茶	tea	220	2022-01-17	uma
12	麦茶	tea	430	2022-01-17	neko

上記のようなデータがあるとして日毎の前日との差分を出す

前日との差分

SELECT
	s.date,
	s.price,
    s.price - LAG(s.price) OVER(ORDER BY s.date) AS before_date_diff
FROM (
	SELECT
		date,
		SUM(price) AS price
	FROM test_drink
	GROUP BY
		date
) AS s;

date	price	before_date_diff
2022-01-10	530
2022-01-12	860	330
2022-01-13	140	-720
2022-01-14	120	-20
2022-01-15	570	450
2022-01-16	250	-320
2022-01-17	650	400

一度日毎にグループ化してから現在のpriceからLAG()関数（前の行の値をとる）を引いて
前日との差分を算出する。

その他window関数

CUME_DIST(): 現在の行の値以下のパーティション値の割合を戻します。
DENSE_RANK() : パーティション内の現在の行のランクをギャップなしで返します。
FIRST_VALUE(expr) : ウィンドウフレームの最初の行からexprの値を返します。
LAG(expr, N) : パーティション内のN行で現在の行を遅延 (前) する行からexprの値を返します。
LAST_VALUE(expr) : ウィンドウフレームの最後の行からexprの値を返します。
LEAD(expr, N) : パーティション内のN行ごとに現在の行を導く (後に続く) 行から、exprの値を返します。
NTH_VALUE(expr, N) : ウィンドウフレームのN番目の行からexprの値を返します。
NTILE() : パーティション内の現在の行のバケット番号。
PERCENT_RANK() : 現在の行の値より小さいパーティション値の割合を戻します (最大値を除く)。
RANK() : パーティション内の現在の行のランク (ギャップあり) を戻します。
ROW_NUMBER() : パーティション内の現在の行の番号を返します。

まとめ

今までサブクエリーや自己結合で集計していたものがwindow関数で簡単に出せるようになる場合があります。
今回は簡単な使い方の紹介でしたが、使い方を覚えれば色々な場面に応用が利くと思いますので積極的に使っていきましょう。