LAG, LEAD i sumy bieżące

LAG i LEAD to dwie specjalne funkcje okna, do których możesz uzyskać dostęp inne wiersze tej samej partycji: poprzedni wiersz (LAG) lub następny (LEAD) zgodnie z ORDER BY okna. Oni są narzędzie umożliwiające porównania między wierszami (delty, różnice, interwały) oraz — w połączeniu z SUM/AVG/COUNT OVER — dla sumy bieżącej.

LAG: poprzedni wiersz

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

Przykład: dla każdego zamówienia „dni od poprzedniego zamówienia przez to samo klient":

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;

Pierwsze zamówienie każdego klienta ma since_previous_order = NULL, ponieważ w partycji nie ma „poprzedniej”.

LEAD: następny rząd

Lustro LAG. Typowe: „czas do następnego zdarzenia”, „różnica z następną wersją”.

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

Bieżące sumy: agreguje z OVER

Funkcje agregujące (SUM, AVG, COUNT, MAX, MIN) stają się skumulowane w połączeniu z OVER (… ORDER BY …):

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

Dla każdego wiersza SUM oblicza sumę ** wszystkich wierszy w partycji do ten punkt**, zgodnie z ORDER BY okna. Rezultatem jest klasyczny „bieżący balans”.

Spróbuj

Przejrzyj ćwiczenie