¿Cuánto ha cambiado el importe respecto al pedido anterior? ¿Cuál es la siguiente compra de este cliente? Para responder hace falta mirar otra fila del resultado, y eso es lo que hacen LAG (la fila anterior) y LEAD (la siguiente). Son funciones de ventana, así que siempre van con OVER y un ORDER BY que defina qué es “anterior” y “siguiente”.

Sintaxis
LAG(columna [, desplazamiento [, valorPorDefecto]]) OVER ([PARTITION BY ...] ORDER BY ...)
LEAD(columna [, desplazamiento [, valorPorDefecto]]) OVER ([PARTITION BY ...] ORDER BY ...)
  • desplazamiento: cuántas filas atrás o adelante mirar. Por defecto, 1.
  • valorPorDefecto: lo que se devuelve cuando no existe esa fila (la primera, para LAG; la última, para LEAD). Por defecto, NULL.

El pedido anterior y el siguiente

Ordenamos los pedidos por fecha y mostramos, al lado de cada uno, el producto pedido justo antes y justo después:

PostgreSQL en tu navegador · Ctrl+Enter para ejecutar · los cambios se deshacen al terminar
fechaPedido producto anterior siguiente
2022-01-21 Xbox series X NULL MacBook Pro M1
2022-06-20 MacBook Pro M1 Xbox series X Xiami Mi 11
2022-07-09 Xiami Mi 11 MacBook Pro M1 Play Station 5
2022-07-18 Play Station 5 Xiami Mi 11 Echo DOT 3
2022-09-01 Echo DOT 3 Play Station 5 Echo DOT 4
2022-09-01 Echo DOT 4 Echo DOT 3 Nintendo Switch
2022-09-05 Nintendo Switch Echo DOT 4 Horno Balay
2022-09-10 Horno Balay Nintendo Switch NULL

El primer pedido no tiene anterior y el último no tiene siguiente, por eso aparecen los NULL.

Diferencia con la fila anterior

El uso más frecuente: restar el valor actual menos el anterior para ver cuánto ha subido o bajado. Aquí con el importe de cada pedido, y usando 0 como valor por defecto para que la primera fila no salga NULL:

PostgreSQL en tu navegador · Ctrl+Enter para ejecutar · los cambios se deshacen al terminar
fechaPedido producto totalPrecio importeAnterior variacion
2022-01-21 Xbox series X 999,98 0 999,98
2022-06-20 MacBook Pro M1 2449,50 999,98 1449,52
2022-07-09 Xiami Mi 11 4304,25 2449,50 1854,75
2022-07-18 Play Station 5 2199,80 4304,25 -2104,45
2022-09-01 Echo DOT 3 1999,50 2199,80 -200,30
2022-09-01 Echo DOT 4 2999,50 1999,50 1000,00
2022-09-05 Nintendo Switch 899,97 2999,50 -2099,53
2022-09-10 Horno Balay 1399,10 899,97 499,13

Con ventas por mes, la misma consulta te daría el crecimiento mes a mes; con cotizaciones, la variación diaria.

Por grupos con PARTITION BY

Con PARTITION BY, LAG y LEAD no salen del grupo. La primera compra de cada cliente no tiene anterior aunque haya pedidos de otros clientes antes:

PostgreSQL en tu navegador · Ctrl+Enter para ejecutar · los cambios se deshacen al terminar
nombre fechaPedido producto compraAnterior
Alejandro 2022-09-05 Nintendo Switch NULL
Ana 2022-07-09 Xiami Mi 11 NULL
Fernando 2022-06-20 MacBook Pro M1 NULL
Luis 2022-09-01 Echo DOT 3 NULL
Luis 2022-09-01 Echo DOT 4 2022-09-01
María 2022-01-21 Xbox series X NULL
María 2022-07-18 Play Station 5 2022-01-21

Solo María y Luis tienen más de un pedido, así que son los únicos con fecha de compra anterior. Luis pidió los dos Echo DOT el mismo día.

Para saber cuántos días pasan entre una compra y la anterior, en SQL Server se usa DATEDIFF(day, compraAnterior, fechaPedido). Las funciones de fechas cambian mucho de una base de datos a otra; en PostgreSQL, por ejemplo, basta con restar las dos fechas.

FIRST_VALUE y LAST_VALUE

Mientras que LAG y LEAD miran a una distancia fija, FIRST_VALUE devuelve el primer valor de la ventana. Por ejemplo, el primer producto que compró cada cliente, repetido en todas sus filas:

PostgreSQL en tu navegador · Ctrl+Enter para ejecutar · los cambios se deshacen al terminar
nombre fechaPedido producto primeraCompra
Alejandro 2022-09-05 Nintendo Switch Nintendo Switch
Ana 2022-07-09 Xiami Mi 11 Xiami Mi 11
Fernando 2022-06-20 MacBook Pro M1 MacBook Pro M1
Luis 2022-09-01 Echo DOT 3 Echo DOT 3
Luis 2022-09-01 Echo DOT 4 Echo DOT 3
María 2022-01-21 Xbox series X Xbox series X
María 2022-07-18 Play Station 5 Xbox series X
LAST_VALUE engaña: con un ORDER BY en el OVER, la ventana por defecto va desde el principio hasta la fila actual, así que el “último” valor es siempre el de la propia fila. Para obtener el último de verdad hay que ampliar el marco: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Lo explicamos en totales acumulados.

Practica

  1. Muestra, para cada pedido, el producto que se pidió dos pedidos antes (LAG con desplazamiento 2).
  2. Calcula la variación de importe de cada pedido respecto al siguiente.
  3. Para cada cliente, muestra su última compra en todas sus filas usando FIRST_VALUE con el orden invertido.