Movatterモバイル変換

The definition of a window used with a window function can include a frame clause. A frame is a subset of the current partition and the frame clause specifies how to define the subset.

Frames are determined with respect to the current row, which enables a frame to move within a partition depending on the location of the current row within its partition. Examples:

The following query demonstrates the use of moving frames to compute running totals within each group of time-orderedlevel values, as well as rolling averages computed from the current row and the rows that immediately precede and follow it:

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 |+----------+---------+------+---------------+-----------------+

For therunning_average column, there is no frame row preceding the first one or following the last. In these cases,AVG() computes the average of the rows that are available.

Aggregate functions used as window functions operate on rows in the current row frame, as do these nonaggregate window functions:

FIRST_VALUE()LAST_VALUE()NTH_VALUE()

Standard SQL specifies that window functions that operate on the entire partition should have no frame clause. MySQL permits a frame clause for such functions but ignores it. These functions use the entire partition even if a frame is specified:

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

The frame clause, if given, has this syntax:

frame_clause:frame_unitsframe_extentframe_units:    {ROWS | RANGE}

In the absence of a frame clause, the default frame depends on whether anORDER BY clause is present, as described later in this section.

Theframe_units value indicates the type of relationship between the current row and frame rows:

Theframe_extent value indicates the start and end points of the frame. You can specify just the start of the frame (in which case the current row is implicitly the end) or useBETWEEN to specify both frame endpoints:

frame_extent:    {frame_start |frame_between}frame_between:    BETWEENframe_start ANDframe_endframe_start,frame_end: {    CURRENT ROW  | UNBOUNDED PRECEDING  | UNBOUNDED FOLLOWING  |expr PRECEDING  |expr FOLLOWING}

WithBETWEEN syntax,frame_start must not occur later thanframe_end.

The permittedframe_start andframe_end values have these meanings:

The following query demonstratesFIRST_VALUE(),LAST_VALUE(), and two instances ofNTH_VALUE():

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 |+----------+---------+------+-------+------+--------+--------+

Each function uses the rows in the current frame, which, per the window definition shown, extends from the first partition row to the current row. For theNTH_VALUE() calls, the current frame does not always include the requested row; in such cases, the return value isNULL.

In the absence of a frame clause, the default frame depends on whether anORDER BY clause is present:

Because the default frame differs depending on presence or absence ofORDER BY, addingORDER BY to a query to get deterministic results may change the results. (For example, the values produced bySUM() might change.) To obtain the same results but ordered perORDER BY, provide an explicit frame specification to be used regardless of whetherORDER BY is present.

The meaning of a frame specification can be nonobvious when the current row value isNULL. Assuming that to be the case, these examples illustrate how various frame specifications apply: