LAG, LEAD y totales acumulados

LAG y LEAD son dos funciones de ventana especiales que le dan acceso a otras filas de la misma partición: la fila anterior (LAG) o la el siguiente (LEAD) según el ORDER BY de la ventana. ellos son los herramienta de referencia para comparaciones fila a fila (deltas, variaciones, intervalos) y, combinado con SUM/AVG/COUNT OVER, para totales acumulados.

LAG: la fila anterior

LAG(colonna)            OVER (PARTITION BY p ORDER BY o)
LAG(colonna, n)         OVER (PARTITION BY p ORDER BY o)   -- n righe indietro
LAG(colonna, n, default) OVER (PARTITION BY p ORDER BY o)  -- default se non c'è

Ejemplo: para cada pedido, "días desde el pedido anterior por el mismo cliente":

SELECT id,
       customer_id,
       ordered_at,
       ordered_at - LAG(ordered_at) OVER (
         PARTITION BY customer_id ORDER BY ordered_at
       ) AS since_previous_order
FROM   orders
ORDER BY customer_id, ordered_at;

El primer pedido de cada cliente tiene since_previous_order = NULL porque no hay "anterior" en la partición.

PRINCIPAL: la siguiente fila

El espejo de LAG. Típico: "tiempo hasta el próximo evento", "diferencia con la próxima versión".

SELECT id, ordered_at,
       LEAD(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at)
         - ordered_at AS to_next
FROM   orders;

Totales acumulados: agregados con OVER

Las funciones agregadas (SUM, AVG, COUNT, MAX, MIN) se convierten acumulativo cuando se combina con OVER (… ORDER BY …):

SELECT order_id,
       total,
       SUM(total) OVER (
         PARTITION BY customer_id
         ORDER BY     ordered_at
       ) AS running_total
FROM   …;

Para cada fila, SUM calcula la suma de todas las filas de la partición hasta ese punto, según el ORDER BY de la ventana. El resultado es el clásico "equilibrio de carrera".

Pruébalo

Ejercicio de revisión