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

index

description: 'Обзорная страница для оконных функций' sidebar_label: 'Оконные функции' sidebar_position: 1 slug: /sql-reference/window-functions/ title: 'Оконные функции'​

Оконные функции

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

Стандартные оконные функции​

ClickHouse поддерживает стандартный синтаксис для определения окон и оконных функций. В таблице ниже указано, поддерживается ли функция в настоящее время.

ФункцияПоддерживается?
произвольная спецификация окна (count(*) over (partition by id order by time desc))✅
выражения, содержащие оконные функции, например, (count(*) over ()) / 2)✅
WINDOW клаузула (select ... from table window w as (partition by id))✅
ROWS фрейм✅
RANGE фрейм✅ (по умолчанию)
INTERVAL синтаксис для DateTime RANGE OFFSET фрейм❌ (укажите количество секунд вместо этого (RANGE работает с любым числовым типом).)
GROUPS фрейм❌
Вычисление агрегатных функций по фрейму (sum(value) over (order by time))✅ (Все агрегатные функции поддерживаются)
rank(), dense_rank(), row_number()✅
Псевдоним: denseRank()
percent_rank()✅ Эффективно вычисляет относительное положение значения в пределах раздела в наборе данных. Эта функция эффективно заменяет более громоздкое и вычислительно сложное ручное SQL-вычисление, выраженное как ifNull((rank() OVER(PARTITION BY x ORDER BY y) - 1) / nullif(count(1) OVER(PARTITION BY x) - 1, 0), 0)
Псевдоним: percentRank()
lag/lead(value, offset)❌
Вы можете использовать одно из следующих обходных решений:
1) any(value) over (.... rows between <offset> preceding and <offset> preceding), или following для lead
2) lagInFrame/leadInFrame, которые аналогичны, но учитывают фрейм окна. Чтобы получить поведение, идентичное lag/lead, используйте rows between unbounded preceding and unbounded following
ntile(buckets)✅
Укажите окно, как (partition by x order by y rows between unbounded preceding and unbounded following).

Специфические для ClickHouse оконные функции​

Существует также следующая специфическая для ClickHouse оконная функция:

nonNegativeDerivative(metric_column, timestamp_column[, INTERVAL X UNITS])​

Находит неотрицательную производную для заданного metric_column по timestamp_column. INTERVAL может быть опущен, по умолчанию INTERVAL 1 SECOND. Вычисляемое значение следующее для каждой строки:

  • 0 для 1-й строки,
  • metrici−metrici−1timestampi−timestampi−1∗interval{\text{metric}_i - \text{metric}_{i-1} \over \text{timestamp}_i - \text{timestamp}_{i-1}} * \text{interval} для ithi_{th} строки.

Синтаксис​

  • PARTITION BY - определяет, как разбивать результирующий набор на группы.
  • ORDER BY - определяет, как упорядочивать строки внутри группы во время вычисления aggregate_function.
  • ROWS or RANGE - определяет границы фрейма, aggregate_function вычисляется в рамках фрейма.
  • WINDOW - позволяет нескольким выражениям использовать одно и то же определение окна.

Функции​

Эти функции могут использоваться только как оконные функции.

  • row_number() - Нумерует текущую строку внутри своей группы, начиная с 1.
  • first_value(x) - Возвращает первое значение, оцененное в его упорядоченном фрейме.
  • last_value(x) - Возвращает последнее значение, оцененное в его упорядоченном фрейме.
  • nth_value(x, offset) - Возвращает первое не NULL значение, оцененное против n-й строки (смещение) в его упорядоченном фрейме.
  • rank() - Ранжирует текущую строку внутри своей группы с пропусками.
  • dense_rank() - Ранжирует текущую строку внутри своей группы без пропусков.
  • lagInFrame(x) - Возвращает значение, оцененное в строке, которая находится на указанном физическом смещении строк перед текущей строкой внутри упорядоченного фрейма.
  • leadInFrame(x) - Возвращает значение, оцененное в строке, которая находится на смещении строк после текущей строки внутри упорядоченного фрейма.

Примеры​

Давайте рассмотрим некоторые примеры того, как можно использовать оконные функции.

Нумерация строк​

Агрегатные функции​

Сравните зарплату каждого игрока со средней по их команде.

Сравните зарплату каждого игрока с максимальной по их команде.

Разбиение по колонке​

Ограничение фрейма​

Примеры из реальной жизни​

Следующие примеры решают общие задачи из реальной жизни.

Максимальная/общая зарплата по отделам​

Кумулятивная сумма​

Скользящее/Постепенное Среднее (по 3 строки)​

Скользящее/Постепенное Среднее (по 10 секунд)​

Скользящее/Постепенное Среднее (по 10 дней)​

Температура хранится с точностью до секунды, но с помощью Range и ORDER BY toDate(ts) мы формируем фрейм размером 10 единиц, а поскольку toDate(ts) единица - день.

Справочная информация​

Проблемы GitHub​

Дорожная карта для начальной поддержки оконных функций в этой проблеме.

Все проблемы GitHub, связанные с оконными функциями, имеют метку comp-window-functions.

Тесты​

Эти тесты содержат примеры в настоящий момент поддерживаемого синтаксиса:

https://github.com/ClickHouse/ClickHouse/blob/master/tests/performance/window_functions.xml

https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/01591_window_functions.sql

Документация Postgres​

https://www.postgresql.org/docs/current/sql-select.html#SQL-WINDOW

https://www.postgresql.org/docs/devel/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS

https://www.postgresql.org/docs/devel/functions-window.html

https://www.postgresql.org/docs/devel/tutorial-window.html

Документация MySQL​

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html