Funkcje okienne dla danych czasowych: LEAD(), LAG()

Teraz naszym zadaniem jest pójść jeszcze dalej i nauczyć się używać funkcji okiennych do analizy danych czasowych. Gotowy? Mam nadzieję, że masz przy sobie kubek kawy, bo będzie ciekawie.

No dobra, jak zawsze, najpierw odpowiadamy na główne pytanie: po co nam funkcje okienne (LEAD(), LAG())? Wyobraź sobie, że pracujesz z danymi czasowymi, czy to logi zdarzeń, godziny pracy, szeregi czasowe czy cokolwiek, gdzie ważna jest kolejność zdarzeń.

Na przykład chcesz:

• Dowiedzieć się, kiedy nastąpiło kolejne zdarzenie po bieżącym.
• Obliczyć różnicę czasu między bieżącym zdarzeniem a poprzednim.
• Posortować dane i policzyć różnicę między rekordami.

W tym miejscu na scenę wchodzą dwie świetne funkcje: LEAD() i LAG(). Pozwalają one wyciągać dane z poprzedniego lub następnego wiersza w określonym oknie. To tak, jakbyś miał magiczną książkę, w której możesz podejrzeć, co będzie na następnej stronie, nie przewracając obecnej.

LEAD() i LAG(): składnia i podstawowe zasady

Obie funkcje mają podobną składnię:

LEAD(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)
LAG(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)

• column_name — kolumna, z której chcemy pobrać dane.
• offset (opcjonalnie) — przesunięcie względem bieżącego wiersza. Domyślnie to 1.
• default_value (opcjonalnie) — wartość, która zostanie zwrócona, jeśli nie ma wiersza z wymaganym przesunięciem (np. gdy jesteś na ostatnim wierszu).
• OVER() — tutaj określasz "okno", w którym będzie wykonywane obliczenie. Najczęściej to ORDER BY, czasem używa się PARTITION BY do podziału danych na grupy.

Przykład: Prosty LEAD() i LAG()

Stwórzmy prostą tabelę events do naszych eksperymentów:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT NOT NULL,
    event_date TIMESTAMP NOT NULL
);

INSERT INTO events (event_name, event_date)
VALUES
    ('Event A', '2023-10-01 10:00:00'),
    ('Event B', '2023-10-01 11:00:00'),
    ('Event C', '2023-10-01 12:00:00'),
    ('Event D', '2023-10-01 13:00:00');

Teraz chcemy zobaczyć, kiedy wystąpiły poprzednie i następne zdarzenia względem każdego zdarzenia:

SELECT
    id,
    event_name,
    event_date,
    LAG(event_date) OVER (ORDER BY event_date) AS previous_event,
    LEAD(event_date) OVER (ORDER BY event_date) AS next_event
FROM events;

Wynik będzie wyglądał tak:

Tutaj LAG() pobiera dane z poprzedniego wiersza, a LEAD() — z następnego. Pierwsze zdarzenie nie ma na co się oglądać, a ostatnie nie ma kogo wyprzedzić, więc dostają NULL.

Przykład: różnica między zdarzeniami

Czasem musimy wiedzieć, ile czasu minęło między zdarzeniami. W tym celu możemy po prostu odjąć jedną datę od drugiej:

SELECT
    id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (ORDER BY event_date) AS time_since_last_event
FROM events;

Wynik:

Przykład: użycie PARTITION BY

Załóżmy, że mamy kilku użytkowników, z których każdy ma swoje zdarzenia. Chcemy znaleźć różnicę między zdarzeniami dla każdego użytkownika.

Aktualizujemy tabelę i dodajemy kolumnę user_id:

ALTER TABLE events ADD COLUMN user_id INT;

UPDATE events SET user_id = 1 WHERE id <= 2;
UPDATE events SET user_id = 2 WHERE id > 2;

Teraz mamy dwóch użytkowników. Użyjemy PARTITION BY, żeby liczyć w każdej grupie osobno:

SELECT
    user_id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS time_since_last_event
FROM events;

Wynik:

Przykłady użycia w prawdziwych zadaniach

1. Logi zdarzeń: analiza czasu między zdarzeniami, takimi jak logowanie użytkownika i wylogowanie.
2. Time-tracking: liczenie czasu spędzonego na konkretnych zadaniach.
3. Analiza zachowań: analiza sekwencji działań klientów w sklepie internetowym.
4. Obliczanie metryk skumulowanych: użycie funkcji okiennych do pracy z szeregami czasowymi.

Typowe błędy

Przy pracy z LEAD() i LAG() najczęstsze problemy to:

• Zapomniany ORDER BY w OVER(). Bez tego funkcja nie wie, w jakiej kolejności są wiersze.
• Problemy z typami danych czasowych (TIMESTAMP vs DATE).
• Ignorowanie wartości NULL, które mogą pojawić się na początku i końcu zakresu okna.

Żeby uniknąć tych błędów, zawsze sprawdzaj swoje dane i upewnij się, że dobrze określiłeś okno dla operacji.