lección 5

Acumulados y medias móviles: FRAMES de ventana

ROWS BETWEEN, running totals, moving averages. El "marco" de la ventana explicado visualmente.

⏱ 50 min

Hasta ahora hemos usado window functions donde cada fila veía TODO su grupo (PARTITION BY sin restricciones). Pero hay situaciones donde no quieres el total de todo el grupo — quieres el total HASTA ESTA FILA (un acumulado), o el promedio de LAS ÚLTIMAS 3 FILAS (una media móvil). Aquí es donde entran los frames de ventana: la parte más poderosa y menos entendida de las window functions.

La analogía: imagina que estás en un tren mirando por la ventana. La ventana tiene un tamaño fijo — quizás ves 3 vagones hacia atrás y 1 hacia adelante. A medida que el tren avanza, la ventana se mueve contigo, siempre mostrándote los mismos N elementos relativos a tu posición. Eso es exactamente un frame: una sub-ventana que se desliza con cada fila.

### Running total: el acumulado progresivo

El running total (suma acumulada) es probablemente el caso de uso más común de frames. "¿Cuánto llevamos vendido HASTA HOY?" No el total del mes completo, sino el total que crece día a día. Es lo que ves en tu cuenta bancaria: el saldo es un running total de todos tus movimientos.

1-- Running total: suma acumulada dia a dia
2SELECT
3    fecha,
4    importe_diario,
5    SUM(importe_diario) OVER (
6        ORDER BY fecha
7        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
8    ) AS acumulado
9FROM ventas_diarias;
10
11-- Resultado:
12-- 2024-01-01 | 100 | acumulado: 100
13-- 2024-01-02 | 150 | acumulado: 250   (100+150)
14-- 2024-01-03 |  80 | acumulado: 330   (100+150+80)
15-- 2024-01-04 | 200 | acumulado: 530   (100+150+80+200)

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW = "desde el inicio hasta aquí"

### La sintaxis del frame: ROWS BETWEEN ... AND ...

El frame define qué filas de la partición participan en el cálculo para cada fila. La sintaxis completa es ROWS BETWEEN [inicio] AND [fin], donde inicio y fin pueden ser:

UNBOUNDED PRECEDING — desde la primera fila de la partición
N PRECEDING — N filas antes de la actual
CURRENT ROW — la fila actual
N FOLLOWING — N filas después de la actual
UNBOUNDED FOLLOWING — hasta la última fila de la partición

El frame define cuántas filas "ve" tu función en cada paso.

Trampa sutil: cuando usas SUM() OVER (PARTITION BY x ORDER BY y) SIN especificar frame, SQL aplica implícitamente RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — es decir, un acumulado. Si quieres el total completo de la partición, omite el ORDER BY o especifica ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Consejo práctico, y es de los importantes: ROWS cuenta FILAS y RANGE cuenta VALORES. Parece lo mismo y no lo es. ROWS BETWEEN 6 PRECEDING toma las seis filas anteriores, sean del día que sean. Si tu tabla tiene un hueco —un fin de semana, un festivo, un día que la tienda no abrió—, esas seis filas se van más atrás en el calendario sin avisarte. RANGE BETWEEN INTERVAL 6 DAYS PRECEDING toma los seis días anteriores de calendario, tenga filas o no. Los dos dan lo mismo SOLO si tu serie tiene una fila por día sin faltar ninguno. En cuanto haya huecos, "media móvil de 7 días" con ROWS deja de ser de 7 días. La regla: si el número que le pones al frame son días, semanas o meses, usa RANGE con INTERVAL. Si de verdad quieres "las N filas anteriores" pase lo que pase, usa ROWS. Y antes de cualquiera de los dos, cuenta si a tu serie le faltan días.

1-- ROWS vs RANGE: la diferencia se ve con valores repetidos
2WITH datos AS (
3    SELECT * FROM (VALUES
4        (1, 100), (2, 100), (3, 100), (4, 200), (5, 300)
5    ) AS t(id, importe)
6)
7SELECT
8    id, importe,
9    SUM(importe) OVER (ORDER BY importe ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_rows,
10    SUM(importe) OVER (ORDER BY importe RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_range
11FROM datos;
12
13-- Resultado:
14-- id=1 | 100 | sum_rows: 100 | sum_range: 300  ← RANGE incluye los 3 de valor 100
15-- id=2 | 100 | sum_rows: 200 | sum_range: 300  ← mismo: agrupa por VALOR
16-- id=3 | 100 | sum_rows: 300 | sum_range: 300
17-- id=4 | 200 | sum_rows: 500 | sum_range: 500  ← aquí coinciden (no hay empates)
18-- id=5 | 300 | sum_rows: 800 | sum_range: 800

ROWS avanza fila a fila (100, 200, 300...). RANGE salta al final del grupo de valores iguales (300, 300, 300...).

### Media móvil: suavizar el ruido

Las ventas diarias son ruidosas: un lunes vende poco, un viernes vende mucho, el Black Friday dispara todo. Para ver la TENDENCIA real, usas una media móvil: el promedio de los últimos N días. Suaviza los picos y muestra hacia dónde vas realmente. Es exactamente lo que hacen los gráficos de bolsa.

1-- Media movil de 7 dias (suaviza ruido semanal)
2SELECT
3    fecha,
4    importe_diario,
5    ROUND(
6        AVG(importe_diario) OVER (
7            ORDER BY fecha
8            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
9        ), 2
10    ) AS media_movil_7d
11FROM ventas_diarias;
12
13-- Media movil de 30 dias (tendencia mensual)
14SELECT
15    fecha,
16    importe_diario,
17    ROUND(
18        AVG(importe_diario) OVER (
19            ORDER BY fecha
20            ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
21        ), 2
22    ) AS media_movil_30d
23FROM ventas_diarias;

6 PRECEDING + CURRENT ROW = 7 filas en total. Para 30 días: 29 PRECEDING.

### Acumulado con reset mensual

Un patrón muy útil: quieres el acumulado de ventas pero que se reinicie cada mes. Así ves "ícuánto llevamos este mes?" en vez del total desde el inicio de los tiempos. La clave: PARTITION BY el mes.

1-- Acumulado que se reinicia cada mes
2SELECT
3    fecha,
4    importe_diario,
5    SUM(importe_diario) OVER (
6        PARTITION BY DATE_TRUNC('month', fecha)
7        ORDER BY fecha
8        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
9    ) AS acumulado_mes
10FROM ventas_diarias
11ORDER BY fecha;
12
13-- Resultado:
14-- 2024-01-01 | 100 | acumulado_mes: 100
15-- 2024-01-02 | 150 | acumulado_mes: 250
16-- ...
17-- 2024-01-31 | 200 | acumulado_mes: 4500  (total de enero)
18-- 2024-02-01 | 180 | acumulado_mes: 180   → se reinicio!

PARTITION BY mes hace que el acumulado se reinicie al cambiar de mes.

Consejo de senior: las medias móviles de 7 y 30 días son estándar en dashboards de producto. Si estás construyendo un dashboard de métricas de negocio, las medias móviles son la forma correcta de mostrar tendencias sin que un día atípico distorsione la historia.

### Porcentaje acumulado: la curva de Pareto

Combinar un running total con el total global te da el porcentaje acumulado — la base del análisis de Pareto (el famoso 80/20). "¿Qué porcentaje de mis clientes genera el 80% de los ingresos?"

1-- Analisis Pareto: que % de clientes genera el 80% de ingresos
2WITH clientes_revenue AS (
3    SELECT
4        cliente_id,
5        SUM(importe) AS revenue
6    FROM pedidos
7    WHERE estado = 'completado'
8    GROUP BY cliente_id
9)
10SELECT
11    cliente_id,
12    revenue,
13    SUM(revenue) OVER (ORDER BY revenue DESC) AS acumulado,
14    ROUND(
15        100.0 * SUM(revenue) OVER (ORDER BY revenue DESC) 
16        / SUM(revenue) OVER (), 1
17    ) AS pct_acumulado
18FROM clientes_revenue
19ORDER BY revenue DESC;

Cuando pct_acumulado llega a 80, has encontrado a tus clientes "vitales" (Pareto).

El análisis de Pareto con window functions es una pregunta CLÁSICA de entrevistas en empresas de datos. Apréndela bien: agrupar → ordenar por valor DESC → acumulado → porcentaje acumulado → filtrar al 80%.

Regístrate para guardar tu progreso.

## comentarios

Reporta erratas, ayuda a otros o comparte tu opinión. Sé constructivo.

Inicia sesión para comentar y responder.

cargando comentarios...