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 関数の概念と構文」 を参照してください。