このセクションでは、クエリーの各行について、その行に関連する行を使用して計算を実行する非集計ウィンドウ関数について説明します。 ほとんどの集計関数は、ウィンドウ関数としても使用できます。セクション12.20.1「集計関数の説明」 を参照してください。
ウィンドウ関数の使用方法と例、および OVER 句、ウィンドウ、パーティション、フレーム、ピアなどの用語の定義については、セクション12.21.2「Window 関数の概念と構文」 を参照してください。
表 12.26 「ウィンドウ関数」
| 名前 | 説明 |
|---|---|
CUME_DIST() |
累積分布値 |
DENSE_RANK() |
パーティション内の現在の行のランク (ギャップなし) |
FIRST_VALUE() |
ウィンドウフレームの最初の行からの引数の値 |
LAG() |
パーティション内の現在行より遅れている行の引数の値 |
LAST_VALUE() |
ウィンドウフレームの最後の行からの引数の値 |
LEAD() |
パーティション内の現在の行の先頭行からの引数の値 |
NTH_VALUE() |
ウィンドウフレームの N 番目の行からの引数の値 |
NTILE() |
パーティション内の現在の行のバケット番号。 |
PERCENT_RANK() |
パーセントランク値 |
RANK() |
パーティション内の現在の行のランク (ギャップあり) |
ROW_NUMBER() |
パーティション内の現在の行数 |
次の関数の説明で、over_clause は、セクション12.21.2「Window 関数の概念と構文」 で説明されている OVER 句を表します。 一部のウィンドウ関数では、結果の計算時に NULL 値の処理方法を指定する null_treatment 句を使用できます。 この句はオプションです。 これは SQL 標準の一部ですが、MySQL 実装では RESPECT NULLS (デフォルト) のみが許可されます。 これは、結果の計算時に NULL 値が考慮されることを意味します。 IGNORE NULLS は解析されますが、エラーが発生します。
-
CUME_DIST()over_clause値のグループ内の値の累積分布、つまり現在の行の値以下のパーティション値の割合を戻します。 これは、ウィンドウパーティションのウィンドウ順序で現在の行の前またはピアの行数をウィンドウパーティションの合計行数で割った数を表します。 戻り値の範囲は 0 から 1 です。
パーティション行を目的の順序にソートするには、
ORDER BYでこの関数を使用する必要があります。ORDER BYを使用しない場合、すべての行はピアであり、N/N= 1,の値を持ちます。ここで、Nはパーティションサイズです。over_clauseについては、セクション12.21.2「Window 関数の概念と構文」 を参照してください。次のクエリーでは、
valカラムの値セットについて、各行のCUME_DIST()値、および同様のPERCENT_RANK()関数によって返されたパーセントランク値が表示されます。 参照のために、クエリーではROW_NUMBER()を使用して行番号も表示されます:mysql> SELECT val, ROW_NUMBER() OVER w AS 'row_number', CUME_DIST() OVER w AS 'cume_dist', PERCENT_RANK() OVER w AS 'percent_rank' FROM numbers WINDOW w AS (ORDER BY val); +------+------------+--------------------+--------------+ | val | row_number | cume_dist | percent_rank | +------+------------+--------------------+--------------+ | 1 | 1 | 0.2222222222222222 | 0 | | 1 | 2 | 0.2222222222222222 | 0 | | 2 | 3 | 0.3333333333333333 | 0.25 | | 3 | 4 | 0.6666666666666666 | 0.375 | | 3 | 5 | 0.6666666666666666 | 0.375 | | 3 | 6 | 0.6666666666666666 | 0.375 | | 4 | 7 | 0.8888888888888888 | 0.75 | | 4 | 8 | 0.8888888888888888 | 0.75 | | 5 | 9 | 1 | 1 | +------+------------+--------------------+--------------+ -
DENSE_RANK()over_clauseパーティション内の現在の行のランクをギャップなしで返します。 同僚は同僚とみなされ、同じランクを受け取ります。 この関数は、ピアグループに連続するランクを割り当てます。その結果、サイズが 1 より大きいグループは、連続しないランク番号を生成しません。 例については、
RANK()関数の説明を参照してください。パーティション行を目的の順序にソートするには、
ORDER BYでこの関数を使用する必要があります。ORDER BYがない場合、すべての行がピアになります。over_clauseについては、セクション12.21.2「Window 関数の概念と構文」 を参照してください。 -
FIRST_VALUE([expr)null_treatment]over_clauseウィンドウフレームの最初の行から
exprの値を返します。over_clauseについては、セクション12.21.2「Window 関数の概念と構文」 を参照してください。null_treatmentについては、概要のセクションで説明しています。次のクエリーは、
FIRST_VALUE()、LAST_VALUE()およびNTH_VALUE()の 2 つのインスタンスを示しています:mysql> SELECT time, subject, val, FIRST_VALUE(val) OVER w AS 'first', LAST_VALUE(val) OVER w AS 'last', NTH_VALUE(val, 2) OVER w AS 'second', NTH_VALUE(val, 4) OVER w AS 'fourth' FROM observations WINDOW w AS (PARTITION BY subject ORDER BY time ROWS UNBOUNDED PRECEDING); +----------+---------+------+-------+------+--------+--------+ | time | subject | val | first | last | second | fourth | +----------+---------+------+-------+------+--------+--------+ | 07:00:00 | st113 | 10 | 10 | 10 | NULL | NULL | | 07:15:00 | st113 | 9 | 10 | 9 | 9 | NULL | | 07:30:00 | st113 | 25 | 10 | 25 | 9 | NULL | | 07:45:00 | st113 | 20 | 10 | 20 | 9 | 20 | | 07:00:00 | xh458 | 0 | 0 | 0 | NULL | NULL | | 07:15:00 | xh458 | 10 | 0 | 10 | 10 | NULL | | 07:30:00 | xh458 | 5 | 0 | 5 | 10 | NULL | | 07:45:00 | xh458 | 30 | 0 | 30 | 10 | 30 | | 08:00:00 | xh458 | 25 | 0 | 25 | 10 | 30 | +----------+---------+------+-------+------+--------+--------+各関数は、現在のフレーム内の行を使用します。この行は、表示されているウィンドウ定義に従って、最初のパーティション行から現在の行に拡張されます。
NTH_VALUE()コールの場合、現在のフレームにはリクエストされた行が常に含まれるわけではありません。このような場合、戻り値はNULLです。 -
LAG([expr[,N[,default]])null_treatment]over_clauseパーティション内の
N行で現在の行を遅延 (前) する行からexprの値を返します。 そのような行がない場合、戻り値はdefaultです。 たとえば、Nが 3 の場合、最初の 2 行の戻り値はdefaultです。Nまたはdefaultがない場合、デフォルトはそれぞれ 1 およびNULLです。Nは、負でないリテラル整数である必要があります。Nが 0 の場合、exprは現在の行に対して評価されます。MySQL 8.0.22 以降、
NをNULLにすることはできません。 さらに、次のいずれかの形式で、1から263までの範囲の整数である必要があります:符号なし整数定数リテラル
位置パラメータマーカー (
?)ユーザー定義変数
ストアドルーチン内のローカル変数
over_clauseについては、セクション12.21.2「Window 関数の概念と構文」 を参照してください。null_treatmentについては、概要のセクションで説明しています。LAG()(および同様のLEAD()関数) は、多くの場合、行間の差異を計算するために使用されます。 次のクエリーは、時間順の監視のセットと、隣接する行からのLAG()およびLEAD()の値、および現在の行と隣接する行の違いを示しています:mysql> SELECT t, val, LAG(val) OVER w AS 'lag', LEAD(val) OVER w AS 'lead', val - LAG(val) OVER w AS 'lag diff', val - LEAD(val) OVER w AS 'lead diff' FROM series WINDOW w AS (ORDER BY t); +----------+------+------+------+----------+-----------+ | t | val | lag | lead | lag diff | lead diff | +----------+------+------+------+----------+-----------+ | 12:00:00 | 100 | NULL | 125 | NULL | -25 | | 13:00:00 | 125 | 100 | 132 | 25 | -7 | | 14:00:00 | 132 | 125 | 145 | 7 | -13 | | 15:00:00 | 145 | 132 | 140 | 13 | 5 | | 16:00:00 | 140 | 145 | 150 | -5 | -10 | | 17:00:00 | 150 | 140 | 200 | 10 | -50 | | 18:00:00 | 200 | 150 | NULL | 50 | NULL | +----------+------+------+------+----------+-----------+この例では、
LAG()コールとLEAD()コールは、それぞれデフォルトのN値とdefault値の 1 とNULLを使用します。最初の行は、
LAG()に前の行がない場合の処理を示しています: この関数は、default値 (この場合はNULL) を戻します。 最後の行には、LEAD()の次の行がない場合と同じものが表示されます。LAG()とLEAD()は、差異ではなく合計の計算にも役立ちます。 Fibonacci シリーズの最初の数個を含む次のデータセットについて考えてみます:mysql> SELECT n FROM fib ORDER BY n; +------+ | n | +------+ | 1 | | 1 | | 2 | | 3 | | 5 | | 8 | +------+次のクエリーは、現在の行に隣接する行の
LAG()およびLEAD()の値を表示します。 また、これらの関数を使用して、前後の行の値を現在の行の値に追加します。 効果は、Fibonacci シリーズの次の番号と、それ以降の次の番号を生成することです:mysql> SELECT n, LAG(n, 1, 0) OVER w AS 'lag', LEAD(n, 1, 0) OVER w AS 'lead', n + LAG(n, 1, 0) OVER w AS 'next_n', n + LEAD(n, 1, 0) OVER w AS 'next_next_n' FROM fib WINDOW w AS (ORDER BY n); +------+------+------+--------+-------------+ | n | lag | lead | next_n | next_next_n | +------+------+------+--------+-------------+ | 1 | 0 | 1 | 1 | 2 | | 1 | 1 | 2 | 2 | 3 | | 2 | 1 | 3 | 3 | 5 | | 3 | 2 | 5 | 5 | 8 | | 5 | 3 | 8 | 8 | 13 | | 8 | 5 | 0 | 13 | 8 | +------+------+------+--------+-------------+Fibonacci 番号の初期セットを生成する方法の 1 つは、再帰的な共通テーブル式を使用することです。 例については、フィボナッチシリーズ世代を参照してください。
MySQL 8.0.22 以降、この関数の rows 引数に負の値を使用することはできません。
-
LAST_VALUE([expr)null_treatment]over_clauseウィンドウフレームの最後の行から
exprの値を返します。over_clauseについては、セクション12.21.2「Window 関数の概念と構文」 を参照してください。null_treatmentについては、概要のセクションで説明しています。例については、
FIRST_VALUE()関数の説明を参照してください。 -
LEAD([expr[,N[,default]])null_treatment]over_clauseパーティション内の
N行ごとに現在の行を導く (後に続く) 行から、exprの値を返します。 そのような行がない場合、戻り値はdefaultです。 たとえば、Nが 3 の場合、戻り値は最後の 2 行のdefaultです。Nまたはdefaultがない場合、デフォルトはそれぞれ 1 およびNULLです。Nは、負でないリテラル整数である必要があります。Nが 0 の場合、exprは現在の行に対して評価されます。MySQL 8.0.22 以降、
NをNULLにすることはできません。 さらに、次のいずれかの形式で、1から263までの範囲の整数である必要があります:符号なし整数定数リテラル
位置パラメータマーカー (
?)ユーザー定義変数
ストアドルーチン内のローカル変数
over_clauseについては、セクション12.21.2「Window 関数の概念と構文」 を参照してください。null_treatmentについては、概要のセクションで説明しています。例については、
LAG()関数の説明を参照してください。MySQL 8.0.22 以降では、この関数の rows 引数に負の値を使用することはできません。
-
NTH_VALUE([expr,N)from_first_last] [null_treatment]over_clauseウィンドウフレームの
N番目の行からexprの値を返します。 そのような行がない場合、戻り値はNULLです。Nはリテラルの正の整数である必要があります。from_first_lastは SQL 標準の一部ですが、MySQL 実装ではFROM FIRST(デフォルト) のみが許可されます。 つまり、ウィンドウの最初の行から計算が開始されます。FROM LASTは解析されますが、エラーが発生します。FROM LASTと同じ効果を得るには (ウィンドウの最後の行から計算を開始)、ORDER BYを使用して逆の順序でソートします。over_clauseについては、セクション12.21.2「Window 関数の概念と構文」 を参照してください。null_treatmentについては、概要のセクションで説明しています。例については、
FIRST_VALUE()関数の説明を参照してください。MySQL 8.0.22 以降では、この関数の行引数に
NULLを使用できません。 -
NTILE(N)over_clauseパーティションを
Nグループ (バケット) に分割し、パーティション内の各行にバケット番号を割り当て、パーティション内の現在の行のバケット番号を返します。 たとえば、Nが 4 の場合、NTILE()は行を 4 つのバケットに分割します。Nが 100 の場合、NTILE()は行を 100 バケットに分割します。Nはリテラルの正の整数である必要があります。 バケット番号の戻り値の範囲は 1 からNです。MySQL 8.0.22 以降、
NをNULLにすることはできません。 また、次のいずれかの形式で、1から263までの範囲の整数である必要があります:符号なし整数定数リテラル
位置パラメータマーカー (
?)ユーザー定義変数
ストアドルーチン内のローカル変数
パーティション行を目的の順序にソートするには、
ORDER BYでこの関数を使用する必要があります。over_clauseについては、セクション12.21.2「Window 関数の概念と構文」 を参照してください。次のクエリーは、
valカラムの値セットについて、行を複数または 4 つのグループに分割した結果のパーセンタイル値を示しています。 参照のために、クエリーではROW_NUMBER()を使用して行番号も表示されます:mysql> SELECT val, ROW_NUMBER() OVER w AS 'row_number', NTILE(2) OVER w AS 'ntile2', NTILE(4) OVER w AS 'ntile4' FROM numbers WINDOW w AS (ORDER BY val); +------+------------+--------+--------+ | val | row_number | ntile2 | ntile4 | +------+------------+--------+--------+ | 1 | 1 | 1 | 1 | | 1 | 2 | 1 | 1 | | 2 | 3 | 1 | 1 | | 3 | 4 | 1 | 2 | | 3 | 5 | 1 | 2 | | 3 | 6 | 2 | 3 | | 4 | 7 | 2 | 3 | | 4 | 8 | 2 | 4 | | 5 | 9 | 2 | 4 | +------+------------+--------+--------+MySQL 8.0.22 以降、構成
NTILE(NULL)は許可されなくなりました。 -
PERCENT_RANK()over_clause現在の行の値より小さいパーティション値の割合を戻します (最大値を除く)。 戻り値の範囲は 0 から 1 で、次の式の結果として計算される行相対ランクを表します。ここで、
rankは行ランク、rowsはパーティション行数です:(rank - 1) / (rows - 1)パーティション行を目的の順序にソートするには、
ORDER BYでこの関数を使用する必要があります。ORDER BYがない場合、すべての行がピアになります。over_clauseについては、セクション12.21.2「Window 関数の概念と構文」 を参照してください。例については、
CUME_DIST()関数の説明を参照してください。 -
RANK()over_clauseパーティション内の現在の行のランク (ギャップあり) を戻します。 同僚は同僚とみなされ、同じランクを受け取ります。 複数のサイズのグループが存在する場合、この関数はピアグループに連続するランクを割り当てません。結果は連続しないランク番号になります。
パーティション行を目的の順序にソートするには、
ORDER BYでこの関数を使用する必要があります。ORDER BYがない場合、すべての行がピアになります。over_clauseについては、セクション12.21.2「Window 関数の概念と構文」 を参照してください。次のクエリーは、ギャップのあるランクを生成する
RANK()と、ギャップのないランクを生成するDENSE_RANK()の違いを示しています。 クエリーでは、valカラムの一連の値の各メンバーのランク値が表示されますが、これには重複が含まれています。RANK()はピア (重複) に同じランク値を割り当て、次に大きい値はピア数からランクを引いてランクを高くします。DENSE_RANK()ではピアにも同じランク値が割り当てられますが、次に高い値にはランクが 1 つ大きくなります。 参照のために、クエリーではROW_NUMBER()を使用して行番号も表示されます:mysql> SELECT val, ROW_NUMBER() OVER w AS 'row_number', RANK() OVER w AS 'rank', DENSE_RANK() OVER w AS 'dense_rank' FROM numbers WINDOW w AS (ORDER BY val); +------+------------+------+------------+ | val | row_number | rank | dense_rank | +------+------------+------+------------+ | 1 | 1 | 1 | 1 | | 1 | 2 | 1 | 1 | | 2 | 3 | 3 | 2 | | 3 | 4 | 4 | 3 | | 3 | 5 | 4 | 3 | | 3 | 6 | 4 | 3 | | 4 | 7 | 7 | 4 | | 4 | 8 | 7 | 4 | | 5 | 9 | 9 | 5 | +------+------------+------+------------+ -
ROW_NUMBER()over_clauseパーティション内の現在の行の番号を返します。 行番号の範囲は 1 からパーティション行の数です。
ORDER BYは、行の番号付けの順序に影響します。ORDER BYを使用しない場合、行番号付けは非決定的です。ROW_NUMBER()は、ピアに異なる行番号を割り当てます。 ピアに同じ値を割り当てるには、RANK()またはDENSE_RANK()を使用します。 例については、RANK()関数の説明を参照してください。over_clauseについては、セクション12.21.2「Window 関数の概念と構文」 を参照してください。