LAG, LEAD und laufende Summen

LAG und LEAD sind zwei spezielle Fensterfunktionen, auf die Sie zugreifen können Andere Zeilen derselben Partition: die vorherige Zeile (LAG) oder die nächsten (LEAD) entsprechend dem ORDER BY des Fensters. Sie sind die Go-to-Tool für Zeilenvergleiche (Deltas, Variationen, Intervalle) und – kombiniert mit SUM/AVG/COUNT OVER – für laufende Summen.

LAG: die vorherige Zeile

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

Beispiel: für jede Bestellung „Tage seit der vorherigen Bestellung durch denselben“. Kunde":

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;

Die erste Bestellung jedes Kunden hat since_previous_order = NULL, weil Es gibt kein „Vorheriges“ in der Partition.

LEAD: die nächste Zeile

Der Spiegel von LAG. Typisch: „Zeit bis zum nächsten Ereignis“, „Differenz“. mit der nächsten Version".

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

Laufende Summen: Aggregate mit OVER

Aggregatfunktionen (SUM, AVG, COUNT, MAX, MIN) werden kumulativ bei Kombination mit OVER (… ORDER BY …):

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

Für jede Zeile berechnet SUM die Summe aller Zeilen in der Partition bis diesem Punkt**, gemäß dem ORDER BY des Fensters. Das Ergebnis ist das klassische „Laufwaage“.

Probieren Sie es aus

Wiederholungsübung