PDF (US Ltr)
- 36.1Mb
PDF (A4)
- 36.2Mb
このページは機械翻訳したものです。
ウィンドウ関数で使用されるウィンドウの定義には、frame 句を含めることができます。 フレームは現在のパーティションのサブセットであり、frame 句はサブセットの定義方法を指定します。
フレームは現在の行に対して決定されます。これにより、現在の行のパーティション内での位置に応じて、フレームをパーティション内で移動できます。 例:
パーティションの開始行から現在の行までのすべての行になるようにフレームを定義することで、各行の累積合計を計算できます。
現在の行の両側で
N行を拡張するようにフレームを定義することで、ローリング平均を計算できます。
次のクエリーは、移動フレームを使用して、時間順序付けされた 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 値は、現在の行とフレーム行の関係のタイプを示します:
ROWS: フレームは、開始行と終了行の位置によって定義されます。 オフセットは、現在の行番号と行番号の違いです。RANGE: フレームは、値の範囲内の行によって定義されます。 オフセットは、現在の行の値と行の値の違いです。
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 の値には、次の意味があります:
CURRENT ROW:ROWSの場合、バインドは現在の行です。RANGEの場合、バインドは現在の行のピアです。UNBOUNDED PRECEDING: バインドは最初のパーティション行です。UNBOUNDED FOLLOWING: バインドは最後のパーティション行です。-
exprPRECEDINGROWSの場合、バインドは現在の行の前のexpr行です。RANGEの場合、バインドされるのは、現在の行の値からexprを引いた値を持つ行です。現在の行の値がNULLの場合、バインドされるのは行のピアです。exprPRECEDINGexprFOLLOWINGexprは?パラメータマーカー (プリペアドステートメントで使用)、負でない数値リテラルまたはINTERVAL形式の時間間隔になります。valunitINTERVAL式の場合、valは負でない間隔値を指定し、unitは値を解釈する単位を示すキーワードです。 (許可されているunits指定子の詳細は、セクション12.7「日付および時間関数」 のDATE_ADD()関数の説明を参照してください。)数値または時間的
expr上のRANGEには、それぞれ数値式または時間的式上のORDER BYが必要です。有効な
exprPRECEDINGexprFOLLOWING10 PRECEDING INTERVAL 5 DAY PRECEDING 5 FOLLOWING INTERVAL '2:30' MINUTE_SECOND FOLLOWING -
exprFOLLOWINGROWSの場合、バインドは現在の行の後のexpr行です。RANGEの場合、バインドされるのは、現在の行の値にexprを加えた値を持つ行です。現在の行の値がNULLの場合、バインドされるのは行のピアです。exprの許容値については、exprPRECEDING
次のクエリーは、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句に従って現在の行と等しい行) を含む、パーティションの開始行から現在の行までの行が含まれます。 デフォルトは、次のフレーム仕様と同等です:RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -
ORDER BYなし: デフォルトのフレームには、すべてのパーティション行が含まれます (ORDER BYがない場合、すべてのパーティション行はピアであるため)。 デフォルトは、次のフレーム仕様と同等です:RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
デフォルトのフレームは ORDER BY の有無によって異なるため、ORDER BY をクエリーに追加して決定的な結果を取得すると、結果が変わる可能性があります。 (たとえば、SUM() によって生成される値は変更される可能性があります。) 同じ結果を取得するが、ORDER BY ごとに順序付けするには、ORDER BY が存在するかどうかに関係なく、使用する明示的なフレーム仕様を指定します。
現在の行の値が NULL の場合、フレーム指定の意味は明白ではない可能性があります。 その場合、次の例は様々なフレーム仕様がどのように適用されるかを示しています:
-
ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND 15 FOLLOWINGフレームは
NULLで始まり、NULLで停止するため、値がNULLの行のみが含まれます。 -
ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWINGフレームは
NULLから始まり、パーティションの最後で停止します。ASCソートではNULL値が最初に配置されるため、フレームはパーティション全体になります。 -
ORDER BY X DESC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWINGフレームは
NULLから始まり、パーティションの最後で停止します。DESCソートではNULL値が最後に配置されるため、フレームはNULL値のみです。 -
ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND UNBOUNDED FOLLOWINGフレームは
NULLから始まり、パーティションの最後で停止します。ASCソートではNULL値が最初に配置されるため、フレームはパーティション全体になります。 -
ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWINGフレームは
NULLで始まり、NULLで停止するため、値がNULLの行のみが含まれます。 -
ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 1 PRECEDINGフレームは
NULLで始まり、NULLで停止するため、値がNULLの行のみが含まれます。 -
ORDER BY X ASC RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWINGフレームはパーティションの先頭から始まり、値が
NULLの行で停止します。ASCソートではNULL値が最初に配置されるため、フレームはNULL値のみです。