Funciones de ventana en SQL: ROW_NUMBER, RANK, LAG/LEAD en la práctica

Si ya te manejas con SELECT, WHERE y JOIN pero te atascas en preguntas como «encuentra los 3 productos principales de cada categoría» o «cuál es la variación porcentual de ingresos día a día», bienvenido a las funciones de ventana. Después de JOIN, esta es la herramienta que más usa un analista en SQL.

Lo mostraré todo sobre una tabla de pedidos sencilla:

CREATE TABLE orders (
  id          INT PRIMARY KEY,
  category    VARCHAR(50),
  product     VARCHAR(100),
  amount      NUMERIC(10, 2),
  created_at  DATE
);

Funciones de ventana frente a GROUP BY

La diferencia clave cabe en una palabra: GROUP BY colapsa las filas. Las funciones de ventana no.

Con GROUP BY pierdes el detalle. Entran un millón de pedidos, con GROUP BY category salen cinco filas, una por categoría. Estupendo para un informe, pero no puedes ver al mismo tiempo el pedido individual y el ticket medio de su categoría.

Con una función de ventana cada fila se queda en su sitio y aparece junto a ella un valor calculado:

SELECT
  id,
  category,
  amount,
  AVG(amount) OVER (PARTITION BY category) AS category_avg
FROM orders;

Léelo en voz alta: «importe promedio sobre la ventana, particionada por categoría». Para cada pedido, el promedio de su categoría aparece a su lado; no se descarta ninguna otra fila.

OVER(): tres mandos

Dentro de OVER() viven tres ajustes. Para la mayoría de las consultas del día a día solo necesitas los dos primeros.

1. PARTITION BY — cómo trocear

Sin PARTITION BY toda la tabla es una sola ventana. Con él, cada valor distinto de la columna obtiene su propio grupo:

-- Promedio de toda la tabla
AVG(amount) OVER ()

-- Promedio por categoría, por separado
AVG(amount) OVER (PARTITION BY category)

2. ORDER BY — en qué orden

Úsalo cuando importe la posición de la fila dentro de la ventana: «primera», «segunda», «la de al lado». Sin ORDER BY, ROW_NUMBER no tiene sentido. Con él, obtienes una numeración:

-- Pedidos dentro de una categoría, ordenados por importe descendente: 1, 2, 3, ...
ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC)

3. Cláusula de marco — el tercer mando opcional

El marco controla «qué filas vecinas entran en la agregación». Por ahora recuerda solo una regla: en cuanto aparece ORDER BY dentro de OVER(), una agregación como SUM pasa a modo acumulativo — sumando desde el comienzo de la ventana hasta la fila actual.

-- Total acumulado desde el comienzo de cada categoría
SUM(amount) OVER (PARTITION BY category ORDER BY created_at)

Esta es la trampa más común para principiantes: añades ORDER BY para «que quede más bonito» y la agregación se convierte sin avisar en un total acumulado. Tatúatelo.

ROW_NUMBER, RANK, DENSE_RANK — tres maneras de numerar

Se diferencian en cómo tratan los empates en la columna de ORDER BY.

Función	Con los valores 10, 10, 20 produce
ROW_NUMBER()	1, 2, 3 — siempre único
RANK()	1, 1, 3 — mismo rango, luego un salto
DENSE_RANK()	1, 1, 2 — mismo rango, sin salto

Caso práctico: los 3 productos principales por categoría

Esta es probablemente la tarea con funciones de ventana más habitual en las entrevistas de SQL. No se puede escribir de forma limpia con GROUP BY. Con ROW_NUMBER se resuelve en una sola pasada:

WITH ranked AS (
  SELECT
    category,
    product,
    SUM(amount) AS total,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY SUM(amount) DESC
    ) AS rn
  FROM orders
  GROUP BY category, product
)
SELECT category, product, total
FROM ranked
WHERE rn <= 3
ORDER BY category, rn;

Paso a paso:

1. Agrupa por (category, product) y suma los ingresos de cada producto. Obtienes una lista de productos con sus ingresos.
2. Dentro de cada categoría, numera los productos por ingresos descendentes. El líder obtiene rn = 1, el segundo rn = 2, y así sucesivamente.
3. La consulta exterior conserva solo las filas donde rn <= 3.

ROW_NUMBER garantiza un 1, 2, 3 estricto incluso cuando dos productos empatan en ingresos. Si quieres «todos los del top tres» — incluidos los empates en el segundo puesto — usa RANK en lugar de ROW_NUMBER.

LAG y LEAD — echar un vistazo a una fila vecina

LAG(col) devuelve col de la fila anterior de la ventana; LEAD(col), de la siguiente. Si no hay vecino, por defecto obtienes NULL.

Caso práctico: crecimiento de ingresos día a día

Una de las tres consultas que más se espera que un analista sepa escribir en una entrevista. Dado un flujo de ingresos diarios, devuelve la variación porcentual frente al día anterior.

Paso 1. Agrega los pedidos en un total diario:

WITH daily AS (
  SELECT
    DATE_TRUNC('day', created_at)::DATE AS day,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT * FROM daily ORDER BY day;

Paso 2. Añade una columna con los ingresos de ayer:

SELECT
  day,
  revenue,
  LAG(revenue) OVER (ORDER BY day) AS prev_revenue
FROM daily
ORDER BY day;

El prev_revenue de la primera fila es NULL — es lo esperado, el primer día no tiene vecino a su izquierda.

Paso 3. Calcula el porcentaje:

SELECT
  day,
  revenue,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY day))
          / NULLIF(LAG(revenue) OVER (ORDER BY day), 0),
    2
  ) AS dod_growth_pct
FROM daily
ORDER BY day;

La protección NULLIF(..., 0) te libra de una división por cero si no hubo pedidos el día anterior.

Suma acumulada

Un total acumulado es el caso más común en el que importa el marco. Con ORDER BY y sin marco explícito, una agregación ya se ejecuta de forma acumulativa:

SELECT
  day,
  revenue,
  SUM(revenue) OVER (ORDER BY day) AS cumulative_revenue
FROM daily
ORDER BY day;

Eso son «los ingresos desde el comienzo de la historia hasta hoy». ¿Quieres el acumulado del mes en curso? Añade PARTITION BY por mes:

SELECT
  day,
  revenue,
  SUM(revenue) OVER (
    PARTITION BY DATE_TRUNC('month', day)
    ORDER BY day
  ) AS month_to_date
FROM daily
ORDER BY day;

El primer día de cada mes el contador se reinicia y empieza a acumular de nuevo.

Media móvil de 7 días

Para que tu gráfico de ingresos deje de temblar los fines de semana — un truco de suavizado básico en los paneles:

SELECT
  day,
  revenue,
  AVG(revenue) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS revenue_7d_avg
FROM daily
ORDER BY day;

El marco ROWS BETWEEN 6 PRECEDING AND CURRENT ROW significa «la fila actual más las seis anteriores». Durante los seis primeros días la ventana no está completa — el promedio se calcula sobre las filas que existan hasta ese momento.

Por dónde seguir

Las funciones de ventana desbloquean toda una clase de consultas que sin ellas requieren subconsultas anidadas o son directamente imposibles: top-N por grupo, métricas día a día, sumas acumuladas, medias móviles, atributos de desfase para ML. Si escribes SQL para analítica, esta es tu herramienta de cada día.

La forma más rápida de interiorizarlo es resolver tareas específicas sobre el tema. Hay una categoría dedicada a las funciones de ventana en el entrenador, con explicaciones paso a paso.