特定の範囲のデータに対して計算などを行えるウィンドウ関数についてまとめる。
ウィンドウ関数は別名分析関数またはOLAP(Online Analytical Processing)とも呼ばれる。
集約関数
例えば、下記のような勤怠表テーブルがあったとする。
| 部署 | 社員 | 労働時間 |
|---|---|---|
| 営業部 | Aさん | 150 |
| 営業部 | Bさん | 130 |
| 営業部 | Cさん | 140 |
| 営業部 | Dさん | 160 |
| システム部 | Eさん | 130 |
| システム部 | Fさん | 170 |
| システム部 | Gさん | 150 |
労働時間の平均を取りたい場合、まずGROUP BYを使うことが思いつく。
GROUP BYを使用した場合
SELECT
部署,
AVG(労働時間)AS 部署平均労働時間
FROM 勤怠表
GROUP BY 部署
結果：
| 部署 | 部署平均労働時間 |
|---|---|
| 営業部 | 145 |
| システム部 | 150 |
一方、部署や社員の列は残しつつ平均労働時間を別列として持ちたいことがある。
その場合、以下のウィンドウ関数を使うことができる。
ウィンドウ関数を使用した場合
SELECT
部署,
社員,
労働時間,
AVG(労働時間) OVER (PARTITION BY 部署) AS 部署平均
FROM 勤怠表
結果：
| 部署 | 社員 | 労働時間 | 部署平均 |
|---|---|---|---|
| 営業部 | Aさん | 150 | 145 |
| 営業部 | Bさん | 130 | 145 |
| 営業部 | Cさん | 140 | 145 |
| 営業部 | Dさん | 160 | 145 |
| システム部 | Eさん | 130 | 150 |
| システム部 | Fさん | 170 | 150 |
| システム部 | Gさん | 150 | 150 |
ウィンドウ関数を用いることで、部署平均として列を持つことができ、各社員ごとに値を持つできる。
AVG(労働時間)
平均の労働時間を集計。
OVER (PARTITION BY 部署)
その単位として部署ごとに分けている。
LAG関数
LAG関数を使うことで1つ前のデータを参照することができる。
例：社員別月間労働時間テーブル
| 社員 | 年月 | 労働時間 |
|---|---|---|
| Aさん | 2024年1月 | 150 |
| Aさん | 2024年2月 | 180 |
| Aさん | 2024年3月 | 140 |
| Bさん | 2024年1月 | 160 |
| Bさん | 2024年2月 | 170 |
| Bさん | 2024年3月 | 150 |
ここで、前月労働時間の列を作る場合、下記のように書ける。
SELECT
社員,
年月,
労働時間,
前月の労働時間
LAG(労働時間) OVER (PARTITION BY 社員 ORDER BY 年月) AS 前月の労働時間
FROM 社員別月間労働時間テーブル
結果：
LAG(労働時間) OVER (PARTITION BY 社員 ORDER BY 年月) AS 前月の労働時間
ここでは、社員毎に年月順に並べ、1つ前の労働時間を持ってきて新たな列に値をセットしている。
RANK関数
次にRANK関数を用いて順位づけしてみる。
例：勤怠テーブル
| 部署 | 社員 | 労働時間 |
|---|---|---|
| 営業部 | Aさん | 150 |
| 営業部 | Bさん | 130 |
| 営業部 | Cさん | 140 |
| 営業部 | Dさん | 160 |
| システム部 | Eさん | 130 |
| システム部 | Fさん | 170 |
| システム部 | Gさん | 150 |
SELECT
部署,
社員,
労働時間,
RANK()OVER (PARTITION BY 部署 ORDER BY 労働時間 DESC) AS 順位
FROM 勤怠テーブル ORDER BY 部署,労働時間 DESC
結果：
| 部署 | 社員 | 労働時間 | 順位 |
|---|---|---|---|
| 営業部 | Dさん | 160 | 1 |
| 営業部 | Aさん | 150 | 2 |
| 営業部 | Cさん | 140 | 3 |
| 営業部 | Bさん | 130 | 4 |
| システム部 | Fさん | 170 | 5 |
| システム部 | Gさん | 150 | 6 |
| システム部 | Eさん | 130 | 7 |
DENSE_RANK関数
DENSE_RANK関数とRANK関数との違いは順位の付け方。
例えば、下記のような労働時間だった場合、
| 部署 | 社員 | 労働時間 |
|---|---|---|
| 営業部 | Dさん | 160 |
| 営業部 | Aさん | 150 |
| 営業部 | Cさん | 150 |
| 営業部 | Bさん | 130 |
RANK関数を使うと、Bさんの順位は4位となる。
| 部署 | 社員 | 労働時間 | 順位 |
|---|---|---|---|
| 営業部 | Dさん | 160 | 1 |
| 営業部 | Aさん | 150 | 2 |
| 営業部 | Cさん | 150 | 2 |
| 営業部 | Bさん | 130 | 4 |
DENSE_RANK関数を使うと、Bさんの順位は上に詰めて3位となる。
| 部署 | 社員 | 労働時間 | 順位 |
|---|---|---|---|
| 営業部 | Dさん | 160 | 1 |
| 営業部 | Aさん | 150 | 2 |
| 営業部 | Cさん | 150 | 2 |
| 営業部 | Bさん | 130 | 3 |
NTILE関数
NTILE関数を使うと等分して集計できる。
例：売上テーブル
| 商品 | 週間売上 |
|---|---|
| A | 300 |
| B | 400 |
| C | 600 |
| D | 250 |
| E | 400 |
| F | 350 |
| G | 800 |
| H | 100 |
| I | 150 |
| J | 200 |
SELECT
商品,
月間売上
NTILE(3)OVER(ORDER BY 週間売上 DESC) AS ランク
FROM 売上テーブル ORDER BY 週間売上 DESC
NTILE(3)OVER(ORDER BY 週間売上 DESC) AS ランク
1、均等に指定した数に分ける（今回の場合3)
2、均等分けしたグループに対し順位をつける
FROM 売上表 ORDER BY 週間売上 DESC
３、最後に週間売上順に並び替えている。
移動平均
最後に移動平均を計算してみる。
移動平均の計算には以下が使用できる。
| 開始地点、終了地点に使用 | |
|---|---|
| CURRENT ROW | 現在の行 |
| n PRECEEDING | n行前 |
| n FOLLOWING | n行後 |
| UNBOUNDED PRECEEDING | 先頭の行 |
| UNBOUNDED FOLLOWING | 末尾の行 |
例：月間売上表
| 年月 | 月間売上 |
|---|---|
| 2023年1月 | 300 |
| 2023年2月 | 600 |
| 2023年3月 | 400 |
| 2023年4月 | 500 |
| 2023年5月 | 600 |
| 2023年6月 | 700 |
| 2023年7月 | 750 |
SELECT
年月,
月間売上,
AVG(月間売上)OVER(ORDER BY年月 ROWS
BETWEEN 2 PREDEDING AND CURRENT ROW) AS 移動平均
FROM 月間売上テーブル
結果：
BETWEEN 2 PREDEDING AND CURRENT ROW
ここではBETWEEN 開始地点 AND 終了地点で範囲指定している。
具体的にはn PRECEEDINGでn行前を指定できるので、今回は2行前、つまり、2023年3月をCURRENT ROWとすると2023年1月を指定することになる。
時系列分析の詳細については、過去にまとめてますので下記参照ください