Оконные функции
Оконные функции позволяют выполнять вычисления по набору строк, связанным с текущей строкой. Некоторые из вычислений, которые вы можете выполнить, похожи на те, которые можно сделать с помощью агрегатной функции, но оконная функция не вызывает группировку строк в один вывод - отдельные строки все еще возвращаются.
Стандартные оконные функции
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-й строки,- для строки.
Синтаксис
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
Документация 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