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

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

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

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

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) - Возвращает первое ненулевое значение, оцененное по nth строке (offset) в ее упорядоченном фрейме.
  • rank() - Ранжирует текущую строку в ее партиции с gaps.
  • dense_rank() - Ранжирует текущую строку в ее партиции без gaps.
  • lagInFrame(x) - Возвращает значение, оцененное на строке, которая находится на заданном физическом смещении строк перед текущей строкой в упорядоченном фрейме.
  • leadInFrame(x) - Возвращает значение, оцененное на строке, которая находится на смещении строк после текущей строки в упорядоченном фрейме.

Примеры​

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

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

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

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

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

Партиционирование по колонке​

Границы фрейма​

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

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

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

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

Скользящее / колеблющееся среднее (по 3 строкам)​

Скользящее / колеблющееся среднее (по 10 секундам)​

Скользящее / колеблющееся среднее (по 10 дням)​

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

Ссылки​

GitHub Issues​

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

Все проблемы 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

Связанный контент​