LAG, LEAD en lopende totalen

LAG en LEAD zijn twee speciale vensterfuncties die u toegang geven tot andere rijen van dezelfde partitie: de vorige rij (LAG) of de volgende (LEAD) volgens ORDER BY van het venster. Zij zijn de go-to-tool voor rij-tot-rij-vergelijkingen (delta's, variaties, intervallen) en — gecombineerd met SUM/AVG/COUNT OVER — voor lopende totalen.

LAG: de vorige rij

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

Voorbeeld: voor elke bestelling, "dagen sinds de vorige bestelling hetzelfde klant":

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;

De eerste bestelling van elke klant heeft since_previous_order = NULL omdat er is geen "vorige" in de partitie.

LEID: de volgende rij

De spiegel van LAG. Typisch: "tijd tot de volgende gebeurtenis", "verschil met de volgende versie".

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

Lopende totalen: aggregeert met OVER

Geaggregeerde functies (SUM, AVG, COUNT, MAX, MIN) worden cumulatief in combinatie met OVER (… ORDER BY …):

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

Voor elke rij berekent SUM de som van alle rijen in de partitie tot dat punt, volgens de ORDER BY van het venster. Het resultaat is de klassieke "loopbalans".

Probeer het

Oefening beoordelen