> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-home-button.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Página de visão geral sobre funções de janela # Funções de janela As funções de janela permitem realizar cálculos em um conjunto de linhas relacionadas à linha atual. Alguns dos cálculos que você pode fazer são semelhantes aos que podem ser feitos com uma função de agregação, mas uma função de janela não faz com que as linhas sejam agrupadas em uma única saída — as linhas individuais ainda são retornadas.
## Funções de janela padrão
O ClickHouse oferece suporte à sintaxe padrão para definir janelas e funções de janela. A tabela abaixo indica se um recurso tem suporte no momento. | Recurso | Suportado? | | ------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | especificação de janela ad hoc (`count(*) over (partition by id order by time desc)`) | ✅ | | expressões que envolvem funções de janela, por exemplo, `(count(*) over ()) / 2)` | ✅ | | cláusula `WINDOW` (`select ... from table window w as (partition by id)`) | ✅ | | frame `ROWS` | ✅ | | frame `RANGE` | ✅ (o padrão) | | sintaxe `INTERVAL` para frame `DateTime` `RANGE OFFSET` | ❌ (em vez disso, especifique o número de segundos (`RANGE` funciona com qualquer tipo numérico).) | | frame `GROUPS` | ❌ | | Cálculo de funções agregadas sobre um frame (`sum(value) over (order by time)`) | ✅ (Há suporte para todas as funções agregadas) | | `rank()`, `dense_rank()`, `row_number()` | ✅
Alias: `denseRank()` | | `percent_rank()` | ✅ Calcula com eficiência a posição relativa de um valor dentro de uma partição em um conjunto de dados. Essa função substitui, na prática, o cálculo manual em SQL, mais verboso e computacionalmente mais custoso, expresso como `ifNull((rank() OVER(PARTITION BY x ORDER BY y) - 1) / nullif(count(1) OVER(PARTITION BY x) - 1, 0), 0)`
Alias: `percentRank()` | | `cume_dist()` | ✅ Calcula a distribuição cumulativa de um valor dentro de um grupo de valores. Retorna a porcentagem de linhas com valores menores ou iguais ao valor da linha atual. | | `lag/lead(value, offset)` | ✅
Você também pode usar uma das soluções alternativas a seguir:
1) `any(value) over (.... rows between preceding and preceding)`, ou `following` para `lead`
2) `lagInFrame/leadInFrame`, que são análogas, mas respeitam o frame da janela. Para obter um comportamento idêntico a `lag/lead`, use `rows between unbounded preceding and unbounded following` | | ntile(buckets) | ✅
Especifique a janela assim: (partition by x order by y rows between unbounded preceding and unbounded following). |

Funções de janela específicas do ClickHouse

Existe também a seguinte função de janela específica do ClickHouse:

nonNegativeDerivative(metric\_column, timestamp\_column\[, INTERVAL X UNITS])

Calcula a derivada não negativa da `metric_column` fornecida em relação à `timestamp_column`. `INTERVAL` pode ser omitido; o padrão é `INTERVAL 1 SECOND`. O valor calculado para cada linha é: * `0` para a 1ª linha, * ${\text{metric}_i - \text{metric}_{i-1} \over \text{timestamp}_i - \text{timestamp}_{i-1}} * \text{interval}$ para a $i_{th}$ linha.
## Sintaxe
```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` - define como dividir um conjunto de resultados em grupos. * `ORDER BY` - define como ordenar as linhas dentro do grupo durante o cálculo de aggregate\_function. * `ROWS or RANGE` - define os limites de um frame; aggregate\_function é calculada dentro desse frame. * `WINDOW` - permite que várias expressões usem a mesma definição de janela. ```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) ```
### Funções
Estas funções só podem ser usadas como funções de janela. * [`row_number()`](/reference/functions/window-functions/row_number) - Numera a linha atual dentro da partição, começando em 1. * [`first_value(x)`](/reference/functions/window-functions/first_value) - Retorna o primeiro valor avaliado dentro do frame ordenado. * [`last_value(x)`](/reference/functions/window-functions/last_value) - Retorna o último valor avaliado dentro do frame ordenado. * [`nth_value(x, offset)`](/reference/functions/window-functions/nth_value) - Retorna o primeiro valor não NULL avaliado na enésima linha (`offset`) dentro do frame ordenado. * [`rank()`](/reference/functions/window-functions/rank) - Classifica a linha atual dentro da partição, com lacunas. * [`dense_rank()`](/reference/functions/window-functions/dense_rank) - Classifica a linha atual dentro da partição, sem lacunas. * [`lagInFrame(x)`](/reference/functions/window-functions/lagInFrame) - Retorna um valor avaliado na linha que está em um deslocamento físico especificado antes da linha atual dentro do frame ordenado. * [`leadInFrame(x)`](/reference/functions/window-functions/leadInFrame) - Retorna um valor avaliado na linha que está a um número de linhas após a linha atual dentro do frame ordenado.
## Exemplos
Veja alguns exemplos de como as funções de janela podem ser usadas.
### Numeração de linhas
```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 │ └─────────────────┴────────┴─────┴──────┴───────────┘ ```
### Funções de agregação
Compare o salário de cada jogador com a média do seu time. ```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 │ └─────────────────┴────────┴───────────────────────────┴─────────┴────────┘ ``` Compare o salário de cada jogador com o salário máximo de sua equipe. ```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 │ └─────────────────┴────────┴───────────────────────────┴─────────┴────────┘ ```
### Particionamento por coluna
```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 └──────────┴───────┴───────┴──────────────┘ ```
### Definição dos limites do frame
```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} -- O frame é delimitado pelos limites de uma partição (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} -- forma curta - sem expressão de limite, sem order by, -- equivalente a `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} -- o frame é delimitado pelo início de uma partição e pela linha atual 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} -- forma curta (o frame é delimitado pelo início de uma partição e a linha atual) -- equivalente a `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} -- o frame é delimitado pelo início de uma partição e pela linha atual, mas a ordem é invertida 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} -- frame deslizante - 1 LINHA PRECEDENTE E LINHA ATUAL 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} -- frame deslizante - 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 não respeita o frame, portanto 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 e last_value respeitam o frame 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} -- segundo valor no frame 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} -- segundo valor dentro do frame + Null para valores ausentes 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 │ └────────────────┴──────────────┘ ``` ```
## Exemplos práticos
Os exemplos a seguir mostram como resolver problemas comuns em cenários reais.
### Salário máximo/total por departamento
```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 │ └────────────┴──────┴────────┴────────────────────┴──────────────────────┴──────────────────┘ ```
### Soma cumulativa
```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 │ └───────┴─────────────────────┴───────┴───────────────┘ ```
### Média móvel / deslizante (em 3 linhas)
```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 │ └──────────┴─────────────────────┴───────┴───────────────────┘ ````
### Média móvel/deslizante (a cada 10 segundos)
```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 │ └──────────┴─────────────────────┴───────┴────────────────────────────┘ ```
### Média móvel / deslizante (de 10 dias)
A temperatura é armazenada com precisão de segundos, mas, ao usar `Range` e `ORDER BY toDate(ts)`, formamos um frame de 10 unidades e, por causa de `toDate(ts)`, a unidade é um dia. ```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 │ └──────────────┴─────────────────────┴───────┴─────────────────────────┘ ````
## Referências
### Issues no GitHub
O plano para o suporte inicial a funções de janela está [nesta issue](https://github.com/ClickHouse/ClickHouse/issues/18097). Todas as issues do GitHub relacionadas a funções de janela têm a label [comp-window-functions](https://github.com/ClickHouse/ClickHouse/labels/comp-window-functions).
### Testes
Estes testes contêm exemplos da gramática atualmente suportada: [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)
### Documentação do 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)
### Documentação do 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)
## Conteúdo relacionado
* Blog: [Trabalhando com dados de séries temporais no ClickHouse](https://clickhouse.com/blog/working-with-time-series-data-and-functions-ClickHouse) * Blog: [Funções de janela e array para sequências de commits do Git](https://clickhouse.com/blog/clickhouse-window-array-functions-git-commits) * Blog: [Inserindo dados no ClickHouse - Parte 3 - Usando S3](https://clickhouse.com/blog/getting-data-into-clickhouse-part-3-s3)