MYSQL WINDOW関数を改めて学びなおす

# WINDOW関数を簡単に説明

通常の集計関数は行をグループ化して集計するが
window関数を使用するとグループ化することなく行ごとに集計できる。

# 例
下記のような飲料テーブルみたいなデータがあるとする
```SQL
SELECT * FROM test_drink;
```

| id |  type |  price |  date |  maker |
|:-------:|:---------|------:|------:|------:|
| 1 |  coffee |  120 |  2022-01-10 |  inu |
| 2 |  coffee |  130 |  2022-01-10 |  neko |
| 3 |  coffee |  140 |  2022-01-10 |  uma |
| 4 |  water |  200 |  2022-01-10 |  neko |
| 5 |  water |  220 |  2022-01-10 |  inu |
| 6 |  water |  240 |  2022-01-10 |  uma |
| 7 |  energy |  300 |  2022-01-10 |  saru |
| 8 |  energy |  330 |  2022-01-10 |  saru |
| 9 |  energy |  350 |  2022-01-10 |  saru |
| 10 |  tea |  410 |  2022-01-10 |  inu |
| 11 |  tea |  420 |  2022-01-10 |  uma |
| 12 |  tea |  430 |  2022-01-10 |  neko |

#### 単純に集計してみる

```SQL:値段の総合計
SELECT SUM(price) FROM test_drink;
```

|  SUM(price) |
|:-------:|
| 3290 |

#### type毎に集計

```SQL:typeごとの合計
SELECT type, sum(price) FROM test_drink group by type;
```

| type |  sum(price) |
|:-------:|:-------:|
| coffee |  390 |
| water |  660 |
| energy |  980 |
| tea |  1260 |

#### window関数を使用して行はそのままに前述の合計を出す

```SQL:window関数を使用
SELECT 
	*,
    SUM(price) over() AS total_price,
    SUM(price) OVER(PARTITION BY type) AS type_total_price
FROM test_drink
order by id;
```

| id |  type |  price |  date |  maker |  total_price |  type_total_price |
|:-------:|:---------|------:|------:|------:|------:|------:|
| 1 |  coffee |  120 |  2022-01-10 |  inu |  3290 |  390 |
| 2 |  coffee |  130 |  2022-01-10 |  neko |  3290 |  390 |
| 3 |  coffee |  140 |  2022-01-10 |  uma |  3290 |  390 |
| 4 |  water |  200 |  2022-01-10 |  neko |  3290 |  660 |
| 5 |  water |  220 |  2022-01-10 |  inu |  3290 |  660 |
| 6 |  water |  240 |  2022-01-10 |  uma |  3290 |  660 |
| 7 |  energy |  300 |  2022-01-10 |  saru |  3290 |  980 |
| 8 |  energy |  330 |  2022-01-10 |  saru |  3290 |  980 |
| 9 |  energy |  350 |  2022-01-10 |  saru |  3290 |  980 |
| 10 |  tea |  410 |  2022-01-10 |  inu |  3290 |  1260 |
| 11 |  tea |  420 |  2022-01-10 |  uma |  3290 |  1260 |
| 12 |  tea |  430 |  2022-01-10 |  neko |  3290 |  1260 |

#### 行はそのままに全price合計、type毎の合計が出せる
### 上記window関数の説明
**<font color="blue">OVER(PARTITION BY type)</font>**
**OVER** : クエリーの結果に対してoverを使用することでwindow関数が適用される。
**PARTITION BY** : 指定することによってパーティション化される
※パーティショニングとは別で行を単一にまとめないGROUP BYみたいなもの
前述total_price : OVER()※空なので結果セット全体に対しての合計
前述type_total_price : OVER(PARTITION BY type) ※typeでパーティション(group)化して合計

#### よく順位を出すときに使われるROW_NUMBER

こんなデータを用意
| id |  name |  type |  price |
|:-------:|:---------|------:|------:
| 1 |  コーヒー牛乳 |  coffee |  120 |
| 2 |  ブラックコーヒー |  coffee |  410 |
| 3 |  カフェラテ |  coffee |  140 |
| 4 |  硬水 |  water |  150 |
| 5 |  軟水 |  water |  220 |
| 6 |  南アルプス |  water |  410 |
| 7 |  モンスター |  energy |  300 |
| 8 |  デカビタ |  energy |  120 |
| 9 |  レッドブル |  energy |  350 |
| 10 |  お茶 |  tea |  250 |
| 11 |  紅茶 |  tea |  220 |
| 12 |  麦茶 |  tea |  430 |

ここで順位を価格の安い順を出すためにいろんなwindow関数を使用
```SQL:順位の出し方
SELECT
	id,
    name,
    type,
	price,
    ROW_NUMBER() OVER(ORDER BY price) AS `ROW_NUMBER`,
    RANK() OVER(ORDER BY price) AS `RANK`,
    DENSE_RANK() OVER(ORDER BY price) AS `DENSE_RANK`
FROM test_drink
order by price, id;
```
#### 結果
| id |  name |  type |  price |  ROW_NUMBER |  RANK |  DENSE_RANK |
|:-------:|:---------|------:|------:|------:|------:|------:|
| 1 |  コーヒー牛乳 |  coffee |  120 |  1 |  1 |  1 |
| 8 |  デカビタ |  energy |  120 |  2 |  1 |  1 |
| 3 |  カフェラテ |  coffee |  140 |  3 |  3 |  2 |
| 4 |  硬水 |  water |  150 |  4 |  4 |  3 |
| 5 |  軟水 |  water |  220 |  5 |  5 |  4 |
| 11 |  紅茶 |  tea |  220 |  6 |  5 |  4 |
| 10 |  お茶 |  tea |  250 |  7 |  7 |  5 |
| 7 |  モンスター |  energy |  300 |  8 |  8 |  6 |
| 9 |  レッドブル |  energy |  350 |  9 |  9 |  7 |
| 2 |  ブラックコーヒー |  coffee |  410 |  10 |  10 |  8 |
| 6 |  南アルプス |  water |  410 |  11 |  10 |  8 |
| 12 |  麦茶 |  tea |  430 |  12 |  12 |  9 |

#### 結果
**ROW_NUMBER** : あくまで現在の行数を出す関数のため、120円が2つあっても順位に差が出ている
**RANK** : 同率の順位がいた場合は次の順位がスキップされる　例だと2位がいない
**DENSE_RANK** : 同率の順位がいた場合、次の順位がスキップされない　例だと2位がいる
※順位を出すときは目的にあった関数を使う

#### WINDOW関数におけるORDER BY
order by と聞くと単純に並び替えを想像するが、並び替えかつ処理する順番が指定される
window関数ではパーティション化された先頭行から現在行までの対象を指定する

### 例
```SQL:順位の出し方
SELECT
	id,
    name,
    type,
	price,
    SUM(price) OVER(PARTITION BY type ORDER BY price) AS sum_price
FROM test_drink
```

| id |  name |  type |  price |  sum_price |
|:-------:|:---------|------:|------:|------:
| 1 |  コーヒー牛乳 |  coffee |  120 |  120 |
| 3 |  カフェラテ |  coffee |  140 |  260 |
| 2 |  ブラックコーヒー |  coffee |  410 |  670 |
| 8 |  デカビタ |  energy |  120 |  120 |
| 7 |  モンスター |  energy |  300 |  420 |
| 9 |  レッドブル |  energy |  350 |  770 |
| 11 |  紅茶 |  tea |  220 |  220 |
| 10 |  お茶 |  tea |  250 |  470 |
| 12 |  麦茶 |  tea |  430 |  900 |
| 4 |  硬水 |  water |  150 |  150 |
| 5 |  軟水 |  water |  220 |  370 |
| 6 |  南アルプス |  water |  410 |  780 |

上記はtypeでパーティション化し価格順で並び替え合計するという動きをする
coffee時はprice順で並び替え、先頭から現在の行までの合計をだしている
id = 2のブラックコーヒーはtypeでパーティションされprice順に並び変えたので
コーヒー牛乳の120 + カフェラテの140 + ブラックコーヒーの410を合計した数値となる

#### ORDER BYの範囲指定
ORDER BY で指定された範囲のことはフレーム呼ばれる。ROWSによりフレームの範囲も指定できる。

```SQL:フレームの指定
SELECT
	id,
    name,
    type,
	price,
    SUM(price) OVER(PARTITION BY type ORDER BY price) AS sum_price,
    SUM(price) OVER(PARTITION BY type ORDER BY price ROWS 1 PRECEDING) AS before_total_price
FROM test_drink
```

| id |  name |  type |  price |  sum_price |  before_total_price |
|:-------:|:--------|:--------|:--------|:--------|:--------|
| 1 |  コーヒー牛乳 |  coffee |  120 |  120 |  120 |
| 3 |  カフェラテ |  coffee |  140 |  260 |  260 |
| 2 |  ブラックコーヒー |  coffee |  410 |  670 |  550 |
| 8 |  デカビタ |  energy |  120 |  120 |  120 |
| 7 |  モンスター |  energy |  300 |  420 |  420 |
| 9 |  レッドブル |  energy |  350 |  770 |  650 |
| 11 |  紅茶 |  tea |  220 |  220 |  220 |
| 10 |  お茶 |  tea |  250 |  470 |  470 |
| 12 |  麦茶 |  tea |  430 |  900 |  680 |
| 4 |  硬水 |  water |  150 |  150 |  150 |
| 5 |  軟水 |  water |  220 |  370 |  370 |
| 6 |  南アルプス |  water |  410 |  780 |  630 |

先ほどの例にbefore_total_priceを加えた例だが、rowsに1 PRECEDINGを指定している。
N PRECEDINGはN個前のデータを開始位置にしているため、1つ前のpriceと現在のpriceの合計になっている。
このデータだと使い道がなさそうだが、例えば時間差を出すときとかに使えそう。
他のフレーム操作関数
**CURRENT ROW** : 現在行
**UNBOUNDED PRECEDING** : パーティションの先頭
**UNBOUNDED FOLLOWING** : パーティションの終端
**N PRECEDING** : 現在行のN行前
**N FOLLOWING** : 現在行のN行後

#### その他window関数の使い方

| # id |  name |  type |  price |  date |  maker |
|:-------:|:--------|:--------|:--------|:--------|:--------|
| 1 |  コーヒー牛乳 |  coffee |  120 |  2022-01-10 |  inu |
| 2 |  ブラックコーヒー |  coffee |  410 |  2022-01-10 |  neko |
| 3 |  カフェラテ |  coffee |  140 |  2022-01-13 |  uma |
| 4 |  硬水 |  water |  150 |  2022-01-12 |  neko |
| 5 |  軟水 |  water |  220 |  2022-01-15 |  inu |
| 6 |  南アルプス |  water |  410 |  2022-01-12 |  uma |
| 7 |  モンスター |  energy |  300 |  2022-01-12 |  saru |
| 8 |  デカビタ |  energy |  120 |  2022-01-14 |  saru |
| 9 |  レッドブル |  energy |  350 |  2022-01-15 |  saru |
| 10 |  お茶 |  tea |  250 |  2022-01-16 |  inu |
| 11 |  紅茶 |  tea |  220 |  2022-01-17 |  uma |
| 12 |  麦茶 |  tea |  430 |  2022-01-17 |  neko |

上記のようなデータがあるとして日毎の前日との差分を出す
```SQL:前日との差分
SELECT
	s.date,
	s.price,
    s.price - LAG(s.price) OVER(ORDER BY s.date) AS before_date_diff
FROM (
	SELECT
		date,
		SUM(price) AS price
	FROM test_drink
	GROUP BY
		date
) AS s;
```

|  date |  price |  before_date_diff |
|:-------:|:--------|:--------|
| 2022-01-10 |  530 |   |
| 2022-01-12 |  860 |  330 |
| 2022-01-13 |  140 |  -720 |
| 2022-01-14 |  120 |  -20 |
| 2022-01-15 |  570 |  450 |
| 2022-01-16 |  250 |  -320 |
| 2022-01-17 |  650 |  400 |

一度日毎にグループ化してから現在のpriceからLAG()関数（前の行の値をとる）を引いて
前日との差分を算出する。

#### その他window関数
**CUME_DIST()**: 現在の行の値以下のパーティション値の割合を戻します。
**DENSE_RANK()** : パーティション内の現在の行のランクをギャップなしで返します。
**FIRST_VALUE(expr)** : ウィンドウフレームの最初の行からexprの値を返します。
**LAG(expr, N)** : パーティション内のN行で現在の行を遅延 (前) する行からexprの値を返します。
**LAST_VALUE(expr)** : ウィンドウフレームの最後の行からexprの値を返します。
**LEAD(expr, N)** : パーティション内のN行ごとに現在の行を導く (後に続く) 行から、exprの値を返します。
**NTH_VALUE(expr, N)** : ウィンドウフレームのN番目の行からexprの値を返します。
**NTILE()** : パーティション内の現在の行のバケット番号。
**PERCENT_RANK()** : 現在の行の値より小さいパーティション値の割合を戻します (最大値を除く)。
**RANK()** : パーティション内の現在の行のランク (ギャップあり) を戻します。
**ROW_NUMBER()** : パーティション内の現在の行の番号を返します。 

#### まとめ
今までサブクエリーや自己結合で集計していたものがwindow関数で簡単に出せるようになる場合があります。
今回は簡単な使い方の紹介でしたが、使い方を覚えれば色々な場面に応用が利くと思いますので積極的に使っていきましょう。