LAG, LEAD e running totals

LAG e LEAD sono due window function speciali che ti danno accesso a altre righe della stessa partizione: la riga precedente (LAG) o successiva (LEAD) secondo l'ORDER BY della finestra. Sono lo strumento di elezione per confronti riga-su-riga (delta, variazioni, intervalli) e — combinati con SUM/AVG/COUNT OVER — per i running totals.

LAG: la riga precedente

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'è

Esempio: per ogni ordine, "giorni dal precedente ordine dello stesso 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;

Il primo ordine di ogni cliente ha since_previous_order = NULL perché non esiste un "precedente" nella partizione.

LEAD: la riga successiva

Specchio di LAG. Tipico: "tempo fino al prossimo evento", "differenza con la versione successiva".

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

Running totals: aggregati con OVER

Le funzioni aggregate (SUM, AVG, COUNT, MAX, MIN) diventano cumulative quando combinate con OVER (… ORDER BY …):

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

Per ogni riga la SUM calcola la somma di tutte le righe della partizione fino a quel punto, secondo l'ORDER BY della finestra. Il risultato è il classico "saldo progressivo".

Prova tu

Esercizio di ripasso