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

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

また、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】

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

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

結果

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)

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;

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したランクにする

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);

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とかだと全体に対して上の方だな、というのがわかる

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

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ブログ

`FIRST_VALUE()` `LAST_VALUE()`

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

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);

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)

こちらの参考にあるように、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:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

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

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

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

参考：【BigQuery】FIRST_VALUE関数，LAST_VALUE関数の使い方とその違い - Qiita

`NTH_VALUE()`

N番目の行の値を返す

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);

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]])

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

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個のグループに分割する

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

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()との違いは値が同じだった場合に重複した数値にするかしないか

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);

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

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

CUME_DIST()

RANK() DENSE_RANK()

PERCENT_RANK()

FIRST_VALUE() LAST_VALUE()

NTH_VALUE()

LAG() LEAD()

NTILE()

ROW_NUMBER()