14.20.2 Window Function Concepts and Syntax — MySQL 9.2

14.20.2 Понятие и синтаксис оконных функций

В этом разделе описывается использование оконных функций. Примеры используют те же данные о продажах, что и в обсуждении функции GROUPING() в разделе 14.19.2, «Модификаторы GROUP BY»:

mysql> SELECT * FROM sales ORDER BY country, year, product;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2001 | Finland | Phone      |     10 |
| 2000 | India   | Calculator |     75 |
| 2000 | India   | Calculator |     75 |
| 2000 | India   | Computer   |   1200 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   1500 |
| 2001 | USA     | Computer   |   1200 |
| 2001 | USA     | TV         |    150 |
| 2001 | USA     | TV         |    100 |
+------+---------+------------+--------+

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

Строка, для которой выполняется вычисление функции, называется текущей строкой.
Строки запроса, относящиеся к текущей строке, над которыми выполняется вычисление функции, составляют окно для текущей строки.

Например, используя таблицу с информацией о продажах, эти два запроса выполняют агрегатные операции, которые производят одну глобальную сумму для всех строк, взятых как группа, и суммы, сгруппированные по стране:

mysql> SELECT SUM(profit) AS total_profit
       FROM sales;
+--------------+
| total_profit |
+--------------+
|         7535 |
+--------------+
mysql> SELECT country, SUM(profit) AS country_profit
       FROM sales
       GROUP BY country
       ORDER BY country;
+---------+----------------+
| country | country_profit |
+---------+----------------+
| Finland |           1610 |
| India   |           1350 |
| USA     |           4575 |
+---------+----------------+

В отличие от этого, оконные операции не сводят группы строк запроса к одной выходной строке. Вместо этого они производят результат для каждой строки. Как и в предыдущих запросах, следующий запрос использует SUM(), но на этот раз как оконную функцию:

mysql> SELECT
         year, country, product, profit,
         SUM(profit) OVER() AS total_profit,
         SUM(profit) OVER(PARTITION BY country) AS country_profit
       FROM sales
       ORDER BY country, year, product, profit;
+------+---------+------------+--------+--------------+----------------+
| year | country | product    | profit | total_profit | country_profit |
+------+---------+------------+--------+--------------+----------------+
| 2000 | Finland | Computer   |   1500 |         7535 |           1610 |
| 2000 | Finland | Phone      |    100 |         7535 |           1610 |
| 2001 | Finland | Phone      |     10 |         7535 |           1610 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Computer   |   1200 |         7535 |           1350 |
| 2000 | USA     | Calculator |     75 |         7535 |           4575 |
| 2000 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | Calculator |     50 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1200 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | TV         |    100 |         7535 |           4575 |
| 2001 | USA     | TV         |    150 |         7535 |           4575 |
+------+---------+------------+--------+--------------+----------------+

Каждая оконная операция в запросе обозначается включением клаузы OVER, которая определяет, как разделить строки запроса на группы для обработки оконной функцией:

Первая клауза OVER пуста, что рассматривает весь набор строк запроса как одну партицию. Таким образом, оконная функция производит глобальную сумму, но делает это для каждой строки.
Вторая клауза OVER разделяет строки по стране, производя сумму по каждой партиции (по каждой стране). Функция производит эту сумму для каждой строки партиции.

Оконные функции разрешены только в списке выбора и клаузе ORDER BY. Строки результата запроса определяются клаузой FROM после обработки WHERE, GROUP BY и HAVING, а выполнение оконных функций происходит до ORDER BY, LIMIT и SELECT DISTINCT.

Клауза OVER разрешена для многих агрегатных функций, которые поэтому могут использоваться в качестве оконных или не оконных функций в зависимости от наличия или отсутствия клаузы OVER:

AVG()
BIT_AND()
BIT_OR()
BIT_XOR()
COUNT()
JSON_ARRAYAGG()
JSON_OBJECTAGG()
MAX()
MIN()
STDDEV_POP(), STDDEV(), STD()
STDDEV_SAMP()
SUM()
VAR_POP(), VARIANCE()
VAR_SAMP()

Подробную информацию о каждой агрегатной функции см. в разделе 14.19.1, «Описание агрегатных функций».

MySQL также поддерживает не агрегатные функции, которые используются только как оконные функции. Для них клауза OVER обязательна:

CUME_DIST()
DENSE_RANK()
FIRST_VALUE()
LAG()
LAST_VALUE()
LEAD()
NTH_VALUE()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

Подробную информацию о каждой не агрегатной функции см. в разделе 14.20.1, «Описание оконных функций».

В качестве примера одной из этих не агрегатных оконных функций этот запрос использует ROW_NUMBER(), который производит номер строки каждой строки в пределах своей партиции. В этом случае строки нумеруются по странам. По умолчанию строки партиции неупорядочены, и нумерация строк не детерминирована. Чтобы отсортировать строки партиции, включите клаузу ORDER BY в определение окна. Запрос использует неупорядоченные и упорядоченные партиции (столбцы row_num1 и row_num2), чтобы проиллюстрировать разницу между пропуском и включением ORDER BY:

mysql> SELECT
         year, country, product, profit,
         ROW_NUMBER() OVER(PARTITION BY country) AS row_num1,
         ROW_NUMBER() OVER(PARTITION BY country ORDER BY year, product) AS row_num2
       FROM sales;
+------+---------+------------+--------+----------+----------+
| year | country | product    | profit | row_num1 | row_num2 |
+------+---------+------------+--------+----------+----------+
| 2000 | Finland | Computer   |   1500 |        2 |        1 |
| 2000 | Finland | Phone      |    100 |        1 |        2 |
| 2001 | Finland | Phone      |     10 |        3 |        3 |
| 2000 | India   | Calculator |     75 |        2 |        1 |
| 2000 | India   | Calculator |     75 |        3 |        2 |
| 2000 | India   | Computer   |   1200 |        1 |        3 |
| 2000 | USA     | Calculator |     75 |        5 |        1 |
| 2000 | USA     | Computer   |   1500 |        4 |        2 |
| 2001 | USA     | Calculator |     50 |        2 |        3 |
| 2001 | USA     | Computer   |   1500 |        3 |        4 |
| 2001 | USA     | Computer   |   1200 |        7 |        5 |
| 2001 | USA     | TV         |    150 |        1 |        6 |
| 2001 | USA     | TV         |    100 |        6 |        7 |
+------+---------+------------+--------+----------+----------+

Как уже упоминалось ранее, для использования оконной функции (или для обработки агрегатной функции как оконной функции) включите клаузу OVER после вызова функции. Клауза OVER имеет две формы:

over_clause:
    {OVER (window_spec) | OVER window_name}

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

В первом случае спецификация окна появляется непосредственно в клаузе OVER, между скобками.
Во втором случае window_name — это имя спецификации окна, определённой клаузой WINDOW в другом месте запроса. Подробности см. в разделе 14.20.4, «Именованные окна».

Для синтаксиса OVER (window_spec) спецификация окна имеет несколько частей, все необязательные:

window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

Если OVER() пуста, окно состоит из всех строк запроса, и оконная функция вычисляет результат, используя все строки. В противном случае, присутствующие в скобках клаузы определяют, какие строки запроса используются для вычисления результата функции и как они разделяются и упорядочиваются:

window_name: Имя окна, определённого клаузой WINDOW в другом месте запроса. Если window_name появляется сама по себе в клаузе OVER, она полностью определяет окно. Если также указаны клаузы разбиения, упорядочения или фрейминга, они изменяют интерпретацию именованного окна. Подробности см. в разделе 14.20.4, «Именованные окна».
partition_clause: Клауза PARTITION BY указывает, как разделить строки запроса на группы. Результат оконной функции для данной строки основан на строках партиции, содержащей эту строку. Если PARTITION BY опущена, существует одна партиция, состоящая из всех строк запроса.

Примечание

Разбиение для оконных функций отличается от разбиения таблиц. Сведения о разбиении таблиц см. в главе 26, Разбиение.

partition_clause имеет следующий синтаксис:
```
partition_clause:
    PARTITION BY expr [, expr] ...
```
Стандарт SQL требует, чтобы PARTITION BY следовали только имена столбцов. Расширение MySQL — разрешение выражений, а не только имён столбцов. Например, если таблица содержит столбец TIMESTAMP с именем ts, стандартный SQL разрешает PARTITION BY ts, но не PARTITION BY HOUR(ts), тогда как MySQL разрешает оба.
order_clause: Клауза ORDER BY указывает, как отсортировать строки в каждой партиции. Строки партиции, равные согласно клаузе ORDER BY, считаются равными. Если ORDER BY опущена, строки партиций неупорядочены, без подразумеваемой последовательности обработки, и все строки партиции равны.

order_clause имеет этот синтаксис:
```
order_clause:
    ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...
```
Каждое выражение ORDER BY необязательно может быть дополнено ASC или DESC для указания направления сортировки. По умолчанию используется ASC, если направление не указано. NULL значения сортируются первыми для возрастающей сортировки и последними для убывающей.

Клауза ORDER BY в определении окна применяется внутри отдельных партиций. Для сортировки всего набора результатов включите клаузу ORDER BY на верхнем уровне запроса.
frame_clause: Фрейм — это подмножество текущей партиции, и клауза фрейма определяет, как определить это подмножество. У клаузы фрейма есть много своих подклауз. Подробности см. в разделе 14.20.3, «Спецификация фреймов оконных функций».