はじめに
ウィンドウ関数について、テストデータを使ってみるととても理解が早かったので記事にしてみました。
ウィンドウ関数とは？
ウィンドウ関数は集合関数を使用した機能です。
一般的な集合関数は後ろにOVER句をつけるとウィンドウ関数になるものが多いです。
ウィンドウ関数は、AVG、MAX、SUMなどの集合関数に制限範囲を加えたものです。
また、集合関数はGROUP BYごとに集計されるので行の数が減ることが多いですが、ウィンドウ関数は対象の行がそのまま残ります。
クエリと結果がある方がわかりやすいので、テストデータを作成して説明します。
テストデータ投入
説明用のテーブルを作成して、適当なデータを投入します。
CREATE TABLE 家計簿 (id INT, 品目 VARCHAR(10), 出費額 INT, 日付 DATE);
INSERT INTO 家計簿 VALUES (1, '食費', 1000, '2020-08-01');
INSERT INTO 家計簿 VALUES (2, '雑費', 3000, '2020-08-10');
INSERT INTO 家計簿 VALUES (3, '食費', 2000, '2020-08-02');
INSERT INTO 家計簿 VALUES (4, '食費', 500, '2020-08-03');
INSERT INTO 家計簿 VALUES (5, '食費', 800, '2020-08-05');
INSERT INTO 家計簿 VALUES (6, '食費', 1500, '2020-08-08');
INSERT INTO 家計簿 VALUES (7, '雑費', 100, '2020-08-06');
INSERT INTO 家計簿 VALUES (8, '食費', 600, '2020-08-11');
SELECT * FROM 家計簿;
+------+--------+-----------+------------+
| id | 品目 | 出費額 | 日付 |
+------+--------+-----------+------------+
| 1 | 食費 | 1000 | 2020-08-01 |
| 2 | 雑費 | 3000 | 2020-08-10 |
| 3 | 食費 | 2000 | 2020-08-02 |
| 4 | 食費 | 500 | 2020-08-03 |
| 5 | 食費 | 800 | 2020-08-05 |
| 6 | 食費 | 1500 | 2020-08-08 |
| 7 | 雑費 | 100 | 2020-08-06 |
| 8 | 雑費 | 600 | 2020-08-11 |
+------+--------+-----------+------------+
食費と雑費しかない家計簿テーブルです。IDは挿入順で日付はバラバラです。
MySQLのバージョンは8.0.21です。
集合関数とウィンドウ関数の違い
集合関数とウィンドウ関数をそれぞれ使って出費額の合計を求めてみます。
集合関数
SELECT SUM(出費額) AS 支出 FROM 家計簿;
+--------+
| 支出 |
+--------+
| 9500 |
+--------+
ウィンドウ関数
SELECT 家計簿.*, SUM(出費額) OVER () AS 支出 FROM 家計簿;
+------+--------+-----------+------------+--------+
| id | 品目 | 出費額 | 日付 | 支出 |
+------+--------+-----------+------------+--------+
| 1 | 食費 | 1000 | 2020-08-01 | 9500 |
| 2 | 雑費 | 3000 | 2020-08-10 | 9500 |
| 3 | 食費 | 2000 | 2020-08-02 | 9500 |
| 4 | 食費 | 500 | 2020-08-03 | 9500 |
| 5 | 食費 | 800 | 2020-08-05 | 9500 |
| 6 | 食費 | 1500 | 2020-08-08 | 9500 |
| 7 | 雑費 | 100 | 2020-08-06 | 9500 |
| 8 | 雑費 | 600 | 2020-08-11 | 9500 |
+------+--------+-----------+------------+--------+
集合関数は一つの行にまとめられたのに対し、ウィンドウ関数は行の数が減っていません。
しかし、どちらも出費の合計を求めることができています。
OVER
ウィンドウ関数にはOVER句が必須です。
OVER句では集計の範囲を指定することができます。
上記のクエリのようにOVER句に何も指定しないと全ての行が対象になります。
PARTITION BY
ウィンドウ関数用のGROUP BYみたいな感じです。
ただ、レコードの集約はされない点が異なります。
下記は集合関数とGROUP BYを使って、品目ごとの支出を求めた場合です。
SELECT 品目, SUM(出費額) AS 支出 FROM 家計簿 GROUP BY 品目;
+--------+--------+
| 品目 | 支出 |
+--------+--------+
| 食費 | 5800 |
| 雑費 | 3700 |
+--------+--------+
品目ごとに行が集約されています。品目の数しか行が残っていません。
それに対して、ウィンドウ関数とPARTITION BYを使った場合が下記です。
SELECT id, 品目, 日付, SUM(出費額) OVER (PARTITION BY 品目) AS 支出 FROM 家計簿;
+------+--------+------------+--------+
| id | 品目 | 日付 | 支出 |
+------+--------+------------+--------+
| 2 | 雑費 | 2020-08-10 | 3700 |
| 7 | 雑費 | 2020-08-06 | 3700 |
| 8 | 雑費 | 2020-08-11 | 3700 |
| 1 | 食費 | 2020-08-01 | 5800 |
| 3 | 食費 | 2020-08-02 | 5800 |
| 4 | 食費 | 2020-08-03 | 5800 |
| 5 | 食費 | 2020-08-05 | 5800 |
| 6 | 食費 | 2020-08-08 | 5800 |
+------+--------+------------+--------+
GROUP BYのように品目ごとに集約されて支出が計算されていますが、行の数は減っていません。
PARTITION BYはGROUP BYと同じように集約に使います。
ORDER BY
OVER句で使われるORDER BYは通常のORDER BY句同様、指定したカラムで順序付けをしますが、集計に影響を与えます。
下はOVER句で日付に対してORDER BYを指定した例です。
SELECT id, 品目, 日付, 出費額, SUM(出費額) OVER (ORDER BY 日付) AS その日までの出費合計
FROM 家計簿;
+------+--------+------------+-----------+--------------------------------+
| id | 品目 | 日付 | 出費額 | その日までの出費合計 |
+------+--------+------------+-----------+--------------------------------+
| 6 | 食費 | 2020-08-01 | 1500 | 1500 |
| 3 | 食費 | 2020-08-02 | 2000 | 3500 |
| 4 | 食費 | 2020-08-03 | 500 | 4000 |
| 5 | 食費 | 2020-08-05 | 800 | 4800 |
| 7 | 雑費 | 2020-08-06 | 100 | 4900 |
| 1 | 食費 | 2020-08-08 | 1000 | 5900 |
| 2 | 雑費 | 2020-08-10 | 3000 | 8900 |
| 8 | 雑費 | 2020-08-11 | 600 | 9500 |
+------+--------+------------+-----------+--------------------------------+
上の例ではその日までの出費の合計を求めています。
ORDER BY句は、行を順番に並べた上で、最初の行から現在行までのみを集計の対象にします。
例えば二つめの行では、
8月1日の1500円 + 8月2日の2000円 = 3500円
という計算をしています。
後述するフレーム句を指定すると集計の範囲を指定することができます。
FRAME
フレーム句を指定すると集計対象行の範囲を指定することができます。
フレーム句には以下の二つが指定できます。
- ROWS
- RANGE
ROWS
ROWSは行数指定です。
下は前一行と現在の行を指定した例です。
SELECT id, 品目, 日付, 出費額,
SUM(出費額) OVER (ORDER BY 日付 ROWS 1 PRECEDING)
AS 前の出費との合計
FROM 家計簿;
+------+--------+------------+-----------+--------------------------+
| id | 品目 | 日付 | 出費額 | 前の出費との合計 |
+------+--------+------------+-----------+--------------------------+
| 6 | 食費 | 2020-08-01 | 1500 | 1500 |
| 3 | 食費 | 2020-08-02 | 2000 | 3500 |
| 4 | 食費 | 2020-08-03 | 500 | 2500 |
| 5 | 食費 | 2020-08-05 | 800 | 1300 |
| 7 | 雑費 | 2020-08-06 | 100 | 900 |
| 1 | 食費 | 2020-08-08 | 1000 | 1100 |
| 2 | 雑費 | 2020-08-10 | 3000 | 4000 |
| 8 | 雑費 | 2020-08-11 | 600 | 3600 |
+------+--------+------------+-----------+--------------------------+
上の例では日付順に並び替えた上で、一行上の行と自分の行とを加算しています。
三行目では、
8月2日の2000円(一つ前の行) + 8月3日の500円(現在の行) = 2500円
という計算がされています。
一行目は前の行が存在しないため、加算が行われていません。
PRECEDINGは前の行を指定しますが、他にも下記のような句を指定できます。
| 指定方法 | 説明 |
|---|---|
| n PRECEDING | nだけ前（小さいほう）へ移動する。nは正の整数。 |
| n FOLLOWING | nだけ後(大きいほう)へ移動する。nは正の整数。 |
| UNBOUNDED PRECEDING | 無制限にさかのぼる方へ移動する。(先頭の行) |
| UNBOUNDED FOLLOWING | 無制限に下る方へ移動する。(末尾の行) |
| CURRENT ROW | 現在行 |
また、BETWEEN句を使うと前後に指定することができます。
SELECT id, 品目, 日付, 出費額,
SUM(出費額) OVER (ORDER BY 日付 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS 前後の一日の合計
FROM 家計簿;
+------+--------+------------+-----------+--------------------------+
| id | 品目 | 日付 | 出費額 | 前後の一日の合計 |
+------+--------+------------+-----------+--------------------------+
| 6 | 食費 | 2020-08-01 | 1500 | 3500 |
| 3 | 食費 | 2020-08-02 | 2000 | 4000 |
| 4 | 食費 | 2020-08-03 | 500 | 3300 |
| 5 | 食費 | 2020-08-05 | 800 | 1400 |
| 7 | 雑費 | 2020-08-06 | 100 | 1900 |
| 1 | 食費 | 2020-08-08 | 1000 | 4100 |
| 2 | 雑費 | 2020-08-10 | 3000 | 4600 |
| 8 | 雑費 | 2020-08-11 | 600 | 3600 |
+------+--------+------------+-----------+--------------------------+
上の例では、日付順に並び替えた上で前後一行と自分の行を加算しています。
二行目では、
8月1日の1500円(一つ前の行) + 8月2日の2000円(現在の行) + 8月3日の500円(一つ後の行)
= 4000円
という計算がされています。
RANGE
RANGEは列の値に基づいて範囲を指定します。
下の例は、1日前と指定の行との出費額の比較をしています。
SELECT id, 品目, 日付, 出費額,
MIN(出費額) OVER (ORDER BY 日付 RANGE BETWEEN interval '1' day PRECEDING
AND interval '1' day PRECEDING)
AS 1日前の出費額
FROM 家計簿;
+------+--------+------------+-----------+---------------------+
| id | 品目 | 日付 | 出費額 | 1日前の出費額 |
+------+--------+------------+-----------+---------------------+
| 6 | 食費 | 2020-08-01 | 1500 | NULL |
| 3 | 食費 | 2020-08-02 | 2000 | 1500 |
| 4 | 食費 | 2020-08-03 | 500 | 2000 |
| 5 | 食費 | 2020-08-05 | 800 | NULL |
| 7 | 雑費 | 2020-08-06 | 100 | 800 |
| 1 | 食費 | 2020-08-08 | 1000 | NULL |
| 2 | 雑費 | 2020-08-10 | 3000 | NULL |
| 8 | 雑費 | 2020-08-11 | 600 | 3000 |
+------+--------+------------+-----------+---------------------+
interval '1' dayは1日を表します。
BETWEEN句で1日前までを指定しています。（BETWEENを省略すると1日前から現在の行までになってしまいます。）
二行目の1日前の出費額には、
8月2日の1日前の8月1日の出費額が求められているのがわかります。
1日前のレコードが存在しない四行目は、NULLになっています。
(MINは特に意味はありません。AVGやSUMを使っても同じ結果になります。)
RANGEは、数値か日付に使われます。そのため、ORDER BY句が文字列の時は使用できません。
WINDOWを使いまわす
OVER句は名前をつけて使いまわすことができます。
今まで紹介してきた書き方は無名構文と言われます。
SELECT id, 日付, 出費額,
SUM(出費額) OVER W AS 前の行との合計,
AVG(出費額) OVER W AS 前の行との平均,
MAX(出費額) OVER W AS 前の行との最大値
FROM 家計簿
WINDOW W AS (ORDER BY 日付 ROWS 1 PRECEDING);
+--------------------------+
| id | 日付 | 出費額 | 前の行との合計 | 前の行との平均 | 前の行との最大値 |
+------+------------+-----------+------------ +-------------+-------------+
| 6 | 2020-08-01 | 1500 | 1500 | 1500 | 1500 |
| 3 | 2020-08-02 | 2000 | 3500 | 1750 | 2000 |
| 4 | 2020-08-03 | 500 | 2500 | 1250 | 2000 |
| 5 | 2020-08-05 | 800 | 1300 | 650 | 800 |
| 7 | 2020-08-06 | 100 | 900 | 450 | 800 |
| 1 | 2020-08-08 | 1000 | 1100 | 550 | 1000 |
| 2 | 2020-08-10 | 3000 | 4000 | 2000 | 3000 |
| 8 | 2020-08-11 | 600 | 3600 | 1800 | 3000 |
+------+------------+-----------+-----------------------+-----------------------+--------------------------+
WINDOW句を指定することでウィンドウに名前をつけることができます。
ただ、この書き方を受け付けないDBMSもあるので注意が必要です。