Acumulados y medias móviles: FRAMES de ventana

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í"

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...).

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.

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.

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).