SQL Window Functions: la guía definitiva con 20 ejemplos

Las window functions son, probablemente, la característica SQL más infravalorada por analistas y Data Engineers junior. Permiten hacer cálculos sobre un conjunto de filas relacionadas sin colapsar el resultado en un GROUP BY. Cuando las dominas, eliminas decenas de subconsultas y CTEs innecesarias de tu código.

Qué es la cláusula OVER

La cláusula OVER() es lo que convierte una función normal en una window function. Define la “ventana” de filas sobre la que opera la función para cada fila del resultado.

SELECT
    columna1,
    columna2,
    funcion() OVER (
        PARTITION BY columna_particion
        ORDER BY columna_orden
        ROWS BETWEEN inicio AND fin
    ) AS resultado
FROM tabla;

• PARTITION BY: divide las filas en grupos (como un GROUP BY, pero sin colapsar filas)
• ORDER BY: define el orden dentro de cada partición
• ROWS/RANGE BETWEEN: define el marco de filas para funciones de agregación

Funciones de Ranking

ROW_NUMBER — número único por fila

-- Ejemplo 1: numerar pedidos por cliente segun fecha
SELECT
    cliente_id,
    pedido_id,
    fecha_pedido,
    ROW_NUMBER() OVER (
        PARTITION BY cliente_id
        ORDER BY fecha_pedido
    ) AS num_pedido
FROM pedidos;

-- Ejemplo 2: obtener solo el primer pedido de cada cliente
WITH pedidos_numerados AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY fecha_pedido) AS rn
    FROM pedidos
)
SELECT * FROM pedidos_numerados WHERE rn = 1;

RANK y DENSE_RANK — ranking con empates

-- Ejemplo 3: ranking de ventas por region
-- RANK salta numeros en empates (1,2,2,4), DENSE_RANK no (1,2,2,3)
SELECT
    region,
    vendedor,
    total_ventas,
    RANK()       OVER (PARTITION BY region ORDER BY total_ventas DESC) AS rank_con_salto,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY total_ventas DESC) AS rank_denso
FROM resumen_ventas;

-- Ejemplo 4: top 3 productos por categoria
WITH ranking AS (
    SELECT
        categoria, producto, ventas,
        DENSE_RANK() OVER (PARTITION BY categoria ORDER BY ventas DESC) AS dr
    FROM ventas_producto
)
SELECT * FROM ranking WHERE dr <= 3;

NTILE — dividir en cuartiles o percentiles

-- Ejemplo 5: segmentar clientes en cuartiles de gasto
SELECT
    cliente_id,
    gasto_anual,
    NTILE(4) OVER (ORDER BY gasto_anual DESC) AS cuartil
    -- cuartil 1 = top 25% de gasto
FROM clientes;

-- Ejemplo 6: deciles de precio de productos
SELECT
    producto_id, precio,
    NTILE(10) OVER (ORDER BY precio) AS decil_precio
FROM productos;

Funciones de Desplazamiento

LAG y LEAD — acceder a filas anteriores o siguientes

-- Ejemplo 7: variacion de ventas mes a mes
SELECT
    mes, ventas,
    LAG(ventas, 1, 0) OVER (ORDER BY mes) AS ventas_mes_anterior,
    ventas - LAG(ventas, 1, 0) OVER (ORDER BY mes) AS variacion_absoluta,
    ROUND(100.0 * (ventas - LAG(ventas,1) OVER (ORDER BY mes))
          / NULLIF(LAG(ventas,1) OVER (ORDER BY mes), 0), 2) AS variacion_pct
FROM ventas_mensuales;

-- Ejemplo 8: tiempo entre compras de un cliente
SELECT
    cliente_id, fecha_compra,
    LAG(fecha_compra) OVER (PARTITION BY cliente_id ORDER BY fecha_compra) AS compra_anterior,
    DATE_DIFF(fecha_compra,
              LAG(fecha_compra) OVER (PARTITION BY cliente_id ORDER BY fecha_compra),
              DAY) AS dias_entre_compras
FROM pedidos;

-- Ejemplo 9: ver el siguiente evento de un usuario
SELECT
    usuario_id, evento, timestamp,
    LEAD(evento, 1) OVER (PARTITION BY usuario_id ORDER BY timestamp) AS siguiente_evento,
    LEAD(timestamp, 1) OVER (PARTITION BY usuario_id ORDER BY timestamp) AS ts_siguiente
FROM eventos_sesion;

FIRST_VALUE y LAST_VALUE

-- Ejemplo 10: precio de la primera compra de cada cliente
SELECT
    cliente_id, fecha_compra, importe,
    FIRST_VALUE(importe) OVER (
        PARTITION BY cliente_id
        ORDER BY fecha_compra
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS importe_primera_compra
FROM pedidos;

-- Ejemplo 11: maximo en ventana movil de 3 filas
SELECT
    fecha, ventas,
    LAST_VALUE(ventas) OVER (
        ORDER BY fecha
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS ventas_ultimo_en_ventana
FROM ventas_diarias;

Funciones de Agregación como Ventana

-- Ejemplo 12: total acumulado de ventas (running total)
SELECT
    fecha, ventas_dia,
    SUM(ventas_dia) OVER (ORDER BY fecha ROWS UNBOUNDED PRECEDING) AS ventas_acumuladas
FROM ventas_diarias;

-- Ejemplo 13: porcentaje sobre el total de la particion
SELECT
    region, vendedor, ventas,
    SUM(ventas) OVER (PARTITION BY region) AS total_region,
    ROUND(100.0 * ventas / SUM(ventas) OVER (PARTITION BY region), 2) AS pct_region
FROM ventas;

-- Ejemplo 14: media movil de 7 dias
SELECT
    fecha, ventas,
    AVG(ventas) OVER (
        ORDER BY fecha
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS media_movil_7d
FROM ventas_diarias;

-- Ejemplo 15: count acumulado de nuevos clientes por mes
SELECT
    mes_alta, nuevos_clientes,
    COUNT(*) OVER (ORDER BY mes_alta ROWS UNBOUNDED PRECEDING) AS total_acumulado
FROM resumen_clientes;

ROWS BETWEEN y RANGE BETWEEN

Estas cláusulas definen el marco de filas para funciones de agregación. La diferencia entre ROWS y RANGE es sutil pero importante:

• ROWS: basado en posición física de filas (más predecible)
• RANGE: basado en valor lógico (puede incluir empates)

-- Ejemplo 16: suma de los ultimos 3 dias (incluyendo hoy)
SUM(ventas) OVER (ORDER BY fecha ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

-- Ejemplo 17: total desde el inicio del periodo hasta el final
SUM(ventas) OVER (ORDER BY fecha ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

-- Ejemplo 18: maximo en ventana de 5 filas centrada en la fila actual
MAX(precio) OVER (ORDER BY fecha ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

-- Ejemplo 19: suma de todos los valores iguales o menores (RANGE)
SUM(importe) OVER (ORDER BY importe RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- Ejemplo 20: ventas YTD vs ventas del anyo completo
SELECT
    fecha, ventas,
    SUM(ventas) OVER (PARTITION BY EXTRACT(YEAR FROM fecha)
                      ORDER BY fecha
                      ROWS UNBOUNDED PRECEDING) AS ventas_ytd,
    SUM(ventas) OVER (PARTITION BY EXTRACT(YEAR FROM fecha)) AS ventas_anyo_completo
FROM ventas_diarias;

Compatibilidad entre plataformas