Ventanas avanzadas: LAG, LEAD, NTILE y marcos — SQL avanzado: ventanas, vistas y CASE

Mirar a los lados: LAG y LEAD

A veces una fila necesita el valor de otra fila relacionada: la del día anterior para calcular el crecimiento, la siguiente para comparar, etc. Eso es justo lo que hacen LAG y LEAD sobre una ventana ordenada:

LAG(col) → el valor de col en la fila anterior (según el ORDER BY).
LEAD(col) → el valor de col en la fila siguiente.

SELECT id, importe,
  LAG(importe)  OVER (ORDER BY id) AS importe_anterior,
  LEAD(importe) OVER (ORDER BY id) AS importe_siguiente
FROM ventas;

En la primera fila no hay anterior y en la última no hay siguiente: ahí el resultado es NULL. Puedes pasar un segundo y tercer argumento para saltar más filas o dar un valor por defecto: LAG(importe, 1, 0) devuelve 0 en lugar de NULL en la primera fila.

Una resta clásica: importe - LAG(importe) OVER (ORDER BY id) te da la variación respecto a la fila previa.

Repartir en grupos: NTILE(n)

NTILE(n) reparte las filas (en el orden de la ventana) en n grupos lo más iguales posible y devuelve a qué grupo (1..n) pertenece cada fila. Es ideal para cuartiles (n = 4), deciles, percentiles aproximados, etc.

SELECT nombre, precio,
  NTILE(3) OVER (ORDER BY precio) AS tercio
FROM productos;

Si las filas no se dividen de forma exacta, los primeros grupos reciben una fila de más.

Marcos de ventana: ROWS BETWEEN

Por defecto, un agregado con ORDER BY dentro del OVER acumula desde la primera fila hasta la actual. Pero puedes definir el marco (la franja de filas que entra en el cálculo) con ROWS BETWEEN ... AND ...:

SELECT id, importe,
  AVG(importe) OVER (
    ORDER BY id
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS media_movil
FROM ventas;

N PRECEDING → N filas antes de la actual.
CURRENT ROW → la fila actual.
N FOLLOWING → N filas después.
UNBOUNDED PRECEDING / UNBOUNDED FOLLOWING → hasta el principio / el final.

Así, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW calcula sobre la fila actual y las dos anteriores: la base de las medias y sumas móviles.

El acumulado clásico (SUM(...) OVER (ORDER BY id)) es en realidad el marco ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW escrito de forma implícita.

Ejemplos

Comparar cada venta con la anterior (LAG)

SELECT id, importe,
  LAG(importe) OVER (ORDER BY id) AS anterior,
  importe - LAG(importe) OVER (ORDER BY id) AS variacion
FROM ventas
ORDER BY id;

Media móvil de 2 filas con un marco de ventana

SELECT id, importe,
  AVG(importe) OVER (
    ORDER BY id
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS media_movil
FROM ventas
ORDER BY id;