Window functions con frames ROWS, RANGE y GROUPS

Qué es un frame dentro de una window function

Una window function básica tiene la forma funcion() OVER (PARTITION BY x ORDER BY y). Sin frame explícito, la "ventana" sobre la que opera la función depende del tipo: las funciones de agregación (SUM, AVG) usan un frame por defecto de RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, mientras que las funciones de ranking (ROW_NUMBER, RANK) no tienen frame.

Un frame explícito permite acotar exactamente sobre qué filas se aplica la función. Los tres tipos:

• ROWS BETWEEN: cuenta filas físicas a partir de la fila actual.
• RANGE BETWEEN: mide por valor de la columna del ORDER BY. Aplica solo si hay una única columna de ordenación y es numérica o temporal.
• GROUPS BETWEEN (SQL:2016, PG 11+): cuenta grupos de filas con el mismo valor en ORDER BY (peers).

Los modificadores son: UNBOUNDED PRECEDING (desde el inicio), N PRECEDING (N unidades antes), CURRENT ROW (fila actual), N FOLLOWING (N unidades después), UNBOUNDED FOLLOWING (hasta el final).

Running total y media móvil

El running total clásico usa un frame abierto por la izquierda:

SELECT fecha, importe,
       SUM(importe) OVER (ORDER BY fecha ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS acumulado
FROM ventas
ORDER BY fecha;

La media móvil de los últimos 7 días se escribe con ROWS 6 PRECEDING:

SELECT fecha, importe,
       AVG(importe) OVER (
           ORDER BY fecha
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS media_7d
FROM ventas
ORDER BY fecha;

Si queremos una media centrada (3 antes + actual + 3 después) se usa un rango simétrico:

SELECT fecha, importe,
       AVG(importe) OVER (
           ORDER BY fecha
           ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
       ) AS media_centrada_7d
FROM ventas;

Diferencia entre ROWS y RANGE

Con ROWS, el frame cuenta filas físicas. Con RANGE, cuenta valores de la columna ORDER BY. La diferencia es crucial cuando hay empates (peers) o huecos temporales:

-- Ventas diarias con huecos (faltan algunos dias)
-- 2026-04-01 : 100
-- 2026-04-02 : 150
-- 2026-04-05 : 200  <- hueco de 3 dias
-- 2026-04-06 : 300

-- ROWS: suma las 2 filas anteriores fisicas
SELECT fecha, importe,
       SUM(importe) OVER (ORDER BY fecha ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS suma_rows
FROM ventas;
-- 04-01: 100 (solo la actual)
-- 04-02: 250 (100+150)
-- 04-05: 450 (100+150+200)  <- salta el hueco
-- 04-06: 650 (150+200+300)

-- RANGE: suma filas dentro del intervalo de valor (2 dias)
SELECT fecha, importe,
       SUM(importe) OVER (
           ORDER BY fecha
           RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW
       ) AS suma_range
FROM ventas;
-- 04-01: 100
-- 04-02: 250 (100+150)
-- 04-05: 200 (solo la actual, no hay nada en 04-03 ni 04-04)
-- 04-06: 500 (200+300, 04-05 y 04-06)

Usa ROWS para N posiciones físicas. Usa RANGE para N unidades de tiempo/valor.

LAG y LEAD con offset

LAG(col, N) devuelve el valor de la columna N filas antes; LEAD(col, N) N filas después. Ambas admiten un valor por defecto para casos sin fila previa/siguiente:

SELECT fecha, importe,
       LAG(importe, 1, 0) OVER (ORDER BY fecha) AS importe_ayer,
       importe - LAG(importe, 1, 0) OVER (ORDER BY fecha) AS variacion,
       LEAD(importe, 1) OVER (ORDER BY fecha) AS importe_manana
FROM ventas;

Patrón típico: comparar el ingreso de cada mes con el del mismo mes del año pasado (LAG(col, 12) si las filas son mensuales).

Percentiles y cuartiles

Las funciones de distribución son útiles para análisis:

SELECT cliente_id, total_compras,
       PERCENT_RANK() OVER (ORDER BY total_compras) AS percentil,
       CUME_DIST() OVER (ORDER BY total_compras) AS distribucion_acumulada,
       NTILE(4) OVER (ORDER BY total_compras) AS cuartil
FROM resumen_clientes
ORDER BY total_compras;

• PERCENT_RANK: posición relativa entre 0 y 1.
• CUME_DIST: fracción de filas con valor <= actual.
• NTILE(4): divide en 4 buckets (cuartiles). Con NTILE(10) se obtienen deciles.

FILTER para agregados condicionales

La cláusula FILTER (WHERE ...) se combina con window functions para contar o sumar condicionalmente dentro de la ventana (PostgreSQL, SQL Server; en MySQL se emula con SUM(CASE WHEN ... THEN 1 ELSE 0 END)):

SELECT fecha,
       COUNT(*) FILTER (WHERE estado = 'completado') OVER (ORDER BY fecha ROWS 6 PRECEDING) AS completados_7d,
       COUNT(*) FILTER (WHERE estado = 'cancelado')  OVER (ORDER BY fecha ROWS 6 PRECEDING) AS cancelados_7d
FROM pedidos;

Combinando PARTITION BY con frames

El caso más potente: agregar por grupo y aplicar frame dentro de cada partición. Por ejemplo, media móvil por región:

SELECT region, fecha, ventas,
       AVG(ventas) OVER (
           PARTITION BY region
           ORDER BY fecha
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS media_7d_region
FROM ventas_diarias;

El PARTITION BY region reinicia el frame al cambiar de región: la media de Madrid no contamina la de Barcelona.

Buenas prácticas

• Siempre explicita el frame cuando uses agregados con ORDER BY para evitar sorpresas con el frame por defecto (RANGE UNBOUNDED PRECEDING).
• Usa ROWS si solo necesitas posiciones físicas (suele ser más rápido que RANGE).
• RANGE con INTERVAL requiere PostgreSQL; MySQL 8 solo admite RANGE con valores numéricos.
• Evita varias window functions con particiones distintas en la misma query si el dataset es grande: cada partición requiere una ordenación, que puede ser costosa. Agrupa las que compartan PARTITION BY ... ORDER BY reutilizando una cláusula WINDOW.

SELECT *,
       SUM(x) OVER w AS total,
       AVG(x) OVER w AS media,
       MAX(x) OVER w AS maximo
FROM tabla
WINDOW w AS (PARTITION BY region ORDER BY fecha ROWS 6 PRECEDING);