メモ > 技術 > データベース: MySQL > ウインドウ関数
ウインドウ関数
MySQL の Window 関数を完全に理解する - Qiita
https://qiita.com/ngyuki/items/c35dd314815939ffbeb6
以下、MariaDB 10.4.8 で検証
■簡単な例
CREATE TABLE users(
uid INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
gid INT NOT NULL,
value INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'ユーザ';
INSERT INTO users VALUES( 1, 100, 1);
INSERT INTO users VALUES( 2, 200, 2);
INSERT INTO users VALUES( 3, 100, 10);
INSERT INTO users VALUES( 4, 200, 20);
INSERT INTO users VALUES( 5, 100, 100);
INSERT INTO users VALUES( 6, 200, 200);
INSERT INTO users VALUES( 7, 100, 1000);
INSERT INTO users VALUES( 8, 200, 2000);
INSERT INTO users VALUES( 9, 100, 10000);
INSERT INTO users VALUES(10, 200, 20000);
GROUP BY で gid ごとの合計を表示した場合
SELECT
gid,
SUM(value) AS total
FROM
users
GROUP BY
gid
;
/*
+-----+-------+
| gid | total |
+-----+-------+
| 100 | 11111 |
| 200 | 22222 |
+-----+-------+
*/
以下はWindow関数を使用した例
「PARTITION BY gid」によって、行の範囲を指定している
SELECT
uid,
gid,
value,
SUM(value) OVER (PARTITION BY gid) AS total
FROM
users
;
/*
+-----+-----+-------+-------+
| uid | gid | value | total |
+-----+-----+-------+-------+
| 7 | 100 | 1000 | 11111 |
| 1 | 100 | 1 | 11111 |
| 9 | 100 | 10000 | 11111 |
| 3 | 100 | 10 | 11111 |
| 5 | 100 | 100 | 11111 |
| 8 | 200 | 2000 | 22222 |
| 2 | 200 | 2 | 22222 |
| 10 | 200 | 20000 | 22222 |
| 4 | 200 | 20 | 22222 |
| 6 | 200 | 200 | 22222 |
+-----+-----+-------+-------+
*/
行の範囲をしていなければ、全体を対象に処理される
SELECT
uid,
gid,
value,
SUM(value) OVER () AS total
FROM
users
;
/*
+-----+-----+-------+-------+
| uid | gid | value | total |
+-----+-----+-------+-------+
| 1 | 100 | 1 | 33333 |
| 2 | 200 | 2 | 33333 |
| 3 | 100 | 10 | 33333 |
| 4 | 200 | 20 | 33333 |
| 5 | 100 | 100 | 33333 |
| 6 | 200 | 200 | 33333 |
| 7 | 100 | 1000 | 33333 |
| 8 | 200 | 2000 | 33333 |
| 9 | 100 | 10000 | 33333 |
| 10 | 200 | 20000 | 33333 |
+-----+-----+-------+-------+
*/
以下はウインドウを明示的に定義して使う例（定義しなければ暗黙的に定義されたことになる）
Oracleでは明示的に定義することができないので、暗黙的に定義される方法の方が汎用性が高い
SELECT
uid,
gid,
value,
SUM(value) OVER w AS total
FROM
users
WINDOW
w AS (PARTITION BY gid)
;
/*
+-----+-----+-------+-------+
| uid | gid | value | total |
+-----+-----+-------+-------+
| 7 | 100 | 1000 | 11111 |
| 1 | 100 | 1 | 11111 |
| 9 | 100 | 10000 | 11111 |
| 3 | 100 | 10 | 11111 |
| 5 | 100 | 100 | 11111 |
| 8 | 200 | 2000 | 22222 |
| 2 | 200 | 2 | 22222 |
| 10 | 200 | 20000 | 22222 |
| 4 | 200 | 20 | 22222 |
| 6 | 200 | 200 | 22222 |
+-----+-----+-------+-------+
*/
OVER 句には ORDER BY も指定できる
ORDER BY は Window 関数がパーティションの中で行を処理する順番を指定できる
SELECT
uid,
gid,
value,
ROW_NUMBER() OVER (PARTITION BY gid ORDER BY value) AS num
FROM
users
;
/*
+-----+-----+-------+-----+
| uid | gid | value | num |
+-----+-----+-------+-----+
| 1 | 100 | 1 | 1 |
| 3 | 100 | 10 | 2 |
| 5 | 100 | 100 | 3 |
| 7 | 100 | 1000 | 4 |
| 9 | 100 | 10000 | 5 |
| 2 | 200 | 2 | 1 |
| 4 | 200 | 20 | 2 |
| 6 | 200 | 200 | 3 |
| 8 | 200 | 2000 | 4 |
| 10 | 200 | 20000 | 5 |
+-----+-----+-------+-----+
*/
■フレームの概念
前述の ORDER BY を SUM で使用すると次のような結果になる
gid で分割されたパーティションの中を value の昇順に並べ、パーティションの先頭から現在行までが順に集計される
SELECT
uid,
gid,
value,
SUM(value) OVER (PARTITION BY gid ORDER BY value) AS total
FROM
users
;
/*
+-----+-----+-------+-------+
| uid | gid | value | total |
+-----+-----+-------+-------+
| 1 | 100 | 1 | 1 |
| 3 | 100 | 10 | 11 |
| 5 | 100 | 100 | 111 |
| 7 | 100 | 1000 | 1111 |
| 9 | 100 | 10000 | 11111 |
| 2 | 200 | 2 | 2 |
| 4 | 200 | 20 | 22 |
| 6 | 200 | 200 | 222 |
| 8 | 200 | 2000 | 2222 |
| 10 | 200 | 20000 | 22222 |
+-----+-----+-------+-------+
*/
この「パーティションの先頭から現在行まで」のような範囲のことを「フレーム」と呼ぶ
フレームの範囲も OVER 句で指定できる。次の「ROWS 〜」の部分がフレームの指定
フレームの終了位置はデフォルトで現在行なので、ROWS CURRENT ROW だけ指定すると「現在行〜現在行」となる
SELECT
uid,
gid,
value,
SUM(value) OVER (PARTITION BY gid ORDER BY value) AS total,
SUM(value) OVER (PARTITION BY gid ORDER BY value ROWS CURRENT ROW) AS 'CURRENT ROW',
SUM(value) OVER (PARTITION BY gid ORDER BY value ROWS UNBOUNDED PRECEDING) AS 'UNBOUNDED PRECEDING',
SUM(value) OVER (PARTITION BY gid ORDER BY value ROWS 1 PRECEDING) AS '1 PRECEDING'
FROM
users
;
/*
+-----+-----+-------+-------+-------------+---------------------+-------------+
| uid | gid | value | total | CURRENT ROW | UNBOUNDED PRECEDING | 1 PRECEDING |
+-----+-----+-------+-------+-------------+---------------------+-------------+
| 1 | 100 | 1 | 1 | 1 | 1 | 1 |
| 3 | 100 | 10 | 11 | 10 | 11 | 11 |
| 5 | 100 | 100 | 111 | 100 | 111 | 110 |
| 7 | 100 | 1000 | 1111 | 1000 | 1111 | 1100 |
| 9 | 100 | 10000 | 11111 | 10000 | 11111 | 11000 |
| 2 | 200 | 2 | 2 | 2 | 2 | 2 |
| 4 | 200 | 20 | 22 | 20 | 22 | 22 |
| 6 | 200 | 200 | 222 | 200 | 222 | 220 |
| 8 | 200 | 2000 | 2222 | 2000 | 2222 | 2200 |
| 10 | 200 | 20000 | 22222 | 20000 | 22222 | 22000 |
+-----+-----+-------+-------+-------------+---------------------+-------------+
*/
■ウインドウ関数の利用例（移動平均）
ウインドウ関数の典型的な利用ケースである、移動平均を求めてみる
移動平均を使えば、長期的なデータの変化を可視化することができる
算術平均
・集合のすべての値を足して、その集合の要素数で割った値
・その集合が、どのようなものかを表すことができる（例：テストの平均点）
移動平均
・直近で取得したデータポイント群で平均を計算した値
・デコボコの多いグラフをなめらかにして、傾向を把握しやすくすることができる
MySQL の Window 関数を完全に理解する - Qiita
https://qiita.com/ngyuki/items/c35dd314815939ffbeb6
SQLのWindow関数で移動平均を求める - Qiita
https://qiita.com/omokawa_yasu/items/abb8624fc57a9f4c427c
【小ネタ】Window関数で移動平均を算出する【SQL】 - Qiita
https://qiita.com/tmiki/items/739457f5df27a306a36b
32-3. 時系列データと移動平均 | 統計学の時間 | 統計WEB
https://bellcurve.jp/statistics/course/12933.html
移動平均とは?活用するメリットやエクセルでの計算方法を紹介!｜いちばんやさしい、医療統計
https://best-biostatistics.com/summary/idou-heikinn.html
[SQL] 移動平均を算出 〜Window関数を使って変動傾向を掴む〜 | DevelopersIO
https://dev.classmethod.jp/articles/sql-moving-average/
CREATE TABLE profits(
day INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
value INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '日ごとの売上';
INSERT INTO profits VALUES( 1, 57);
INSERT INTO profits VALUES( 2, 47);
INSERT INTO profits VALUES( 3, 122);
INSERT INTO profits VALUES( 4, 104);
INSERT INTO profits VALUES( 5, 34);
INSERT INTO profits VALUES( 6, 65);
INSERT INTO profits VALUES( 7, 38);
INSERT INTO profits VALUES( 8, 39);
INSERT INTO profits VALUES( 9, 43);
INSERT INTO profits VALUES(10, 102);
INSERT INTO profits VALUES(11, 87);
INSERT INTO profits VALUES(12, 58);
INSERT INTO profits VALUES(13, 43);
INSERT INTO profits VALUES(14, 71);
INSERT INTO profits VALUES(15, 45);
INSERT INTO profits VALUES(16, 43);
INSERT INTO profits VALUES(17, 142);
INSERT INTO profits VALUES(18, 101);
INSERT INTO profits VALUES(19, 58);
INSERT INTO profits VALUES(20, 67);
INSERT INTO profits VALUES(21, 48);
INSERT INTO profits VALUES(22, 65);
INSERT INTO profits VALUES(23, 75);
INSERT INTO profits VALUES(24, 123);
INSERT INTO profits VALUES(25, 134);
INSERT INTO profits VALUES(26, 67);
INSERT INTO profits VALUES(27, 63);
INSERT INTO profits VALUES(28, 63);
INSERT INTO profits VALUES(29, 76);
INSERT INTO profits VALUES(30, 48);
SELECT
day,
value,
AVG(value) OVER (
ORDER BY
day
ROWS
BETWEEN 4 PRECEDING AND CURRENT ROW
) AS `Moving AVG`
FROM
profits
;
/*
+-----+-------+------------+
| day | value | Moving AVG |
+-----+-------+------------+
| 1 | 57 | 57.0000 |
| 2 | 47 | 52.0000 |
| 3 | 122 | 75.3333 |
| 4 | 104 | 82.5000 |
| 5 | 34 | 72.8000 |
| 6 | 65 | 74.4000 |
| 7 | 38 | 72.6000 |
| 8 | 39 | 56.0000 |
| 9 | 43 | 43.8000 |
| 10 | 102 | 57.4000 |
| 11 | 87 | 61.8000 |
| 12 | 58 | 65.8000 |
| 13 | 43 | 66.6000 |
| 14 | 71 | 72.2000 |
| 15 | 45 | 60.8000 |
| 16 | 43 | 52.0000 |
| 17 | 142 | 68.8000 |
| 18 | 101 | 80.4000 |
| 19 | 58 | 77.8000 |
| 20 | 67 | 82.2000 |
| 21 | 48 | 83.2000 |
| 22 | 65 | 67.8000 |
| 23 | 75 | 62.6000 |
| 24 | 123 | 75.6000 |
| 25 | 134 | 89.0000 |
| 26 | 67 | 92.8000 |
| 27 | 63 | 92.4000 |
| 28 | 63 | 90.0000 |
| 29 | 76 | 80.6000 |
| 30 | 48 | 63.4000 |
+-----+-------+------------+
*/
例えば7dayの移動平均である「72.6000」は、以下のように求められている
つまり、現在の行（CURRENT ROW）とその前の4行（4 PRECEDING）の計5行から平均化した値を算出し、傾向を把握しやすくしている
(122 + 104 + 34 + 65 + 38) / 5 = 72.6