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

Las funciones de ventana son una de esas cosas que, cuando las descubres, te preguntas cómo habías vivido sin ellas. Permiten hacer cálculos sobre un conjunto de filas relacionadas con la fila actual, sin reducir el resultado a una sola fila como haría un GROUP BY. El resultado: puedes tener en la misma consulta el valor de cada fila y un agregado calculado sobre un subconjunto de filas, todo a la vez.

La clave está en OVER()

Todas las funciones de ventana usan la cláusula OVER(). Sin ella, son funciones de agregado normales. Con ella, se convierten en funciones de ventana. Dentro de OVER() puedes usar PARTITION BY para dividir las filas en grupos (como un GROUP BY que no colapsa filas) y ORDER BY para definir el orden dentro de cada partición.

SELECT
  empleado,
  departamento,
  salario,
  AVG(salario) OVER (PARTITION BY departamento) AS media_dept
FROM empleados;

Esta consulta devuelve cada fila con su salario individual y, además, la media del salario del departamento al que pertenece. Nada de subqueries ni de joins consigo mismo.

ROW_NUMBER, RANK y DENSE_RANK

Estas tres funciones numeran filas pero se comportan de forma diferente cuando hay empates.

• ROW_NUMBER(): asigna un número único a cada fila, sin importar si hay empates. Si dos filas tienen el mismo salario, una tendrá el 1 y la otra el 2, de forma arbitraria dentro del orden definido.
• RANK(): si dos filas empatan, les asigna el mismo rango, pero deja un hueco después. Si dos filas tienen rango 3, la siguiente tendrá rango 5.
• DENSE_RANK(): igual que RANK pero sin huecos. Si dos filas empatan en rango 3, la siguiente tiene rango 4.

SELECT
  empleado,
  salario,
  ROW_NUMBER() OVER (ORDER BY salario DESC) AS row_num,
  RANK()       OVER (ORDER BY salario DESC) AS rango,
  DENSE_RANK() OVER (ORDER BY salario DESC) AS dense_rango
FROM empleados;

Un caso de uso habitual de ROW_NUMBER es sacar el registro más reciente por grupo, algo que con GROUP BY solo sería mucho más engorroso:

SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY usuario_id ORDER BY fecha DESC) AS rn
  FROM pedidos
) t
WHERE rn = 1;

LAG y LEAD: mirar hacia atrás y hacia delante

LAG() devuelve el valor de la fila anterior según el orden definido. LEAD() hace lo contrario: devuelve el valor de la fila siguiente. Son perfectas para calcular diferencias entre periodos, detectar cambios de estado o comparar con el valor anterior.

SELECT
  fecha,
  ventas,
  LAG(ventas)  OVER (ORDER BY fecha) AS ventas_dia_anterior,
  LEAD(ventas) OVER (ORDER BY fecha) AS ventas_dia_siguiente,
  ventas - LAG(ventas) OVER (ORDER BY fecha) AS variacion
FROM ventas_diarias
ORDER BY fecha;

Ambas aceptan un segundo parámetro para indicar cuántas filas saltar (por defecto 1) y un tercero para el valor por defecto cuando no existe fila anterior o siguiente.

Otras funciones útiles

Además de las anteriores, hay otras que conviene tener presentes:

• FIRST_VALUE / LAST_VALUE: devuelven el primer o último valor de la partición según el orden definido. Con LAST_VALUE hay que tener cuidado con el frame por defecto de la ventana.
• NTILE(n): divide las filas en n grupos de tamaño similar y devuelve el número de grupo. Útil para cuartiles, deciles, percentiles.
• SUM, AVG, COUNT como funciones de ventana: añadir OVER() a cualquier agregado estándar lo convierte en función de ventana. Puedes calcular totales acumulados con una ventana ordenada.

-- Total acumulado de ventas por fecha
SELECT
  fecha,
  ventas,
  SUM(ventas) OVER (ORDER BY fecha ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_acumulado
FROM ventas_diarias;

Rendimiento y consideraciones prácticas

Las funciones de ventana se ejecutan después del WHERE y del GROUP BY, así que no puedes filtrar por el resultado de una función de ventana en el mismo nivel de la query. Tienes que envolverla en una subquery o una CTE, como en el ejemplo de ROW_NUMBER de arriba.

En cuanto al rendimiento, PostgreSQL procesa las funciones de ventana con un nodo WindowAgg en el plan de ejecución. Si defines varias funciones de ventana con la misma cláusula OVER, el optimizador las agrupa en un solo paso. Si las cláusulas OVER son diferentes, se hacen varios pasados sobre los datos, así que conviene no multiplicarlas sin necesidad.

Si trabajas con conjuntos de datos grandes y necesitas ir más allá de SQL puro, las funciones de ventana son también el puente natural hacia el análisis con pandas y Polars en Python, donde encontrarás operaciones equivalentes sobre DataFrames. Y si en tu proyecto usas Elixir, Ecto también soporta queries con window functions a través de su DSL.

Imagen: Pexels / Markus Winkler