> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8a08bda2.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Страница обзора оконных функций # Оконные функции Оконные функции позволяют выполнять вычисления по набору строк, связанных с текущей строкой. Некоторые из этих вычислений похожи на те, которые выполняет агрегатная функция, но оконная функция не объединяет строки в один результирующий вывод — отдельные строки по-прежнему возвращаются.
## Стандартные оконные функции
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()` | | `cume_dist()` | ✅ Вычисляет кумулятивное распределение значения в группе значений. Возвращает процент строк со значениями, меньшими или равными значению в текущей строке. | | `lag/lead(value, offset)` | ✅
Также можно использовать одно из следующих обходных решений:
1) `any(value) over (.... rows between preceding and 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` для первой строки, * ${\text{metric}_i - \text{metric}_{i-1} \over \text{timestamp}_i - \text{timestamp}_{i-1}} * \text{interval}$ для $i$-й строки.
## Синтаксис
```text theme={null} aggregate_function (column_name) OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column] [ROWS or RANGE expression_to_bound_rows_within_the_group]] | [window_name]) FROM table_name WINDOW window_name as ([ [PARTITION BY grouping_column] [ORDER BY sorting_column] [ROWS or RANGE expression_to_bound_rows_within_the_group] ]) ``` * `PARTITION BY` - определяет, как разбить результирующий набор на группы. * `ORDER BY` - определяет порядок строк внутри группы при вычислении aggregate\_function. * `ROWS or RANGE` - определяет границы рамки окна, в пределах которой вычисляется aggregate\_function. * `WINDOW` - позволяет нескольким выражениям использовать одно и то же определение окна. ```text theme={null} PARTITION ┌─────────────────┐ <-- UNBOUNDED PRECEDING (BEGINNING of the PARTITION) │ │ │ │ │=================│ <-- N PRECEDING <─┐ │ N ROWS │ │ F │ Before CURRENT │ │ R │~~~~~~~~~~~~~~~~~│ <-- CURRENT ROW │ A │ M ROWS │ │ M │ After CURRENT │ │ E │=================│ <-- M FOLLOWING <─┘ │ │ │ │ └─────────────────┘ <--- UNBOUNDED FOLLOWING (END of the PARTITION) ```
### Функции
Эти функции можно использовать только как оконные функции. * [`row_number()`](/reference/functions/window-functions/row_number) - Нумерует текущую строку в пределах её партиции, начиная с 1. * [`first_value(x)`](/reference/functions/window-functions/first_value) - Возвращает первое значение, вычисленное в пределах упорядоченной рамки окна. * [`last_value(x)`](/reference/functions/window-functions/last_value) - Возвращает последнее значение, вычисленное в пределах упорядоченной рамки окна. * [`nth_value(x, offset)`](/reference/functions/window-functions/nth_value) - Возвращает первое значение, отличное от NULL, вычисленное для n-й строки (offset) в пределах упорядоченной рамки окна. * [`rank()`](/reference/functions/window-functions/rank) - Присваивает текущей строке ранг в пределах её партиции с пропусками. * [`dense_rank()`](/reference/functions/window-functions/dense_rank) - Присваивает текущей строке ранг в пределах её партиции без пропусков. * [`lagInFrame(x)`](/reference/functions/window-functions/lagInFrame) - Возвращает значение, вычисленное для строки, находящейся на указанное физическое смещение перед текущей строкой в пределах упорядоченной рамки окна. * [`leadInFrame(x)`](/reference/functions/window-functions/leadInFrame) - Возвращает значение, вычисленное для строки, находящейся на указанное смещение после текущей строки в пределах упорядоченной рамки окна.
## Примеры
Рассмотрим несколько примеров использования оконных функций.
### Нумерация строк
```sql theme={null} CREATE TABLE salaries ( `team` String, `player` String, `salary` UInt32, `position` String ) Engine = Memory; INSERT INTO salaries FORMAT Values ('Port Elizabeth Barbarians', 'Gary Chen', 195000, 'F'), ('New Coreystad Archdukes', 'Charles Juarez', 190000, 'F'), ('Port Elizabeth Barbarians', 'Michael Stanley', 150000, 'D'), ('New Coreystad Archdukes', 'Scott Harrison', 150000, 'D'), ('Port Elizabeth Barbarians', 'Robert George', 195000, 'M'); ``` ```sql theme={null} SELECT player, salary, row_number() OVER (ORDER BY salary ASC) AS row FROM salaries; ``` ```text theme={null} ┌─player──────────┬─salary─┬─row─┐ │ Michael Stanley │ 150000 │ 1 │ │ Scott Harrison │ 150000 │ 2 │ │ Charles Juarez │ 190000 │ 3 │ │ Gary Chen │ 195000 │ 4 │ │ Robert George │ 195000 │ 5 │ └─────────────────┴────────┴─────┘ ``` ```sql theme={null} SELECT player, salary, row_number() OVER (ORDER BY salary ASC) AS row, rank() OVER (ORDER BY salary ASC) AS rank, dense_rank() OVER (ORDER BY salary ASC) AS denseRank FROM salaries; ``` ```text theme={null} ┌─player──────────┬─salary─┬─row─┬─rank─┬─denseRank─┐ │ Michael Stanley │ 150000 │ 1 │ 1 │ 1 │ │ Scott Harrison │ 150000 │ 2 │ 1 │ 1 │ │ Charles Juarez │ 190000 │ 3 │ 3 │ 2 │ │ Gary Chen │ 195000 │ 4 │ 4 │ 3 │ │ Robert George │ 195000 │ 5 │ 4 │ 3 │ └─────────────────┴────────┴─────┴──────┴───────────┘ ```
### Функции агрегации
Сравните зарплату каждого игрока со средней зарплатой в его команде. ```sql theme={null} SELECT player, salary, team, avg(salary) OVER (PARTITION BY team) AS teamAvg, salary - teamAvg AS diff FROM salaries; ``` ```text theme={null} ┌─player──────────┬─salary─┬─team──────────────────────┬─teamAvg─┬───diff─┐ │ Charles Juarez │ 190000 │ New Coreystad Archdukes │ 170000 │ 20000 │ │ Scott Harrison │ 150000 │ New Coreystad Archdukes │ 170000 │ -20000 │ │ Gary Chen │ 195000 │ Port Elizabeth Barbarians │ 180000 │ 15000 │ │ Michael Stanley │ 150000 │ Port Elizabeth Barbarians │ 180000 │ -30000 │ │ Robert George │ 195000 │ Port Elizabeth Barbarians │ 180000 │ 15000 │ └─────────────────┴────────┴───────────────────────────┴─────────┴────────┘ ``` Сравните зарплату каждого игрока с максимальной зарплатой в его команде. ```sql theme={null} SELECT player, salary, team, max(salary) OVER (PARTITION BY team) AS teamMax, salary - teamMax AS diff FROM salaries; ``` ```text theme={null} ┌─player──────────┬─salary─┬─team──────────────────────┬─teamMax─┬───diff─┐ │ Charles Juarez │ 190000 │ New Coreystad Archdukes │ 190000 │ 0 │ │ Scott Harrison │ 150000 │ New Coreystad Archdukes │ 190000 │ -40000 │ │ Gary Chen │ 195000 │ Port Elizabeth Barbarians │ 195000 │ 0 │ │ Michael Stanley │ 150000 │ Port Elizabeth Barbarians │ 195000 │ -45000 │ │ Robert George │ 195000 │ Port Elizabeth Barbarians │ 195000 │ 0 │ └─────────────────┴────────┴───────────────────────────┴─────────┴────────┘ ```
### Партиционирование по столбцу
```sql theme={null} CREATE TABLE wf_partition ( `part_key` UInt64, `value` UInt64, `order` UInt64 ) ENGINE = Memory; INSERT INTO wf_partition FORMAT Values (1,1,1), (1,2,2), (1,3,3), (2,0,0), (3,0,0); SELECT part_key, value, order, groupArray(value) OVER (PARTITION BY part_key) AS frame_values FROM wf_partition ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1,2,3] │ <┐ │ 1 │ 2 │ 2 │ [1,2,3] │ │ 1-st group │ 1 │ 3 │ 3 │ [1,2,3] │ <┘ │ 2 │ 0 │ 0 │ [0] │ <- 2-nd group │ 3 │ 0 │ 0 │ [0] │ <- 3-d group └──────────┴───────┴───────┴──────────────┘ ```
### Границы рамки окна
```sql theme={null} CREATE TABLE wf_frame ( `part_key` UInt64, `value` UInt64, `order` UInt64 ) ENGINE = Memory; INSERT INTO wf_frame FORMAT Values (1,1,1), (1,2,2), (1,3,3), (1,4,4), (1,5,5); ``` ```sql theme={null} -- Рамка окна ограничена границами партиции (BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) SELECT part_key, value, order, groupArray(value) OVER ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1,2,3,4,5] │ │ 1 │ 2 │ 2 │ [1,2,3,4,5] │ │ 1 │ 3 │ 3 │ [1,2,3,4,5] │ │ 1 │ 4 │ 4 │ [1,2,3,4,5] │ │ 1 │ 5 │ 5 │ [1,2,3,4,5] │ └──────────┴───────┴───────┴──────────────┘ ``` ```sql theme={null} -- краткая форма — без выражения границ и без ORDER BY, -- эквивалент `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` SELECT part_key, value, order, groupArray(value) OVER (PARTITION BY part_key) AS frame_values_short, groupArray(value) OVER (PARTITION BY part_key ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values_short─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1,2,3,4,5] │ [1,2,3,4,5] │ │ 1 │ 2 │ 2 │ [1,2,3,4,5] │ [1,2,3,4,5] │ │ 1 │ 3 │ 3 │ [1,2,3,4,5] │ [1,2,3,4,5] │ │ 1 │ 4 │ 4 │ [1,2,3,4,5] │ [1,2,3,4,5] │ │ 1 │ 5 │ 5 │ [1,2,3,4,5] │ [1,2,3,4,5] │ └──────────┴───────┴───────┴────────────────────┴──────────────┘ ``` ```sql theme={null} -- рамка окна ограничена началом партиции и текущей строкой SELECT part_key, value, order, groupArray(value) OVER ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1] │ │ 1 │ 2 │ 2 │ [1,2] │ │ 1 │ 3 │ 3 │ [1,2,3] │ │ 1 │ 4 │ 4 │ [1,2,3,4] │ │ 1 │ 5 │ 5 │ [1,2,3,4,5] │ └──────────┴───────┴───────┴──────────────┘ ``` ```sql theme={null} -- краткая форма (рамка окна ограничена началом партиции и текущей строкой) -- эквивалент `ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` SELECT part_key, value, order, groupArray(value) OVER (PARTITION BY part_key ORDER BY order ASC) AS frame_values_short, groupArray(value) OVER (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values_short─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1] │ [1] │ │ 1 │ 2 │ 2 │ [1,2] │ [1,2] │ │ 1 │ 3 │ 3 │ [1,2,3] │ [1,2,3] │ │ 1 │ 4 │ 4 │ [1,2,3,4] │ [1,2,3,4] │ │ 1 │ 5 │ 5 │ [1,2,3,4,5] │ [1,2,3,4,5] │ └──────────┴───────┴───────┴────────────────────┴──────────────┘ ``` ```sql theme={null} -- рамка окна ограничена началом партиции и текущей строкой, но порядок сортировки обратный SELECT part_key, value, order, groupArray(value) OVER (PARTITION BY part_key ORDER BY order DESC) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [5,4,3,2,1] │ │ 1 │ 2 │ 2 │ [5,4,3,2] │ │ 1 │ 3 │ 3 │ [5,4,3] │ │ 1 │ 4 │ 4 │ [5,4] │ │ 1 │ 5 │ 5 │ [5] │ └──────────┴───────┴───────┴──────────────┘ ``` ```sql theme={null} -- скользящая рамка окна — 1 PRECEDING ROW AND CURRENT ROW SELECT part_key, value, order, groupArray(value) OVER ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1] │ │ 1 │ 2 │ 2 │ [1,2] │ │ 1 │ 3 │ 3 │ [2,3] │ │ 1 │ 4 │ 4 │ [3,4] │ │ 1 │ 5 │ 5 │ [4,5] │ └──────────┴───────┴───────┴──────────────┘ ``` ```sql theme={null} -- скользящая рамка окна - ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING SELECT part_key, value, order, groupArray(value) OVER ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING ) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1,2,3,4,5] │ │ 1 │ 2 │ 2 │ [1,2,3,4,5] │ │ 1 │ 3 │ 3 │ [2,3,4,5] │ │ 1 │ 4 │ 4 │ [3,4,5] │ │ 1 │ 5 │ 5 │ [4,5] │ └──────────┴───────┴───────┴──────────────┘ ``` ```sql theme={null} -- row_number не учитывает рамку окна, поэтому rn_1 = rn_2 = rn_3 != rn_4 SELECT part_key, value, order, groupArray(value) OVER w1 AS frame_values, row_number() OVER w1 AS rn_1, sum(1) OVER w1 AS rn_2, row_number() OVER w2 AS rn_3, sum(1) OVER w2 AS rn_4 FROM wf_frame WINDOW w1 AS (PARTITION BY part_key ORDER BY order DESC), w2 AS ( PARTITION BY part_key ORDER BY order DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) ORDER BY part_key ASC, value ASC; ``` ┌─part\_key─┬─value─┬─order─┬─frame\_values─┬─rn\_1─┬─rn\_2─┬─rn\_3─┬─rn\_4─┐ │ 1 │ 1 │ 1 │ \[5,4,3,2,1] │ 5 │ 5 │ 5 │ 2 │ │ 1 │ 2 │ 2 │ \[5,4,3,2] │ 4 │ 4 │ 4 │ 2 │ │ 1 │ 3 │ 3 │ \[5,4,3] │ 3 │ 3 │ 3 │ 2 │ │ 1 │ 4 │ 4 │ \[5,4] │ 2 │ 2 │ 2 │ 2 │ │ 1 │ 5 │ 5 │ \[5] │ 1 │ 1 │ 1 │ 1 │ └──────────┴───────┴───────┴──────────────┴──────┴──────┴──────┴──────┘ ```` ```sql -- first_value и last_value учитывают границы рамки окна SELECT groupArray(value) OVER w1 AS frame_values_1, first_value(value) OVER w1 AS first_value_1, last_value(value) OVER w1 AS last_value_1, groupArray(value) OVER w2 AS frame_values_2, first_value(value) OVER w2 AS first_value_2, last_value(value) OVER w2 AS last_value_2 FROM wf_frame WINDOW w1 AS (PARTITION BY part_key ORDER BY order ASC), w2 AS (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ORDER BY part_key ASC, value ASC; ┌─frame_values_1─┬─first_value_1─┬─last_value_1─┬─frame_values_2─┬─first_value_2─┬─last_value_2─┐ │ [1] │ 1 │ 1 │ [1] │ 1 │ 1 │ │ [1,2] │ 1 │ 2 │ [1,2] │ 1 │ 2 │ │ [1,2,3] │ 1 │ 3 │ [2,3] │ 2 │ 3 │ │ [1,2,3,4] │ 1 │ 4 │ [3,4] │ 3 │ 4 │ │ [1,2,3,4,5] │ 1 │ 5 │ [4,5] │ 4 │ 5 │ └────────────────┴───────────────┴──────────────┴────────────────┴───────────────┴──────────────┘ ```` ```sql theme={null} -- второе значение в рамке окна SELECT groupArray(value) OVER w1 AS frame_values_1, nth_value(value, 2) OVER w1 AS second_value FROM wf_frame WINDOW w1 AS (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) ORDER BY part_key ASC, value ASC; ┌─frame_values_1─┬─second_value─┐ │ [1] │ 0 │ │ [1,2] │ 2 │ │ [1,2,3] │ 2 │ │ [1,2,3,4] │ 2 │ │ [2,3,4,5] │ 3 │ └────────────────┴──────────────┘ ``` ```sql theme={null} -- второе значение в рамке окна + Null для отсутствующих значений SELECT groupArray(value) OVER w1 AS frame_values_1, nth_value(toNullable(value), 2) OVER w1 AS second_value FROM wf_frame WINDOW w1 AS (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) ORDER BY part_key ASC, value ASC; ``` ┌─frame\_values\_1─┬─second\_value─┐ │ \[1] │ ᴺᵁᴸᴸ │ │ \[1,2] │ 2 │ │ \[1,2,3] │ 2 │ │ \[1,2,3,4] │ 2 │ │ \[2,3,4,5] │ 3 │ └────────────────┴──────────────┘ ``` ```
## Примеры из практики
Следующие примеры показывают, как решать распространённые практические задачи.
### Максимальная/общая зарплата по отделам
```sql theme={null} CREATE TABLE employees ( `department` String, `employee_name` String, `salary` Float ) ENGINE = Memory; INSERT INTO employees FORMAT Values ('Finance', 'Jonh', 200), ('Finance', 'Joan', 210), ('Finance', 'Jean', 505), ('IT', 'Tim', 200), ('IT', 'Anna', 300), ('IT', 'Elen', 500); ``` ```sql theme={null} SELECT department, employee_name AS emp, salary, max_salary_per_dep, total_salary_per_dep, round((salary / total_salary_per_dep) * 100, 2) AS `share_per_dep(%)` FROM ( SELECT department, employee_name, salary, max(salary) OVER wndw AS max_salary_per_dep, sum(salary) OVER wndw AS total_salary_per_dep FROM employees WINDOW wndw AS ( PARTITION BY department ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) ORDER BY department ASC, employee_name ASC ); ┌─department─┬─emp──┬─salary─┬─max_salary_per_dep─┬─total_salary_per_dep─┬─share_per_dep(%)─┐ │ Finance │ Jean │ 505 │ 505 │ 915 │ 55.19 │ │ Finance │ Joan │ 210 │ 505 │ 915 │ 22.95 │ │ Finance │ Jonh │ 200 │ 505 │ 915 │ 21.86 │ │ IT │ Anna │ 300 │ 500 │ 1000 │ 30 │ │ IT │ Elen │ 500 │ 500 │ 1000 │ 50 │ │ IT │ Tim │ 200 │ 500 │ 1000 │ 20 │ └────────────┴──────┴────────┴────────────────────┴──────────────────────┴──────────────────┘ ```
### Нарастающий итог
```sql theme={null} CREATE TABLE warehouse ( `item` String, `ts` DateTime, `value` Float ) ENGINE = Memory INSERT INTO warehouse VALUES ('sku38', '2020-01-01', 9), ('sku38', '2020-02-01', 1), ('sku38', '2020-03-01', -4), ('sku1', '2020-01-01', 1), ('sku1', '2020-02-01', 1), ('sku1', '2020-03-01', 1); ``` ```sql theme={null} SELECT item, ts, value, sum(value) OVER (PARTITION BY item ORDER BY ts ASC) AS stock_balance FROM warehouse ORDER BY item ASC, ts ASC; ┌─item──┬──────────────────ts─┬─value─┬─stock_balance─┐ │ sku1 │ 2020-01-01 00:00:00 │ 1 │ 1 │ │ sku1 │ 2020-02-01 00:00:00 │ 1 │ 2 │ │ sku1 │ 2020-03-01 00:00:00 │ 1 │ 3 │ │ sku38 │ 2020-01-01 00:00:00 │ 9 │ 9 │ │ sku38 │ 2020-02-01 00:00:00 │ 1 │ 10 │ │ sku38 │ 2020-03-01 00:00:00 │ -4 │ 6 │ └───────┴─────────────────────┴───────┴───────────────┘ ```
### Скользящее среднее (по 3 строкам)
```sql theme={null} CREATE TABLE sensors ( `metric` String, `ts` DateTime, `value` Float ) ENGINE = Memory; ``` insert into sensors values('cpu\_temp', '2020-01-01 00:00:00', 87), ('cpu\_temp', '2020-01-01 00:00:01', 77), ('cpu\_temp', '2020-01-01 00:00:02', 93), ('cpu\_temp', '2020-01-01 00:00:03', 87), ('cpu\_temp', '2020-01-01 00:00:04', 87), ('cpu\_temp', '2020-01-01 00:00:05', 87), ('cpu\_temp', '2020-01-01 00:00:06', 87), ('cpu\_temp', '2020-01-01 00:00:07', 87); ```` ```sql SELECT metric, ts, value, avg(value) OVER ( PARTITION BY metric ORDER BY ts ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_temp FROM sensors ORDER BY metric ASC, ts ASC; ┌─metric───┬──────────────────ts─┬─value─┬───moving_avg_temp─┐ │ cpu_temp │ 2020-01-01 00:00:00 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:00:01 │ 77 │ 82 │ │ cpu_temp │ 2020-01-01 00:00:02 │ 93 │ 85.66666666666667 │ │ cpu_temp │ 2020-01-01 00:00:03 │ 87 │ 85.66666666666667 │ │ cpu_temp │ 2020-01-01 00:00:04 │ 87 │ 89 │ │ cpu_temp │ 2020-01-01 00:00:05 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:00:06 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:00:07 │ 87 │ 87 │ └──────────┴─────────────────────┴───────┴───────────────────┘ ````
### Скользящее среднее (за 10 секунд)
```sql theme={null} SELECT metric, ts, value, avg(value) OVER (PARTITION BY metric ORDER BY ts RANGE BETWEEN 10 PRECEDING AND CURRENT ROW) AS moving_avg_10_seconds_temp FROM sensors ORDER BY metric ASC, ts ASC; ┌─metric───┬──────────────────ts─┬─value─┬─moving_avg_10_seconds_temp─┐ │ cpu_temp │ 2020-01-01 00:00:00 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:01:10 │ 77 │ 77 │ │ cpu_temp │ 2020-01-01 00:02:20 │ 93 │ 93 │ │ cpu_temp │ 2020-01-01 00:03:30 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:04:40 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:05:50 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:06:00 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:07:10 │ 87 │ 87 │ └──────────┴─────────────────────┴───────┴────────────────────────────┘ ```
### Скользящее среднее (за 10 дней)
Температура хранится с точностью до секунды, но с помощью `Range` и `ORDER BY toDate(ts)` мы формируем рамку окна размером 10 единиц, и благодаря `toDate(ts)` такой единицей является день. ```sql theme={null} CREATE TABLE sensors ( `metric` String, `ts` DateTime, `value` Float ) ENGINE = Memory; ``` insert into sensors values('ambient\_temp', '2020-01-01 00:00:00', 16), ('ambient\_temp', '2020-01-01 12:00:00', 16), ('ambient\_temp', '2020-01-02 11:00:00', 9), ('ambient\_temp', '2020-01-02 12:00:00', 9), ('ambient\_temp', '2020-02-01 10:00:00', 10), ('ambient\_temp', '2020-02-01 12:00:00', 10), ('ambient\_temp', '2020-02-10 12:00:00', 12), ('ambient\_temp', '2020-02-10 13:00:00', 12), ('ambient\_temp', '2020-02-20 12:00:01', 16), ('ambient\_temp', '2020-03-01 12:00:00', 16), ('ambient\_temp', '2020-03-01 12:00:00', 16), ('ambient\_temp', '2020-03-01 12:00:00', 16); ```` ```sql SELECT metric, ts, value, round(avg(value) OVER (PARTITION BY metric ORDER BY toDate(ts) RANGE BETWEEN 10 PRECEDING AND CURRENT ROW),2) AS moving_avg_10_days_temp FROM sensors ORDER BY metric ASC, ts ASC; ┌─metric───────┬──────────────────ts─┬─value─┬─moving_avg_10_days_temp─┐ │ ambient_temp │ 2020-01-01 00:00:00 │ 16 │ 16 │ │ ambient_temp │ 2020-01-01 12:00:00 │ 16 │ 16 │ │ ambient_temp │ 2020-01-02 11:00:00 │ 9 │ 12.5 │ │ ambient_temp │ 2020-01-02 12:00:00 │ 9 │ 12.5 │ │ ambient_temp │ 2020-02-01 10:00:00 │ 10 │ 10 │ │ ambient_temp │ 2020-02-01 12:00:00 │ 10 │ 10 │ │ ambient_temp │ 2020-02-10 12:00:00 │ 12 │ 11 │ │ ambient_temp │ 2020-02-10 13:00:00 │ 12 │ 11 │ │ ambient_temp │ 2020-02-20 12:00:01 │ 16 │ 13.33 │ │ ambient_temp │ 2020-03-01 12:00:00 │ 16 │ 16 │ │ ambient_temp │ 2020-03-01 12:00:00 │ 16 │ 16 │ │ ambient_temp │ 2020-03-01 12:00:00 │ 16 │ 16 │ └──────────────┴─────────────────────┴───────┴─────────────────────────┘ ````
## Ссылки
### GitHub Issues
План начальной поддержки оконных функций приведён [в этом issue](https://github.com/ClickHouse/ClickHouse/issues/18097). Все GitHub issues, связанные с оконными функциями, помечены тегом [comp-window-functions](https://github.com/ClickHouse/ClickHouse/labels/comp-window-functions).
### Тесты
Эти тесты содержат примеры поддерживаемой в настоящее время грамматики: [https://github.com/ClickHouse/ClickHouse/blob/master/tests/performance/window\_functions.xml](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](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/current/sql-select.html#SQL-WINDOW) [https://www.postgresql.org/docs/devel/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS](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/functions-window.html) [https://www.postgresql.org/docs/devel/tutorial-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-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-usage.html) [https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html](https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html)
## Связанные материалы
* Блог: [Работа с временными рядами в ClickHouse](https://clickhouse.com/blog/working-with-time-series-data-and-functions-ClickHouse) * Блог: [Оконные функции и функции для работы с массивами для последовательностей коммитов Git](https://clickhouse.com/blog/clickhouse-window-array-functions-git-commits) * Блог: [Загрузка данных в ClickHouse — часть 3: использование S3](https://clickhouse.com/blog/getting-data-into-clickhouse-part-3-s3)