MySQL 8.0の新機能について調べてまとめています。 今回はWindow関数に関する章(https://dev.mysql.com/doc/refman/8.0/en/window-functions.html )を読んだまとめです。
window関数はクエリの結果から行ごとにその行に関連する計算を行う機能。
GROUP BYでは集約操作の集約するカラムの値ごとに1行の結果が返ってくるのに対し、window関数では行ごとにwindow関数による計算され結果が返る。
例えば(https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html より)
mysql> SELECT year, country, product, profit, SUM(profit) OVER() AS total_profit, SUM(profit) OVER(PARTITION BY country) AS country_profit FROM sales ORDER BY country, year, product, profit; +------+---------+------------+--------+--------------+----------------+ | year | country | product | profit | total_profit | country_profit | +------+---------+------------+--------+--------------+----------------+ | 2000 | Finland | Computer | 1500 | 7535 | 1610 | | 2000 | Finland | Phone | 100 | 7535 | 1610 | | 2001 | Finland | Phone | 10 | 7535 | 1610 | | 2000 | India | Calculator | 75 | 7535 | 1350 | | 2000 | India | Calculator | 75 | 7535 | 1350 | | 2000 | India | Computer | 1200 | 7535 | 1350 | | 2000 | USA | Calculator | 75 | 7535 | 4575 | | 2000 | USA | Computer | 1500 | 7535 | 4575 | | 2001 | USA | Calculator | 50 | 7535 | 4575 | | 2001 | USA | Computer | 1200 | 7535 | 4575 | | 2001 | USA | Computer | 1500 | 7535 | 4575 | | 2001 | USA | TV | 100 | 7535 | 4575 | | 2001 | USA | TV | 150 | 7535 | 4575 | +------+---------+------------+--------+--------------+----------------+
window関数をどの範囲に適用するかはOVER句を用いて定義する。
window関数全体のsyntaxはこの後説明するが、例えば上記のサンプルの2つめのOVER句ではcountryごとに結果を分割することを示している。これによってsum(profit)は同じ行のcountryと同じcontryのsum(profit)を返す。
一方で、最初のOVER句に引数がない。この場合はwindow全体の結果を対象として計算する。
基本的性質
- Window functionはSELECTクエリとORDER BYのみで使える
- クエリの結果はFROM句の結果によって決定され、
WHERE,GROUP BY,HAVINGの処理後、そしてORDER BY,LIMIT,SELECT DISTINCTの前に処理される。 - window関数によって計算する対象の行を
current_row,current_rowの値を計算するために対象とする行の範囲をquery rowsという
window functions
MySQL8.0でサポートされるwindow関数を表にまとめた。
Small Syntaxは独自に追加したで、公式ドキュメントの12.21.1のリストの表題になっている文法。
これらの関数を使う場合OVER句は必須。
over_clauseはOVER句のこと。null_treatmentはMySQLではSQL標準でデフォルトのRESPECT NULLSしかサポートされていない。IGNORE NULLSはエラーになる
| Name | Small Syntax | Description |
|---|---|---|
| CUME_DIST | CUME_DIST() over_clause | 累積分布、全レコード中の割合を0~1で表す |
| DENSE_RANK | DENSE_RANK() over_clause | 数値が飛ばないようにしたランク(同じランクの複数レコードがあっても次のランクを飛ばさない) |
| FIRST_VALUE | FIRST_VALUE(expr) [null_treatment] over_clause | windowフレーム内の最初の値 |
| LAG | LAG(expr [, N[, default]]) [null_treatment] over_clause | window内でexprを満たすN行前の値 |
| LAST_VALUE | LAST_VALUE(expr) [null_treatment] over_clause | windowフレーム内の最後の値 |
| LEAD | LEAD(expr [, N[, default]]) [null_treatment] over_clause | window内でexprを満たすN行後の値 |
| NTH_VALUE | NTH_VALUE(expr, N) [from_first_last] [null_treatment] over_clause | window内でexprを満たす行のうちN番目の値 |
| NTILE | NTILE(N) over_clause | partitionをN個のbucketに分割して、current_rowがどのbucketに入っているかを示す。 |
| PERCENT_RANK | PERCENT_RANK() over_clause | (rank-1)/(rows-1) |
| RANK | RANK() over_clause | ランキング、同じランクの行がある場合は次のランクが飛ぶ |
| ROW_NUMBER | ROW_NUMBER() over_clause | window内で何行目か |
window関数の早見クエリ
window関数がどういう動きをするのかざっくり思い出すときに便利なクエリサンプル。
準備
create table purchase_history ( time TIME, product VARCHAR(20), num int ); insert into purchase_history (time, product, num) values ('10:00:00', 'orange', 20), ('10:01:00', 'orange', 50), ('10:01:00', 'orange', 10), ('10:01:00', 'apple', 30), ('10:05:00', 'apple', 10), ('10:05:00', 'apple', 55), ('10:09:00', 'apple', 30); insert into purchase_history (time, product, num) values ('10:12:00', 'orange', 30), ('10:18:00', 'orange', 35), ('10:19:00', 'orange', 60), ('10:13:00', 'apple', 40), ('10:13:00', 'apple', 25), ('10:16:00', 'apple', 70), ('10:17:00', 'apple', 90); insert into purchase_history (time, product, num) values ('10:26:00', 'orange', 10), ('10:27:00', 'orange', 25), ('10:28:00', 'orange', 90), ('10:21:01', 'apple', 10), ('10:23:00', 'apple', 15), ('10:28:00', 'apple', 20), ('10:29:00', 'apple', 80);
早見クエリ
mysql> SELECT time, product, num, FIRST_VALUE(num) over(w), LAST_VALUE(num) over(w), ROW_NUMBER() over(w), RANK() over(w), DENSE_RANK() over(w), PERCENT_RANK() over(w), CUME_DIST() over(w), LAG(num,1) over(w), LEAD(num,2) over(w), NTH_VALUE(num, 2) over(w), NTILE(3) over(w) FROM purchase_history WINDOW w AS (PARTITION BY `product` ORDER BY `time`, `product`); +----------+---------+------+--------------------------+-------------------------+----------------------+----------------+----------------------+------------------------+---------------------+--------------------+----------------------+---------------------------+------------------+ | time | product | num | FIRST_VALUE(num) over(w) | LAST_VALUE(num) over(w) | ROW_NUMBER() over(w) | RANK() over(w) | DENSE_RANK() over(w) | PERCENT_RANK() over(w) | CUME_DIST() over(w) | LAG(num,1) over(w) | LEAD(num, 2) over(w) | NTH_VALUE(num, 2) over(w) | NTILE(3) over(w) | +----------+---------+------+--------------------------+-------------------------+----------------------+----------------+----------------------+------------------------+---------------------+--------------------+----------------------+---------------------------+------------------+ | 10:01:00 | apple | 30 | 30 | 80 | 1 | 1 | 1 | 0 | 0.08333333333333333 | NULL | 55 | 10 | 1 | | 10:05:00 | apple | 10 | 30 | 80 | 2 | 2 | 2 | 0.09090909090909091 | 0.25 | 30 | 30 | 10 | 1 | | 10:05:00 | apple | 55 | 30 | 80 | 3 | 2 | 2 | 0.09090909090909091 | 0.25 | 10 | 40 | 10 | 1 | | 10:09:00 | apple | 30 | 30 | 80 | 4 | 4 | 3 | 0.2727272727272727 | 0.3333333333333333 | 55 | 25 | 10 | 1 | | 10:13:00 | apple | 40 | 30 | 80 | 5 | 5 | 4 | 0.36363636363636365 | 0.5 | 30 | 70 | 10 | 2 | | 10:13:00 | apple | 25 | 30 | 80 | 6 | 5 | 4 | 0.36363636363636365 | 0.5 | 40 | 90 | 10 | 2 | | 10:16:00 | apple | 70 | 30 | 80 | 7 | 7 | 5 | 0.5454545454545454 | 0.5833333333333334 | 25 | 10 | 10 | 2 | | 10:17:00 | apple | 90 | 30 | 80 | 8 | 8 | 6 | 0.6363636363636364 | 0.6666666666666666 | 70 | 15 | 10 | 2 | | 10:21:01 | apple | 10 | 30 | 80 | 9 | 9 | 7 | 0.7272727272727273 | 0.75 | 90 | 20 | 10 | 3 | | 10:23:00 | apple | 15 | 30 | 80 | 10 | 10 | 8 | 0.8181818181818182 | 0.8333333333333334 | 10 | 80 | 10 | 3 | | 10:28:00 | apple | 20 | 30 | 80 | 11 | 11 | 9 | 0.9090909090909091 | 0.9166666666666666 | 15 | NULL | 10 | 3 | | 10:29:00 | apple | 80 | 30 | 80 | 12 | 12 | 10 | 1 | 1 | 20 | NULL | 10 | 3 | | 10:00:00 | orange | 20 | 20 | 90 | 1 | 1 | 1 | 0 | 0.1111111111111111 | NULL | 10 | 50 | 1 | | 10:01:00 | orange | 50 | 20 | 90 | 2 | 2 | 2 | 0.125 | 0.3333333333333333 | 20 | 30 | 50 | 1 | | 10:01:00 | orange | 10 | 20 | 90 | 3 | 2 | 2 | 0.125 | 0.3333333333333333 | 50 | 35 | 50 | 1 | | 10:12:00 | orange | 30 | 20 | 90 | 4 | 4 | 3 | 0.375 | 0.4444444444444444 | 10 | 60 | 50 | 2 | | 10:18:00 | orange | 35 | 20 | 90 | 5 | 5 | 4 | 0.5 | 0.5555555555555556 | 30 | 10 | 50 | 2 | | 10:19:00 | orange | 60 | 20 | 90 | 6 | 6 | 5 | 0.625 | 0.6666666666666666 | 35 | 25 | 50 | 2 | | 10:26:00 | orange | 10 | 20 | 90 | 7 | 7 | 6 | 0.75 | 0.7777777777777778 | 60 | 90 | 50 | 3 | | 10:27:00 | orange | 25 | 20 | 90 | 8 | 8 | 7 | 0.875 | 0.8888888888888888 | 10 | NULL | 50 | 3 | | 10:28:00 | orange | 90 | 20 | 90 | 9 | 9 | 8 | 1 | 1 | 25 | NULL | 50 | 3 | +----------+---------+------+--------------------------+-------------------------+----------------------+----------------+----------------------+------------------------+---------------------+--------------------+----------------------+---------------------------+------------------+ 21 rows in set (0.01 sec)
Syntax
referenceの中にあるsyntax記述の寄せ集め。over句移行のsyntax
over_clause:
{OVER (window_spec) | OVER window_name}
window_spec:
[window_name] [partition_clause] [order_clause] [frame_clause]
partition_clause:
PARTITION BY expr [, expr] ...
order_clause:
ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...
frame_clause:
frame_units frame_extent
frame_units:
{ROWS | RANGE}
frame_extent:
{frame_start | frame_between}
frame_between:
BETWEEN frame_start AND frame_end
frame_start, frame_end: {
CURRENT ROW
| UNBOUNDED PRECEDING
| UNBOUNDED FOLLOWING
| expr PRECEDING
| expr FOLLOWING
}
WINDOW句から始まる場合のsyntax (主にNamed window)
(window_spec以降は上記と同じ)
WINDOW window_name AS (window_spec)
[, window_name AS (window_spec)] ...
window_spec:
[window_name] [partition_clause] [order_clause] [frame_clause]
over句
over_clause:
{OVER (window_spec) | OVER window_name}
先に述べたようにwindown関数を使うためにはquery rows(current_rowに対するwindow関数の対象範囲)を指定する必要がある。(OVER句の引数が空の場合はすべての行が対象になる)
query rowsの指定の仕方には直接window_specを指定する方法と、名前付きwindowを指定する方法がある。
どちらの方法でも同じquery rowsを指定していれば結果に違いはない。
この記事内だと最初に挙げた year, country, product, ...のサンプルクエリでは直接query rowsを指定していて、早見表では名前付きwindow (w) を宣言してこれをOVER句で指定している。
名前付きwindowは以下の構文でFROM句のあとで宣言できる
WINDOW window_name AS (window_spec)
[, window_name AS (window_spec)] ...
window_spac
window_spec:
[window_name] [partition_clause] [order_clause] [frame_clause]
window_spec内の各句は以降で説明する.
なお、frame_clauseに関してはさらに次のFrame Specificationで説明する
partition_clause
PARTITION BY句ではどのようにquery rowsを分割するかを決定する。window関数が計算の対象とする行はこのPARTITION BY句で分割した結果セットとなる。
この句が省略される場合はクエリによるすべての結果が対象となる。
partition_clause:
PARTITION BY expr [, expr] ...
SQL標準ではPARTITION BYの前に来ることが許されるのはカラム名だけだが、MySQLの拡張ではここにexprを許可している。
例えばPARTITION BYの対象のカラムがTIMESTAMP型のtsだった場合、SQL標準ではtsのみが指定できるが、MySQLではHOUR(ts)なども指定できる。
order_clause
ご存知ORDER BY句.
PARTITION BYによって区切られたそれぞれの範囲内でのソート順序を示す。
全体の結果をソートしたい場合はwindow句内ではなくトップレベルで指定する。
order_clause:
ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...
Frame Specification
PARTITION句の中でframe句を使うことで、partiton内でさらにcurrent_rowを基準としたsubsetを指定することができる、これをフレーム(frame)と呼ぶ。 これを利用することで移動平均などを求めることができる。
早見表と同じテーブルを利用したサンプル
mysql> SELECT -> time, -> product, -> num, -> sum(num) over(PARTITION BY `product` ORDER BY `time`, `product` ROWS UNBOUNDED PRECEDING) as running_total, -> sum(num) over(PARTITION BY `product` ORDER BY `time`, `product` ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as running_average, -> FIRST_VALUE(num) over(PARTITION BY `product` ORDER BY `time`, `product` ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as `first_value`, -> LAST_VALUE(num) over(PARTITION BY `product` ORDER BY `time`, `product` ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as `last_value`, -> NTH_VALUE(num, 2) over(PARTITION BY `product` ORDER BY `time`, `product` ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as `nth_value` -> FROM purchase_history; +----------+---------+------+---------------+-----------------+-------------+------------+-----------+ | time | product | num | running_total | running_average | first_value | last_value | nth_value | +----------+---------+------+---------------+-----------------+-------------+------------+-----------+ | 10:01:00 | apple | 30 | 30 | 40 | 30 | 10 | 10 | | 10:05:00 | apple | 10 | 40 | 95 | 30 | 55 | 10 | | 10:05:00 | apple | 55 | 95 | 95 | 10 | 30 | 55 | | 10:09:00 | apple | 30 | 125 | 125 | 55 | 40 | 30 | | 10:13:00 | apple | 40 | 165 | 95 | 30 | 25 | 40 | | 10:13:00 | apple | 25 | 190 | 135 | 40 | 70 | 25 | | 10:16:00 | apple | 70 | 260 | 185 | 25 | 90 | 70 | | 10:17:00 | apple | 90 | 350 | 170 | 70 | 10 | 90 | | 10:21:01 | apple | 10 | 360 | 115 | 90 | 15 | 10 | | 10:23:00 | apple | 15 | 375 | 45 | 10 | 20 | 15 | | 10:28:00 | apple | 20 | 395 | 115 | 15 | 80 | 20 | | 10:29:00 | apple | 80 | 475 | 100 | 20 | 80 | 80 | | 10:00:00 | orange | 20 | 20 | 70 | 20 | 50 | 50 | | 10:01:00 | orange | 50 | 70 | 80 | 20 | 10 | 50 | | 10:01:00 | orange | 10 | 80 | 90 | 50 | 30 | 10 | | 10:12:00 | orange | 30 | 110 | 75 | 10 | 35 | 30 | | 10:18:00 | orange | 35 | 145 | 125 | 30 | 60 | 35 | | 10:19:00 | orange | 60 | 205 | 105 | 35 | 10 | 60 | | 10:26:00 | orange | 10 | 215 | 95 | 60 | 25 | 10 | | 10:27:00 | orange | 25 | 240 | 125 | 10 | 90 | 25 | | 10:28:00 | orange | 90 | 330 | 115 | 25 | 90 | 90 | +----------+---------+------+---------------+-----------------+-------------+------------+-----------+ 21 rows in set (0.01 sec)
- SQL標準ではパーティション全体を対象とするwindow関数ではframe句を使わないことになっているが、MySQLでは許容されたうえで無視される
- 以下の関数ではframe句を指定しても無視されて、パーティション全体を対象とした上で計算される
- CUME_DIST()
- DENSE_RANK()
- LAG()
- LEAD()
- NTILE()
- PERCENT_RANK()
- RANK()
- ROW_NUMBER()
フレーム句のSyntaxは以下
frame_clause:
frame_units frame_extent
frame_units:
{ROWS | RANGE}
frame_extent:
{frame_start | frame_between}
frame_between:
BETWEEN frame_start AND frame_end
frame_start, frame_end: {
CURRENT ROW
| UNBOUNDED PRECEDING
| UNBOUNDED FOLLOWING
| expr PRECEDING
| expr FOLLOWING
}
frame_units
frame_unitsではcurrent_rowとframeに含む行の関係を指定する。
- ROWSの場合、current_rowからのポジションによってframeのstartとendを指定する
- RANGEの場合、current_rowの値をもとにした値の範囲によってframe rowsを決定する
frame_extent, frame_between
frame_extentではframe rowsの範囲を明示的に指定する。
- frame_startでは開始行のみを指定し、終了行はcurrent_rowになる
- frame_betweenでは開始行と終了行を指定できる
- betweenを使った場合、frame_startがframe_endの前に来ることはできない
frame_start, frame_end
frameの開始と終了の指定には以下のSyntaxを指定することができる
- CURRENT ROW: 文字通りcurrent_rowを指定
- UNBOUNDED PRECEDING: パーティションの最初の行
- UNBOUNDED FOLLOWING: パーティションの最後の行
- expr PRECEDING: current_row以前のexprを満たす行、exprの結果がNULLの場合はcurrent_row
- expr FOLLOWING: current_row以降のexprを満たす行、exprの結果がNULLの場合はcurrent_row
current_rowがNULLだった場合frameの定義は曖昧になる。
基本的には
- NULLとの比較(IS NULL, IS NOT NULLを除く)はNULLになる。つまりPRECENDING/FOLLOWING NはNULLの行を返す
- 昇順でsortした場合はNULLは先頭に来る、降順の場合は最後
以上を抑えておけば変える行がわかる。
具体例は、https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html を確認
window関数の制限
MySQLではwindow関数で以下はサポートしない。
- 集約を行うwindow関数での
DISTINCT構文 - window関数のネスト(Nested window functions)
current rowに依存した動的なフレームのエンドポイントの指定
パーサーは以下の構造をパースするが、サポートされていない(パーサで対応しているということは近々対応するということかな?)
GROUPSフレームユニット指定詞はパースされるがエラーになる。ROWS,RANGEだけがサポートされているEXCLUDE句はパースされるが、エラーになるIGNORE NULLSはパースされるが、エラーになるRESPECT NULLSのみがサポートされているFROM LASTはパースされるが、エラーになるFROM FIRSTのみがサポートされている
reference
- https://dev.mysql.com/doc/refman/8.0/en/window-functions.html
- https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html
- https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html
- https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html
- https://dev.mysql.com/doc/refman/8.0/en/window-functions-named-windows.html
- https://dev.mysql.com/doc/refman/8.0/en/window-function-restrictions.html
