【SQL】ウィンドウ関数


特定の範囲のデータに対して計算などを行えるウィンドウ関数についてまとめる。

ウィンドウ関数は別名分析関数またはOLAP(Online Analytical Processing)とも呼ばれる。


## 集約関数
例えば、下記のような勤怠表テーブルがあったとする。
|部署|社員|労働時間|
|--|--|--|
|営業部|Aさん|150|
|営業部|Bさん|130|
|営業部|Cさん|140|
|営業部|Dさん|160|
|システム部|Eさん|130|
|システム部|Fさん|170|
|システム部|Gさん|150|

労働時間の平均を取りたい場合、まずGROUP BYを使うことが思いつく。

### GROUP BYを使用した場合

```sql
SELECT 
   部署,
   AVG(労働時間)AS 部署平均労働時間
FROM 勤怠表
GROUP BY 部署
```
結果：
|部署|部署平均労働時間|
|--|--|
|営業部|145|
|システム部|150|

一方、部署や社員の列は残しつつ平均労働時間を別列として持ちたいことがある。
その場合、以下のウィンドウ関数を使うことができる。

### ウィンドウ関数を使用した場合

```sql
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|

ウィンドウ関数を用いることで、部署平均として列を持つことができ、各社員ごとに値を持つできる。

```sql
AVG(労働時間)
```
平均の労働時間を集計。
```sql
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|

ここで、前月労働時間の列を作る場合、下記のように書ける。


```sql
SELECT 
  社員,
  年月,
  労働時間,
  前月の労働時間
  LAG(労働時間) OVER (PARTITION BY 社員 ORDER BY 年月) AS 前月の労働時間
FROM 社員別月間労働時間テーブル
```
結果：
<img width="430" alt="スクリーンショット 2024-06-02 10.43.24.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/496614/aa5dd494-b3a3-900d-7583-309b7fb80641.png">

```sql
LAG(労働時間) OVER (PARTITION BY 社員 ORDER BY 年月) AS 前月の労働時間
```
ここでは、社員毎に年月順に並べ、1つ前の労働時間を持ってきて新たな列に値をセットしている。

## RANK関数

次にRANK関数を用いて順位づけしてみる。

例：勤怠テーブル
|部署|社員|労働時間|
|--|--|--|
|営業部|Aさん|150|
|営業部|Bさん|130|
|営業部|Cさん|140|
|営業部|Dさん|160|
|システム部|Eさん|130|
|システム部|Fさん|170|
|システム部|Gさん|150|

```sql
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|

```sql
SELECT
   商品,
   月間売上
   NTILE(3)OVER(ORDER BY 週間売上 DESC) AS ランク
FROM 売上テーブル ORDER BY 週間売上 DESC
```

<img width="230" alt="スクリーンショット 2024-06-02 11.31.00.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/496614/b647d8f4-84e0-ef0c-3693-9436fa3db9db.png">

```sql
NTILE(3)OVER(ORDER BY 週間売上 DESC) AS ランク
```
1、均等に指定した数に分ける（今回の場合3)
2、均等分けしたグループに対し順位をつける


```sql
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|

```sql
SELECT 
   年月,
   月間売上,
   AVG(月間売上)OVER(ORDER BY年月　ROWS 
                    BETWEEN 2 PREDEDING AND CURRENT ROW) AS 移動平均
FROM 月間売上テーブル
```
結果：
<img width="320" alt="スクリーンショット 2024-06-02 12.00.35.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/496614/1522e820-399d-86fa-a3f7-a27d8bd01cdf.png">

```sql
BETWEEN 2 PREDEDING AND CURRENT ROW
```
ここではBETWEEN 開始地点 AND 終了地点で範囲指定している。
具体的にはn PRECEEDINGでn行前を指定できるので、今回は2行前、つまり、2023年3月をCURRENT ROWとすると2023年1月を指定することになる。

時系列分析の詳細については、過去にまとめてますので下記参照ください

https://qiita.com/sho91/items/78ad45213868bdccfcce