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

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

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

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

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

ФункцияПоддерживается?
ad hoc спецификация окна (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) - Возвращает первое ненулевое значение, вычисленное против n-й строки (offset) в заданной упорядоченной рамке.
  • rank() - Нумерует текущую строку в пределах своей партиции с пропусками.
  • dense_rank() - Нумерует текущую строку в пределах своей партиции без пропусков.
  • 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

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