はじめに
データ分析における重要な関数そうなので、何章かに分けて、Window関数についてまとめています。
前回の記事は👇
今回もいくつかのサンプルを用意して説明します。
今回は、Window関数の機能一覧の紹介と、Window関数の典型的な利用ケースである移動平均を紹介していきます。
今回もSQLの実行は 「DB Fiddle」というWebサービスを使ってみます。
Window関数の機能一覧の紹介
Window関数の機能を1枚絵で俯瞰してみましょう。
以下の論文から引用してます。
Efficient processing of window functions in analytical SQL queries | Request PDF
3つの機能から成り立っていることが分かります
- PARTITION BY 句によるレコード集合のカット
- ORDER BY 句による順序づけ
- フレーム句による現在の行を中心とした範囲の定義
Window関数で移動平均を算出
当日と前日の単純移動平均を計算してみましょう
参考
Schema (MySQL v8.0)
CREATE TABLE temperatures (
id INT NOT NULL PRIMARY KEY auto_increment,
date DATE,
city_name TEXT,
avg_temp FLOAT
);
INSERT INTO temperatures VALUES
(1, '2020-12-01', '東京', 10.0),
(2, '2020-12-02', '東京', 9.0),
(3, '2020-12-03', '東京', 8.0),
(4, '2020-12-04', '東京', 7.0),
(5, '2020-12-01', '愛知', 11.0),
(6, '2020-12-02', '愛知', 10.0),
(7, '2020-12-03', '愛知', 9.0),
(8, '2020-12-04', '愛知', 8.0);
Query
SELECT
date AS 日付,
city_name,
AVG(avg_temp) OVER avg_temp_2days AS '移動平均2日間'
FROM temperatures
WINDOW avg_temp_2days AS (
PARTITION BY city_name
ORDER BY id ASC
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
);
ウィンドウ関数のフレーム句で使用できるオプション
- ROWS: 移動単位を行で指定
- n PRECEDING: nだけ前（小さい方）へ移動する
- CURRENT ROW: 現在行
Results
| 日付 | city_name | 移動平均2日間 |
|---|---|---|
| 2020-12-01 | 愛知 | 11 |
| 2020-12-02 | 愛知 | 10.5 |
| 2020-12-03 | 愛知 | 9.5 |
| 2020-12-04 | 愛知 | 8.5 |
| 2020-12-01 | 東京 | 10 |
| 2020-12-02 | 東京 | 9.5 |
| 2020-12-03 | 東京 | 8.5 |
| 2020-12-04 | 東京 | 7.5 |
参照
29 - 35p
アウトプット100本ノック実施中