MySQL 8.0 リファレンスマニュアル MySQL NDB Cluster 8.0 を含む

このページは機械翻訳したものです。

12.21.3 ウィンドウ機能フレーム仕様

ウィンドウ関数で使用されるウィンドウの定義には、frame 句を含めることができます。 フレームは現在のパーティションのサブセットであり、frame 句はサブセットの定義方法を指定します。

フレームは現在の行に対して決定されます。これにより、現在の行のパーティション内での位置に応じて、フレームをパーティション内で移動できます。 例:

次のクエリーは、移動フレームを使用して、時間順序付けされた level 値の各グループ内の累積合計、および現在の行とその直前と直後の行から計算されたローリング平均を計算する方法を示しています: 

mysql> SELECT
         time, subject, val,
         SUM(val) OVER (PARTITION BY subject ORDER BY time
                        ROWS UNBOUNDED PRECEDING)
           AS running_total,
         AVG(val) OVER (PARTITION BY subject ORDER BY time
                        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
           AS running_average
       FROM observations;
+----------+---------+------+---------------+-----------------+
| time     | subject | val  | running_total | running_average |
+----------+---------+------+---------------+-----------------+
| 07:00:00 | st113   |   10 |            10 |          9.5000 |
| 07:15:00 | st113   |    9 |            19 |         14.6667 |
| 07:30:00 | st113   |   25 |            44 |         18.0000 |
| 07:45:00 | st113   |   20 |            64 |         22.5000 |
| 07:00:00 | xh458   |    0 |             0 |          5.0000 |
| 07:15:00 | xh458   |   10 |            10 |          5.0000 |
| 07:30:00 | xh458   |    5 |            15 |         15.0000 |
| 07:45:00 | xh458   |   30 |            45 |         20.0000 |
| 08:00:00 | xh458   |   25 |            70 |         27.5000 |
+----------+---------+------+---------------+-----------------+

running_average カラムの場合、最初のカラムの前または最後のカラムの後にフレーム行はありません。 このような場合、AVG() は使用可能な行の平均を計算します。

ウィンドウ関数として使用される集計関数は、次の非集計ウィンドウ関数と同様に、現在の行フレームの行を操作します: 

FIRST_VALUE()
LAST_VALUE()
NTH_VALUE()

標準 SQL は、パーティション全体で動作するウィンドウ関数に frame 句を含めないことを指定します。 MySQL では、このような関数の frame 句は許可されますが、無視されます。 これらの関数は、フレームが指定されている場合でもパーティション全体を使用します: 

CUME_DIST()
DENSE_RANK()
LAG()
LEAD()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

frame 句が指定されている場合、構文は次のとおりです: 

frame_clause:
    frame_units frame_extent

frame_units:
    {ROWS | RANGE}

frame 句がない場合、このセクションの後半で説明するように、デフォルトのフレームは ORDER BY 句が存在するかどうかによって異なります。

frame_units 値は、現在の行とフレーム行の関係のタイプを示します:

frame_extent 値は、フレームの開始点と終了点を示します。 フレームの開始のみを指定するか (この場合、現在の行が暗黙的に終了します)、BETWEEN を使用して両方のフレームエンドポイントを指定できます: 

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
}

BETWEEN 構文では、frame_start を frame_end より後にすることはできません。

許可される frame_start および frame_end の値には、次の意味があります:

次のクエリーは、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 です。

frame 句がない場合、デフォルトのフレームは ORDER BY 句が存在するかどうかによって異なります:

デフォルトのフレームは ORDER BY の有無によって異なるため、ORDER BY をクエリーに追加して決定的な結果を取得すると、結果が変わる可能性があります。 (たとえば、SUM() によって生成される値は変更される可能性があります。) 同じ結果を取得するが、ORDER BY ごとに順序付けするには、ORDER BY が存在するかどうかに関係なく、使用する明示的なフレーム仕様を指定します。

現在の行の値が NULL の場合、フレーム指定の意味は明白ではない可能性があります。 その場合、次の例は様々なフレーム仕様がどのように適用されるかを示しています:

MySQL Documentation Library