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

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

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

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

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 unrounded 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​

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

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

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