12.21.1 Window 関数の説明

このセクションでは、クエリーの各行について、その行に関連する行を使用して計算を実行する非集計ウィンドウ関数について説明します。ほとんどの集計関数は、ウィンドウ関数としても使用できます。セクション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 から 2⁶³ までの範囲の整数である必要があります:
- 符号なし整数定数リテラル
- 位置パラメータマーカー (?)
- ユーザー定義変数
- ストアドルーチン内のローカル変数
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 から 2⁶³ までの範囲の整数である必要があります:
- 符号なし整数定数リテラル
- 位置パラメータマーカー (?)
- ユーザー定義変数
- ストアドルーチン内のローカル変数
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 から 2⁶³ までの範囲の整数である必要があります:
- 符号なし整数定数リテラル
- 位置パラメータマーカー (?)
- ユーザー定義変数
- ストアドルーチン内のローカル変数
パーティション行を目的の順序にソートするには、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 関数の概念と構文」を参照してください。