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

ОБЗОР

Описание​

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

  • Обрабатывают данные в пределах заданного окна, а не группируют их с помощью 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)
+---------+------------+-------+------+
| item_id | date_time  | sales | sum  |
+---------+------------+-------+------+
|       2 | 2024-07-01 |   100 |  100 |
|       1 | 2024-07-01 |   100 |  200 |
|       3 | 2024-07-01 |   140 |  340 |
+---------+------------+-------+------+
3 rows in set (0.02 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