MySQL 8 のWindow関数を試してみた（集約関数以外）　

こちらの参考を元に試してみました。間違っていたら指摘いただけるとありがたいです:bow:

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

---

また、`sum()`や`count()`といった集約関数でもウィンドウ関数として使えますが、今回はそれ以外の関数を試したという内容です。

---

| 関数 | 説明  | google翻訳 |
|:--|:--|:--|
| CUME_DIST() | Cumulative distribution value | 累積分布値|
| DENSE_RANK() | Rank of current row within its partition, without gaps | パーティション内のギャップのない現在の行のランク|
| FIRST_VALUE() | Value of argument from first row of window frame | ウィンドウフレームの最初の行からの引数の値|
| LAG() | Value of argument from row lagging current row within partition | パーティション内の現在の行よりも遅い行の引数の値|
| LAST_VALUE() | Value of argument from last row of window frame | ウィンドウフレームの最後の行からの引数の値|
| LEAD() | Value of argument from row leading current row within partition | パーティション内の現在の行に先行する行の引数の値|
| NTH_VALUE() | Value of argument from N-th row of window frame | ウィンドウフレームのN番目の行からの引数の値|
| NTILE() | Bucket number of current row within its partition. | パーティション内の現在の行のバケット番号|
| PERCENT_RANK() | Percentage rank value | 割合ランク値|
| RANK() | Rank of current row within its partition, with gaps | ギャップ内のパーティション内の現在の行のランク|
| ROW_NUMBER() | Number of current row within its partition | パーティション内の現在の行の数|

### `CUME_DIST()`

- 0 より大きく 1 以下の値の範囲を返す
- 累積分布を返す
    - 累積分布で個人的にわかりやすかった説明です 【[12-1. 累積分布関数とは | 統計学の時間 | 統計WEB](https://bellcurve.jp/statistics/course/6708.html)】

https://www.db-fiddle.com/f/2kQsqvBDY2nPKD2txdYwP7/0

```sql
create table hoge(n int);
insert into hoge values (1),(2),(2),(4),(5);

select n
      ,cume_dist() over(order by n)
from hoge;
```

```sql:結果
mysql> select n
    ->       ,cume_dist() over(order by n)
    -> from hoge;
+------+------------------------------+
| n    | cume_dist() over(order by n) |
+------+------------------------------+
|    1 |                          0.2 | /* 5つの値の中で nが1以下 の割合20% */
|    2 |                          0.6 | /* 5つの値の中で nが2以下 の割合60% */
|    2 |                          0.6 | /* 5つの値の中で nが2以下 の割合60% */
|    4 |                          0.8 | /* 5つの値の中で nが4以下 の割合80% */
|    5 |                            1 | /* 5つの値の中で nが5以下 の割合100% */
+------+------------------------------+
5 rows in set (0.01 sec)
```

---

```sql
create table fuga(category int, n int);
insert into fuga values
(1,3),(1,3),(1,5),(1,5),(1,1),
(2,10),(2,10),(2,3),(2,3),(2,5);

select category
      ,n
      ,cume_dist() over(partition by category order by n)
from fuga;
```

```sql
mysql> select category
    ->          ,n
    ->          ,cume_dist() over(partition by category order by n) cumedist
    -> from fuga;
+----------+------+----------+
| category | n    | cumedist |
+----------+------+----------+
|        1 |    1 |      0.2 | /* category:1の値の中で nが1以下の割合20% */
|        1 |    3 |      0.6 | /* category:1の値の中で nが3以下の割合60% */
|        1 |    3 |      0.6 | /* category:1の値の中で nが3以下の割合60% */
|        1 |    5 |        1 | /* category:1の値の中で nが5以下の割合100% */
|        1 |    5 |        1 | /* category:1の値の中で nが5以下の割合100% */
|        2 |    3 |      0.4 | /* category:2の値の中で nが3以下の割合40% */
|        2 |    3 |      0.4 | /* category:2の値の中で nが3以下の割合40% */
|        2 |    5 |      0.6 | /* category:2の値の中で nが5以下の割合60% */
|        2 |   10 |        1 | /* category:2の値の中で nが10以下の割合100% */
|        2 |   10 |        1 | /* category:2の値の中で nが10以下の割合100% */
+----------+------+----------+
10 rows in set (0.00 sec)
```

### `RANK()` `DENSE_RANK()`

- 値に対するランクを返してくれる
- `RANK()`は重複したランクの次のランクはその分飛んで次のランクにする、`DENSE_RANK()`重複したランクの次のランクも関係なく+1したランクにする

https://www.db-fiddle.com/f/vSsqxv2jXisKaxwHRunoeq/2

```sql
create table hoge(n int);
insert into hoge values
(10),(20),(20),(100),(50),(60),(90),(20),(50),(70);

create table fuga(category int, n int);
insert into fuga values
(1,10),(1,10),(1,10),(1,20),(1,30),
(2,10),(2,10),(2,50),(2,50),(2,60);
```

```sql
mysql> select n
    ->       ,rank() over(order by n) r
    ->       ,dense_rank() over(order by n) dr
    -> from hoge;
+------+----+----+
| n    | r  | dr |
+------+----+----+
|   10 |  1 |  1 |
|   20 |  2 |  2 |
|   20 |  2 |  2 |
|   20 |  2 |  2 |
|   50 |  5 |  3 |
|   50 |  5 |  3 |
|   60 |  7 |  4 |
|   70 |  8 |  5 |
|   90 |  9 |  6 |
|  100 | 10 |  7 |
+------+----+----+
10 rows in set (0.00 sec)

mysql> select category
    ->       ,n
    ->       ,rank() over(partition by category order by n) r
    ->       ,dense_rank() over(partition by category order by n) dr
    -> from fuga;
+----------+------+---+----+
| category | n    | r | dr |
+----------+------+---+----+
|        1 |   10 | 1 |  1 |
|        1 |   10 | 1 |  1 |
|        1 |   10 | 1 |  1 |
|        1 |   20 | 4 |  2 |
|        1 |   30 | 5 |  3 |
|        2 |   10 | 1 |  1 |
|        2 |   10 | 1 |  1 |
|        2 |   50 | 3 |  2 |
|        2 |   50 | 3 |  2 |
|        2 |   60 | 5 |  3 |
+----------+------+---+----+
10 rows in set (0.00 sec)
```

### `PERCENT_RANK()`

- ランクを相対的な値で出してくれる
- 0 〜 1 の値の範囲を返す

`0`が1位、`1`が最下位
`0.25`とかだと全体に対して上の方だな、というのがわかる

https://www.db-fiddle.com/f/2NKvbDPsJo5kssVkE4rrg7/0

```sql
create table hoge(n int);
insert into hoge values (1),(2),(2),(4),(5);
```

```sql
mysql> select n
    ->       ,rank() over (order by n) r
    ->       ,percent_rank() over (order by n)　pr
    ->       ,(rank() over (order by n) - 1) / (count(*) over () - 1) tmp -- (rank - 1)/(rows - 1) の計算と　percent_rankは同じ
    -> from hoge;
+------+---+-------+--------+
| n    | r | 　pr  | tmp    |
+------+---+-------+--------+
|    1 | 1 |     0 | 0.0000 |
|    2 | 2 |  0.25 | 0.2500 |
|    2 | 2 |  0.25 | 0.2500 |
|    4 | 4 |  0.75 | 0.7500 |
|    5 | 5 |     1 | 1.0000 |
+------+---+-------+--------+
5 rows in set (0.01 sec)
```

参考：[SQL分析関数 PERCENT_RANK パーセントで順位を計算する：AABlog：So-netブログ](https://asai-atsushi.blog.ss-blog.jp/2016-02-03)
 
### `FIRST_VALUE()` `LAST_VALUE()`

- `FIRST_VALUE()` 最初の行の値を返す
- `LAST_VALUE()` 最後の行の値を返す

https://www.db-fiddle.com/f/oxJwFDjsckyLfsyQxjMiEV/1

```sql
create table fuga(category int, n int);
insert into fuga values
(1,10),(1,10),(1,10),(1,20),(1,30),
(2,20),(2,20),(2,50),(2,50),(2,60);
```

```sql
mysql> select category
    ->       ,n
    -> 
    ->       ,first_value(n) over(order by n) f1 -- 最初の値を返す
    ->       ,last_value(n) over(order by n) l1 -- 最初の行から現在の行までで最後の値を返してしまう
    ->       
    ->       ,first_value(n) over(order by n range between unbounded preceding and unbounded following) f2 -- 最初の行の値を返す
    ->       ,last_value(n) over(order by n range between unbounded preceding and unbounded following) l2 -- 全行で最後の値を返す
    ->       
    ->       ,first_value(n) over(partition by category order by n) fp1 -- categoryごとの最初の値を返す
    ->       ,last_value(n) over(partition by category order by n) lp1 -- categoryごとの最初の行から現在の行までで最後の値を返してしまう
    ->       
    ->       ,first_value(n) over(partition by category order by n range between unbounded preceding and unbounded following) fp2-- categoryごとの最初の値を返す
    ->       ,last_value(n) over(partition by category order by n range between unbounded preceding and unbounded following) lp2 -- categoryごとの最後の値を返す
    -> from fuga;
+----------+------+------+-------+------+------+------+------+------+------+
| category | n    | f1   | l1　  | f2   | l2   | fp1  | lp1  | fp2  | lp2  |
+----------+------+------+-------+------+------+------+------+------+------+
|        1 |   10 |   10 |    10 |   10 |   60 |   10 |   10 |   10 |   30 |
|        1 |   10 |   10 |    10 |   10 |   60 |   10 |   10 |   10 |   30 |
|        1 |   10 |   10 |    10 |   10 |   60 |   10 |   10 |   10 |   30 |
|        1 |   20 |   10 |    20 |   10 |   60 |   10 |   20 |   10 |   30 |
|        1 |   30 |   10 |    30 |   10 |   60 |   10 |   30 |   10 |   30 |
|        2 |   20 |   10 |    20 |   10 |   60 |   20 |   20 |   20 |   60 |
|        2 |   20 |   10 |    20 |   10 |   60 |   20 |   20 |   20 |   60 |
|        2 |   50 |   10 |    50 |   10 |   60 |   20 |   50 |   20 |   60 |
|        2 |   50 |   10 |    50 |   10 |   60 |   20 |   50 |   20 |   60 |
|        2 |   60 |   10 |    60 |   10 |   60 |   20 |   60 |   20 |   60 |
+----------+------+------+-------+------+------+------+------+------+------+
10 rows in set (0.00 sec)
```

https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html

こちらの参考にあるように、`order by` だけを指定した場合、`LAST_VALUE()`が思った通りに動かないことがあります。


> With ORDER BY: The default frame includes rows from the partition start through the current row, including all peers of the current row (rows equal to the current row according to the ORDER BY clause). The default is equivalent to this frame specification:

```sql
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
```

`order by`があった場合で、範囲について何も指定がないとデフォルトで↑のような指定になってしまうため、
`LAST_VALUE()`は最初の行から現在の行の中の範囲から最後の値を返してしまいます。

最初の行からpartitionで区切った最後までの値で`LAST_VALUE()`を返したい場合は↓のように指定すると良いようでした　

```sql
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
```

参考：[【BigQuery】FIRST_VALUE関数，LAST_VALUE関数の使い方とその違い - Qiita](https://qiita.com/kota_fujimura/items/f77643132440b533be94)


### `NTH_VALUE()`

- N番目の行の値を返す

https://www.db-fiddle.com/f/xqaSnB3QwXRmuVRQt8GiBX/0

```sql
create table fuga(category int, n int);
insert into fuga values
(1,10),(1,20),(1,20),(1,30),(1,50),
(2,30),(2,30),(2,50),(2,50),(2,70);
```

```sql
mysql> select category
    ->       ,n
    -> 
    ->       ,nth_value(n, 2) over(order by n) n1 -- 2つ目の値を返す
    ->       ,nth_value(n, 5) over(order by n) n2 -- 5つ目の値を返す
    -> 
    ->       ,nth_value(n, 2) over(order by n range between unbounded preceding and unbounded following) n3
    ->       ,nth_value(n, 5) over(order by n range between unbounded preceding and unbounded following) n4
    -> 
    ->       ,nth_value(n, 2) over(partition by category order by n) n5 -- カテゴリーごとの2つ目の値を返す
    ->       ,nth_value(n, 5) over(partition by category order by n) n6 -- カテゴリーごとの5つ目の値を返す
    -> 
    ->       ,nth_value(n, 2) over(partition by category order by n range between unbounded preceding and unbounded following) n7
    ->       ,nth_value(n, 5) over(partition by category order by n range between unbounded preceding and unbounded following) n8 
    -> from fuga;
+----------+------+------+------+------+------+------+------+------+------+
| category | n    | n1   | n2   | n3   | n4   | n5   | n6   | n7   | n8   |
+----------+------+------+------+------+------+------+------+------+------+
|        1 |   10 | NULL | NULL |   20 |   30 | NULL | NULL |   20 |   50 |
|        1 |   20 |   20 | NULL |   20 |   30 |   20 | NULL |   20 |   50 |
|        1 |   20 |   20 | NULL |   20 |   30 |   20 | NULL |   20 |   50 |
|        1 |   30 |   20 |   30 |   20 |   30 |   20 | NULL |   20 |   50 |
|        1 |   50 |   20 |   30 |   20 |   30 |   20 |   50 |   20 |   50 |
|        2 |   30 |   20 |   30 |   20 |   30 |   30 | NULL |   30 |   70 |
|        2 |   30 |   20 |   30 |   20 |   30 |   30 | NULL |   30 |   70 |
|        2 |   50 |   20 |   30 |   20 |   30 |   30 | NULL |   30 |   70 |
|        2 |   50 |   20 |   30 |   20 |   30 |   30 | NULL |   30 |   70 |
|        2 |   70 |   20 |   30 |   20 |   30 |   30 |   70 |   30 |   70 |
+----------+------+------+------+------+------+------+------+------+------+
10 rows in set (0.00 sec)
```

例えば、5行目の値(`nth_value(n, 5)`)を出したいときに、1〜4行目がnullになってしまう場合があるため、`LAST_VALUE()`と同様に、` range between unbounded preceding and unbounded following` をつけると良いと思いました。

### `LAG()` `LEAD()`

- `LAG()` 現在の行からN行先行する行の値を返す
    - 構文:`LAG(expr [, N[, default]])`
- `LEAD()` 現在の行からN行リードしている行を返す
    - 構文:`LEAD(expr [, N[, default]])`

https://www.db-fiddle.com/f/nEeQyNMhFKR6zyVQ9DyAmD/0

```sql
create table hoge(n int);
insert into hoge values
(10),(20),(20),(100),(50),(60),(90),(20),(50),(70);
```

```sql
mysql> select n
    ->       ,lag(n) over(order by n) lag1 -- 1行前の値を返す
    ->       ,lead(n) over(order by n) lead1 -- 1行後の値を返す
    ->       ,lag(n, 3) over(order by n) lag3 -- 3行前の値を返す
    ->       ,lead(n, 3) over(order by n) lead3 -- 3行後の値を返す
    ->       ,lag(n, 3, 999) over(order by n) lagdefault -- 3行前の値を返す、取得できな かった場合のデフォルト値は999
    ->       ,lead(n, 3, 999) over(order by n) leaddefault -- 3行後の値を返す、取得でき なかった場合のデフォルト値は999
    -> from hoge;
+------+------+-------+------+-------+------------+-------------+
| n    | lag1 | lead1 | lag3 | lead3 | lagdefault | leaddefault |
+------+------+-------+------+-------+------------+-------------+
|   10 | NULL |    20 | NULL |    20 |        999 |          20 |
|   20 |   10 |    20 | NULL |    50 |        999 |          50 |
|   20 |   20 |    20 | NULL |    50 |        999 |          50 |
|   20 |   20 |    50 |   10 |    60 |         10 |          60 |
|   50 |   20 |    50 |   20 |    70 |         20 |          70 |
|   50 |   50 |    60 |   20 |    90 |         20 |          90 |
|   60 |   50 |    70 |   20 |   100 |         20 |         100 |
|   70 |   60 |    90 |   50 |  NULL |         50 |         999 |
|   90 |   70 |   100 |   50 |  NULL |         50 |         999 |
|  100 |   90 |  NULL |   60 |  NULL |         60 |         999 |
+------+------+-------+------+-------+------------+-------------+
10 rows in set (0.00 sec)
```

### `NTILE()`

- n個のグループに分割する

https://www.db-fiddle.com/f/kbecXN9k5MSTa8htiLFBFm/0

```sql
create table hoge(n int);
insert into hoge values
(10),(20),(20),(100),(50),(60),(90),(20),(50),(70);
```

```sql
mysql> select n
    ->       ,ntile(2) over(order by n) n2
    ->       ,ntile(3) over(order by n) n3
    ->       ,ntile(4) over(order by n) n4
    ->       ,ntile(5) over(order by n) n5
    ->       ,ntile(100) over(order by n) n100
    -> from hoge;
+------+------+------+------+------+------+
| n    | n2   | n3   | n4   | n5   | n100 |
+------+------+------+------+------+------+
|   10 |    1 |    1 |    1 |    1 |    1 |
|   20 |    1 |    1 |    1 |    1 |    2 |
|   20 |    1 |    1 |    1 |    2 |    3 |
|   20 |    1 |    1 |    2 |    2 |    4 |
|   50 |    1 |    2 |    2 |    3 |    5 |
|   50 |    2 |    2 |    2 |    3 |    6 |
|   60 |    2 |    2 |    3 |    4 |    7 |
|   70 |    2 |    3 |    3 |    4 |    8 |
|   90 |    2 |    3 |    4 |    5 |    9 |
|  100 |    2 |    3 |    4 |    5 |   10 |
+------+------+------+------+------+------+
10 rows in set (0.01 sec)
```

### `ROW_NUMBER()`

- 行の番号を返す

`RANK()`との違いは値が同じだった場合に重複した数値にするかしないか

https://www.db-fiddle.com/f/21iEDwT8Dw8P8NwCgevNBa/0

```sql
create table hoge(n int);
insert into hoge values
(10),(20),(20),(100),(50),(60),(90),(20),(50),(70);

create table fuga(category int, n int);
insert into fuga values
(1,10),(1,10),(1,10),(1,20),(1,30),
(2,10),(2,10),(2,50),(2,50),(2,60);
```

```sql
mysql> select n
    ->       ,row_number() over(order by n) `row`
    ->       ,rank() over(order by n) `rank`
    -> from hoge;
+------+-----+------+
| n    | row | rank |
+------+-----+------+
|   10 |   1 |    1 |
|   20 |   2 |    2 |
|   20 |   3 |    2 |
|   20 |   4 |    2 |
|   50 |   5 |    5 |
|   50 |   6 |    5 |
|   60 |   7 |    7 |
|   70 |   8 |    8 |
|   90 |   9 |    9 |
|  100 |  10 |   10 |
+------+-----+------+
10 rows in set (0.00 sec)

mysql> select category
    ->       ,n
    ->       ,row_number() over(partition by category order by n) `row`
    ->       ,rank() over(partition by category order by n) `rank`
    -> from fuga;
+----------+------+-----+------+
| category | n    | row | rank |
+----------+------+-----+------+
|        1 |   10 |   1 |    1 |
|        1 |   10 |   2 |    1 |
|        1 |   10 |   3 |    1 |
|        1 |   20 |   4 |    4 |
|        1 |   30 |   5 |    5 |
|        2 |   10 |   1 |    1 |
|        2 |   10 |   2 |    1 |
|        2 |   50 |   3 |    3 |
|        2 |   50 |   4 |    3 |
|        2 |   60 |   5 |    5 |
+----------+------+-----+------+
10 rows in set (0.00 sec)
```

---

最後まで見ていただいてありがとうございました。m(_ _)m