Перейти к содержимому
Последнее обновление

ОБЗОР

Описание​

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

  • Обрабатывают данные в пределах заданного окна, а не по группам GROUP BY
  • Вычисляют значение для каждой строки результирующего набора
  • Позволяют добавлять дополнительные столбцы в список SELECT
  • Выполняются в конце обработки запроса (после JOIN, WHERE, GROUP BY)

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

Синтаксис​

<FUNCTION> ( [ <ARGUMENTS> ] ) OVER ( [ <windowDefinition> ] )

Где:

windowDefinition ::=

[ PARTITION BY <expr1> [, ...] ]
[ ORDER BY <expr2> [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ <windowFrameClause> ]

Где:

windowFrameClause ::=
{
  | { ROWS } <n> PRECEDING
  | { ROWS } CURRENT ROW
  | { ROWS } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  | { ROWS | RANGE } UNBOUNDED PRECEDING
  | { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  | { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  | { ROWS } BETWEEN <n> { PRECEDING | FOLLOWING } AND <n> { PRECEDING | FOLLOWING }
  | { ROWS } BETWEEN UNBOUNDED PRECEDING AND <n> { PRECEDING | FOLLOWING }
  | { ROWS } BETWEEN <n> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
}

Параметры​

<FUNCTION>

Имя оконной функции. Поддерживаются все агрегатные функции, а также специальные оконные функции: DENSE_RANK(), FIRST_VALUE(), LAG(), LAST_VALUE(), LEAD(), RANK(), ROW_NUMBER(), NTH_VALUE(), PERCENT_RANK(), CUME_DIST(), NTILE().

<ARGUMENTS>

Необязательный параметр. Входные аргументы оконной функции. Типы и количество аргументов зависят от используемой функции.

<PARTITION_BY>

Необязательный параметр. Подобно GROUP BY, группирует данные по указанным столбцам, после чего вычисления выполняются внутри каждой секции.

<ORDER_BY>

Необязательный параметр. Используется для сортировки данных внутри каждой секции. Если секции не заданы, сортируется весь набор данных. Однако этот ORDER BY отличается от обычного ORDER BY, который указывается в конце оператора SQL. Сортировка, заданная в предложении OVER, применяется только к данным внутри соответствующей секции, тогда как ORDER BY в конце оператора SQL определяет порядок всех строк в окончательном результате запроса. Оба варианта могут использоваться одновременно. Кроме того, если ORDER BY явно не указан в предложении OVER, порядок данных внутри секции может быть произвольным, что способно привести к непредсказуемым итоговым результатам. Если столбцы сортировки заданы явно, но содержат повторяющиеся значения, результаты всё равно могут быть нестабильными. Конкретные примеры приведены в разборе примера ниже.

<windowFrameClause>

Необязательный параметр. Используется для определения рамки окна. В настоящее время поддерживаются два типа: RANGE и ROWS. Выражение N PRECEDING/FOLLOWING, где N — положительное целое число, задаёт диапазон скользящего окна относительно текущей строки. В настоящее время оно поддерживается только для окон типа ROWS, поэтому задаёт физическое смещение относительно текущей строки. Тип RANGE пока имеет некоторые ограничения: допускаются только варианты BOTH UNBOUNDED BOUNDARY или ONE UNBOUNDED BOUNDARY AND ONE CURRENT ROW. Если рамка не задана, по умолчанию неявно используется RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Возвращаемое значение​

Возвращает значение того же типа данных, что и входное выражение.

Однозначный порядок данных для аналитических функций​

1. Проблема нестабильных результатов

Когда предложение ORDER BY оконной функции не обеспечивает однозначный порядок данных, например если выражение ORDER BY даёт повторяющиеся значения, порядок строк становится неопределённым. Это означает, что при повторных выполнениях запроса эти строки могут возвращаться в разном порядке, приводя к нестабильным результатам оконной функции.

Следующий пример показывает, как запрос возвращает разные результаты при последовательных запусках. Нестабильность возникает главным образом потому, что ORDER BY date_time не обеспечивает однозначный порядок для оконной функции SUM.

CREATE TABLE test_window_order 
    (item_id int,
    date_time date,
    sales double)
distributed BY hash(item_id)
properties("replication_num" = 1);

INSERT INTO test_window_order VALUES
(1, '2024-07-01', 100),
(2, '2024-07-01', 100),
(3, '2024-07-01', 140);

SELECT
    item_id, date_time, sales,
    sum(sales) OVER (ORDER BY date_time ROWS BETWEEN 
        UNBOUNDED PRECEDING AND CURRENT ROW) sum
FROM
    test_window_order;

Из-за повторяющихся значений в столбце сортировки date_time можно получить следующие два результата запроса:

+---------+------------+-------+------+
| item_id | date_time  | sales | sum  |
+---------+------------+-------+------+
|       1 | 2024-07-01 |   100 |  100 |
|       3 | 2024-07-01 |   140 |  240 |
|       2 | 2024-07-01 |   100 |  340 |
+---------+------------+-------+------+
3 rows in set (0.03 sec)

2. Решение

Чтобы решить эту проблему, можно добавить в предложение сортировки столбец с уникальными значениями, например item_id: его включение в ORDER BY обеспечит однозначный порядок строк.

SELECT
        item_id,
        date_time,
        sales,
        sum(sales) OVER (
        ORDER BY item_id,
        date_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum
FROM
        test_window_order;

В результате запрос возвращает стабильный результат:

+---------+------------+-------+------+
| item_id | date_time  | sales | sum  |
+---------+------------+-------+------+
|       1 | 2024-07-01 |   100 |  100 |
|       2 | 2024-07-01 |   100 |  200 |
|       3 | 2024-07-01 |   140 |  340 |
+---------+------------+-------+------+
3 rows in set (0.03 sec)
Report issue