> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Página de resumen de las funciones de ventana # Funciones de ventana Las funciones de ventana permiten realizar cálculos sobre un conjunto de filas relacionadas con la fila actual. Algunos de los cálculos que puede realizar son similares a los que pueden hacerse con una función de agregación, pero una función de ventana no agrupa las filas en una única salida: se siguen devolviendo las filas individuales.
## Funciones de ventana estándar
ClickHouse admite la gramática estándar para definir ventanas y funciones de ventana. La siguiente tabla indica si una funcionalidad es compatible actualmente. | Característica | ¿Compatible? | | --------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | especificación ad hoc de ventana (`count(*) over (partition by id order by time desc)`) | ✅ | | expresiones que incluyen funciones de ventana, p. ej. `(count(*) over ()) / 2)` | ✅ | | cláusula `WINDOW` (`select ... from table window w as (partition by id)`) | ✅ | | marco `ROWS` | ✅ | | marco `RANGE` | ✅ (el valor predeterminado) | | sintaxis `INTERVAL` para el marco `DateTime` `RANGE OFFSET` | ❌ (especifique en su lugar el número de segundos (`RANGE` funciona con cualquier tipo numérico).) | | marco `GROUPS` | ❌ | | Cálculo de funciones de agregación sobre un marco (`sum(value) over (order by time)`) | ✅ (Se admiten todas las funciones de agregación) | | `rank()`, `dense_rank()`, `row_number()` | ✅
Alias: `denseRank()` | | `percent_rank()` | ✅ Calcula eficientemente la posición relativa de un valor dentro de una partición en un conjunto de datos. Esta función sustituye eficazmente el cálculo manual en SQL, más verboso y computacionalmente más costoso, expresado 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 la distribución acumulada de un valor dentro de un grupo de valores. Devuelve el porcentaje de filas con valores menores o iguales que el valor de la fila actual. | | `lag/lead(value, offset)` | ✅
También puede usar una de las siguientes alternativas:
1) `any(value) over (.... rows between preceding and preceding)`, o `following` para `lead`
2) `lagInFrame/leadInFrame`, que son análogas, pero respetan el marco de la ventana. Para obtener un comportamiento idéntico a `lag/lead`, use `rows between unbounded preceding and unbounded following` | | ntile(buckets) | ✅
Especifique la ventana así: (partition by x order by y rows between unbounded preceding and unbounded following). |

ClickHouse-specific window functions

También existe la siguiente función de ventana específica de ClickHouse:

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

Calcula la derivada no negativa de la `metric_column` indicada con respecto a `timestamp_column`. `INTERVAL` puede omitirse; el valor predeterminado es `INTERVAL 1 SECOND`. El valor calculado para cada fila es el siguiente: * `0` para la primera fila, * ${\text{metric}_i - \text{metric}_{i-1} \over \text{timestamp}_i - \text{timestamp}_{i-1}} * \text{interval}$ para la fila $i_{th}$.
## Sintaxis
```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 cómo dividir un conjunto de resultados en grupos. * `ORDER BY` - define cómo ordenar las filas dentro del grupo durante el cálculo de aggregate\_function. * `ROWS or RANGE` - define los límites del marco; aggregate\_function se calcula dentro de ese marco. * `WINDOW` - permite que varias expresiones usen la misma definición de ventana. ```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) ```
### Funciones
Estas funciones solo se pueden usar como función de ventana. * [`row_number()`](/reference/functions/window-functions/row_number) - Numera la fila actual dentro de su partición a partir de 1. * [`first_value(x)`](/reference/functions/window-functions/first_value) - Devuelve el primer valor evaluado dentro de su marco ordenado. * [`last_value(x)`](/reference/functions/window-functions/last_value) - Devuelve el último valor evaluado dentro de su marco ordenado. * [`nth_value(x, offset)`](/reference/functions/window-functions/nth_value) - Devuelve el primer valor no NULL evaluado en la enésima fila (`offset`) de su marco ordenado. * [`rank()`](/reference/functions/window-functions/rank) - Asigna un rango a la fila actual dentro de su partición con huecos. * [`dense_rank()`](/reference/functions/window-functions/dense_rank) - Asigna un rango a la fila actual dentro de su partición sin huecos. * [`lagInFrame(x)`](/reference/functions/window-functions/lagInFrame) - Devuelve un valor evaluado en la fila situada un desplazamiento físico especificado antes de la fila actual dentro del marco ordenado. * [`leadInFrame(x)`](/reference/functions/window-functions/leadInFrame) - Devuelve un valor evaluado en la fila situada un número de filas de desplazamiento después de la fila actual dentro del marco ordenado.
## Ejemplos
Veamos algunos ejemplos de cómo se pueden utilizar las funciones de ventana.
### Numerar filas
```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 │ └─────────────────┴────────┴─────┴──────┴───────────┘ ```
### Funciones de agregación
Compara el salario de cada jugador con el promedio de su equipo. ```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 el salario de cada jugador con el salario máximo de su equipo. ```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 │ └─────────────────┴────────┴───────────────────────────┴─────────┴────────┘ ```
### Particionamiento por columna
```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.er grupo │ 1 │ 3 │ 3 │ [1,2,3] │ <┘ │ 2 │ 0 │ 0 │ [0] │ <- 2.º grupo │ 3 │ 0 │ 0 │ [0] │ <- 3.er grupo └──────────┴───────┴───────┴──────────────┘ ```
### Límites del marco
```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} -- El marco está delimitado por los límites de una partición (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 corta - sin expresión de límite, sin 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} -- el frame está delimitado por el inicio de una partición y la fila actual 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 corta (el frame está delimitado por el inicio de una partición y la fila actual) -- 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} -- el marco está acotado por el inicio de una partición y la fila actual, pero el orden es descendente 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} -- marco deslizante - 1 FILA PRECEDENTE Y FILA ACTUAL 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} -- marco 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 no respeta el marco, por lo que 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 y last_value respetan el marco 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 dentro del marco 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 del marco + 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 │ └────────────────┴──────────────┘ ``` ```
## Ejemplos reales
Los siguientes ejemplos resuelven problemas habituales en el mundo real.
### Salario 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 │ └────────────┴──────┴────────┴────────────────────┴──────────────────────┴──────────────────┘ ```
### Suma acumulada
```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 │ └───────┴─────────────────────┴───────┴───────────────┘ ```
### Media móvil / deslizante (de 3 filas)
```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 │ └──────────┴─────────────────────┴───────┴───────────────────┘ ````
### Media móvil / deslizante (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 │ └──────────┴─────────────────────┴───────┴────────────────────────────┘ ```
### Media móvil / deslizante (por 10 días)
La temperatura se almacena con precisión de segundos, pero al usar `Range` y `ORDER BY toDate(ts)` formamos un marco de 10 unidades y, dado que `toDate(ts)` se utiliza, la unidad es un día. ```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 │ └──────────────┴─────────────────────┴───────┴─────────────────────────┘ ````
## Referencias
### Issues de GitHub
La hoja de ruta para la compatibilidad inicial con las funciones de ventana está [en este issue](https://github.com/ClickHouse/ClickHouse/issues/18097). Todos los issues de GitHub relacionados con las funciones de ventana tienen la etiqueta [comp-window-functions](https://github.com/ClickHouse/ClickHouse/labels/comp-window-functions).
### Pruebas
Estas pruebas contienen ejemplos de la sintaxis admitida actualmente: [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)
### Documentación de 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)
### Documentación de 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)
## Contenido relacionado
* Blog: [Trabajar con datos de series temporales en ClickHouse](https://clickhouse.com/blog/working-with-time-series-data-and-functions-ClickHouse) * Blog: [Funciones de ventana y de arrays para secuencias de commit de Git](https://clickhouse.com/blog/clickhouse-window-array-functions-git-commits) * Blog: [Carga de datos en ClickHouse - Parte 3 - Uso de S3](https://clickhouse.com/blog/getting-data-into-clickhouse-part-3-s3)