14.20.3 Window Function Frame Specification — MySQL 8.4

14.20.3 Спецификация фрейма функции окна

Определение окна, используемого с функцией окна, может включать в себя клаузу фрейма. Фрейм — это подмножество текущего раздела, а клауза фрейма указывает, как определить подмножество.

Фреймы определяются относительно текущей строки, что позволяет фрейму перемещаться внутри раздела в зависимости от расположения текущей строки в рамках раздела. Примеры:

Определив фрейм как все строки от начала раздела до текущей строки, можно вычислить текущие суммы для каждой строки.
Определив фрейм как расширяющийся на 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 определяет, что функции окна, которые работают со всем разделом, не должны иметь клаузу фрейма. MySQL допускает клаузу фрейма для таких функций, но игнорирует ее. Эти функции используют весь раздел, даже если фрейм указан:

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

Клауза фрейма, если она указана, имеет следующий синтаксис:

frame_clause:
    frame_units frame_extent

frame_units:
    {ROWS | RANGE}

В отсутствие клаузы фрейма, фрейм по умолчанию зависит от наличия клаузы 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: Граница — это последняя строка раздела.
expr PRECEDING: Для ROWS граница — это expr строк перед текущей строкой. Для RANGE граница — это строки со значениями, равными значению текущей строки минус expr; если значение текущей строки равно NULL, граница — это строки-аналоги строки.

Для expr PRECEDING (и expr FOLLOWING), expr может быть маркером параметра ? (для использования в подготовленном запросе), неотрицательным числовым литералом или временным интервалом в формате INTERVAL val unit. Для выражений INTERVAL, val указывает неотрицательное значение интервала, а unit — ключевое слово, указывающее единицы, в которых должно интерпретироваться значение. (Подробности о допустимых спецификаторах units см. в описании функции DATE_ADD() в разделе 14.7 «Функции даты и времени».)

RANGE для числового или временного expr требует ORDER BY соответственно для числового или временного выражения.

Примеры допустимых индикаторов expr PRECEDING и expr FOLLOWING:
```
10 PRECEDING
INTERVAL 5 DAY PRECEDING
5 FOLLOWING
INTERVAL '2:30' MINUTE_SECOND FOLLOWING
```
expr FOLLOWING: Для ROWS граница — это expr строк после текущей строки. Для RANGE граница — это строки со значениями, равными значению текущей строки плюс expr; если значение текущей строки равно NULL, граница — это строки-аналоги строки.

Для допустимых значений expr см. описание expr PRECEDING.

Следующий запрос демонстрирует FIRST_VALUE(), LAST_VALUE() и два примера NTH_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 |
+----------+---------+------+-------+------+--------+--------+

Каждая функция использует строки в текущем фрейме, который, согласно определению окна, показанному выше, простирается от первой строки раздела до текущей строки. Для вызовов NTH_VALUE() текущий фрейм не всегда включает в себя запрошенную строку; в таких случаях возвращаемое значение равно NULL.

В отсутствие клаузы фрейма, фрейм по умолчанию зависит от наличия клаузы 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.