【SQL】ウィンドウ関数について勉強したことを書いてみる

ウィンドウ関数について書籍『達人に学ぶSQL徹底指南書 第2版』で学んだので、自分なりの解釈を記事にしてみようと思います。
本記事をご覧になった方で、誤りやご指摘、お気づきの点がござましたら、コメント等でご教示いただけますようお願いいたします。（私の成長につながります）

まずはじめに「GROUP BY」と「PARTITION BY」の違いを理解しよう！

ウィンドウ関数の説明をする入る前に「GROUP BY」と「PARTITION BY」の違いについて、お伝えしたいと思います。
「GROUP BY」も「PARTITION BY」もレコードを指定の区分ごとでまとめたいときに使用しますが、そのまとめ方が異なります。

例えば4つのレコードがあるとします。

■GROUP BY
GROUP BYでは、以下のようにまとめます。

■PARTITION BY
PARTITION BYでは、以下のようにまとめます。

両者の違いは、「GROUP BY」がレコードA,レコードBを一纏めにしているのに対し、
「PARTITION BY」では、AとBに分けつつ、各レコードを扱っていることです。

簡単なイメージの違いではありますが、両者の違い抑えたところで、ウィンドウ関数の説明に移りたいと思います。

ウィンドウ関数を使って楽に集計しよう！

例として、以下のようなServerLoadテーブルがあるとします

server_name	max_val	load_val	load_date
A	1024	460	2024-10-29
A	2048	1120	2024-10-30
A	4096	1900	2024-11-02
B	512	160	2024-10-26
B	4096	3400	2024-10-30
B	1024	530	2024-11-01
B	2024	800	2024-11-07

ローカル環境でお試しされる場合は、以下を実行してください。
（以下はMySQL8.0に対応のため、他DB製品では記法が異なることがありますのでご注意ください）

-- ServerLoadテーブルを作成する
CREATE TABLE `ServerLoad` (
	`server_name` VARCHAR(100) NOT NULL,
	`max_val` INT NOT NULL,
	`load_val` INT NOT NULL,
	`load_date` TIMESTAMP NOT NULL
);

-- ServerLoadテーブルにサンプルレコードを挿入
INSERT INTO ServerLoad (server_name, max_val, load_val, load_date)
VALUES
('A', 1024, 460, '2024-10-29'),
('A', 2048, 1120, '2024-10-30'),
('A', 4096, 1900, '2024-11-02'),
('B', 512, 160, '2024-10-26'),
('B', 4096, 3400, '2024-10-30'),
('B', 1024, 530, '2024-11-01'),
('B', 2048, 800, '2024-11-07');

※日付項目は、時分秒を省略しています。

このServerLoadテーブルにおいて、サーバーごとのmax_valとload_valの合計値を集計したいとします。
この場合は「GROUP BY」を用います。

実行SQL例：

SELECT server_name, SUM(max_val) AS sum_max_val, SUM(load_val) AS sum_load_val
FROM ServerLoad
GROUP BY server_name;

結果例：

server_name	sum_max_val	sum_load_val
A	7168	3480
B	7680	4890

これにより、サーバーごとの負荷上限と現在の負荷量の合計を知ることができました。
しかし、サーバーのレコード単位での合計を知りたいというような状況がある場合、「GROUP BY」では柔軟な対応が難しいと思います。

そこで登場するのがウィンドウ関数です。
ウィンドウ関数では、指定の区分ごとにパーティションで区切り、昇順降順でソートをかけた各レコードに対して、カーソル（マウスカーソルのイメージでOK!!）を移動させながらパーティション内において1行ずつ集計を行うことができます。

例として、server_nameごとにload_dateを昇順に並べて、load_valの現時点までの累積値を知りたいとします。
以下の結果セットが期待されます。

server_name	load_date	cur_val	cum_load_val
A	2024-10-29	460	460
A	2024-10-30	1120	1580
A	2024-11-02	1900	3480
B	2024-10-26	160	160
B	2024-10-30	3400	3560
B	2024-11-01	530	4090
B	2024-11-07	800	4890

この場合、次のSQLを発行します。

SELECT server_name, load_date, load_val AS cur_val,
	SUM(load_val) OVER(PARTITION BY server_name ORDER BY load_date) AS cum_load_val
FROM ServerLoad;

この書き方を初めて見た方にとっては、少々気味が悪いですよね。
「SUM関数の後に変なのがついてる...」って感じだと思います。

上記SQLにおけるウィンドウ関数は、その気味が悪い部分、すなわち

SUM(load_val) OVER(PARTITION BY server_name ORDER BY load_date) AS cum_load_val

部分です。

順番に処理を追っていきましょう。

まず、ServerLoadテーブルをserver_nameごとにパーティションに区切り、各パーティションの中でload_dateを昇順に並べ変える必要があります。
それを実現しているのが、

OVER(PARTITION BY server_name ORDER BY load_date)

部分になります。

※ここでは、説明のためにserver_nameが「A」で区切られたパーティションを「パーティションA」、
server_nameが「B」で区切られたパーティションを「パーティションB」とします。

この段階でのテーブルの状態は以下のようになります。

次に、パーティションに区切られ、ソートされたテーブルの各行に対して、合計値を求めていきます。 ここで重要になる考えが「カーソル」です。 簡単にいうと、マウスカーソルと同じで、最初は1行目にカーソルを当て処理を実行し、次に2行目にカーソルを当て処理を実行し、さらに次に3行目にカーソルを当て処理を実行し...という調子で処理を進めていきます。 手続き型言語（JavaやPythonなど）の繰り返し処理（for文,while文）のイメージですね。

以下に各行での合計処理を順番に書いていきます。

・サーバーAの1行目
パーティションAの1行目にカーソルが当たっている状態で、cur_valの合計値がcum_load_valに入っています。

・サーバーAの2行目
パーティションAの2行目にカーソルが当たっている状態で、1行目から2行目までのcur_valの合計値がcum_load_valに入っています。

・サーバーAの3行目
パーティションAの3行目にカーソルが当たっている状態で、1行目から3行目までのcur_valの合計値がcum_load_valに入っています。

パーティションAのカーソルが最終行まで行くと、
パーティションが切り替わり、カーソルがリセットされ、再度1行目から処理を進めていきます。

・サーバーBの1行目
パーティションBの1行目にカーソルが当たっている状態で、cur_valの合計値がcum_load_valに入っています。

・サーバーBの2行目
パーティションBの2行目にカーソルが当たっている状態で、1行目から2行目までのcur_valの合計値がcum_load_valに入っています。

・サーバーBの3行目
パーティションBの3行目にカーソルが当たっている状態で、1行目から3行目までのcur_valの合計値がcum_load_valに入っています。

・サーバーBの4行目
パーティションBの4行目にカーソルが当たっている状態で、1行目から4行目までのcur_valの合計値がcum_load_valに入っています。

このように、各行までの累計値を求めることができます。

また、他にもサーバーごとの使用中の負荷量が大きさをランク付けするようなこともできます。
実行SQL例：

SELECT server_name, load_val,
RANK() OVER(PARTITION BY server_name ORDER BY load_val DESC) AS ranking
FROM ServerLoad;

結果例：

server_name	load_val	ranking
A	1900	1
A	1120	2
A	460	3
B	3400	1
B	800	2
B	530	3
B	160	4

結果例から、サーバーA,サーバーBのランキング1位のみを抽出したい場合は、

SELECT * 
FROM (
	SELECT server_name, load_val,
	RANK() OVER(PARTITION BY server_name ORDER BY load_val DESC) AS ranking
	FROM ServerLoad
) sub
WHERE sub.ranking = 1;

とすると、

server_name	load_val	ranking
A	1900	1
B	3400	1

という結果が得られます。
FROM句のサブクエリから受けた結果をメインクエリで絞り込みをかけることで実現しています。

以上で、説明は終わりになります。
ウィンドウ関数の説明はいかがだったでしょうか。
パーティションごとに区切り、区切ったパーティションの中でソートし、パーティション内でカーソルを移動させながら処理していくというイメージが湧けば、初めは気味悪かったウィンドウ関数も短くかけて便利に感じてきたのではないでしょうか。
本記事では、無名ウィンドウ関数（無名関数のようなもの）を前提に記載を進めましたが、ウィンドウ関数に名前をつけて使用することも可能です。

名前付きウィンドウ関数の例：

SELECT server_name, load_val,
RANK() OVER load_val_ranking AS ranking
FROM ServerLoad
WINDOW load_val_ranking AS (PARTITION BY server_name ORDER BY load_val DESC);

また、今回は記事に書いていませんが、「カーソル」が動く範囲を自分で指定することもできます。
ORDER BY句の後に「ROWS」や「RANGE」というキーワードを書き、続けて「PRECEDING」や「FOLLOWING」でカーソル位置を指定することもできますので、興味がある方は別途お調べいただければと思います。
（『達人に学ぶSQL徹底指南書 第2版』にも出てきます）

個人的な話ですが、最近はWITH RECURSIVE（再帰的に検索し、結果セットを仮想テーブルとしてメモリに保持する）など、新しいSQLを学習する機会が多く、データ操作がより楽しくなりました。
欲しいデータをピシャリと取得できるよう、今後も学習に励みたいと思います。

冒頭でも申し上げましたが、本記事について、誤りやご指摘、お気づきの点がござましたら、コメント等でご教示いただけますようお願いいたします。

参考書籍：『達人に学ぶSQL徹底指南書 第2版』(Amazon Kindle版)
https://amzn.asia/d/efeKM6o