Rozwiązanie Zadania Domowego (z Lekcji 12)

Sprawdźmy Twoje sumy narastające i średnie kroczące. To fundamenty zaawansowanej analityki.

1. Suma narastająca cen książek w kategoriach Zadanie: Pokaż, jak narasta wartość (cena) książek wewnątrz każdej kategorii, od najtańszej.

SELECT 
    k.tytul,
    kat.nazwa AS kategoria,
    k.cena,    
    SUM(k.cena) OVER(
        PARTITION BY k.kategoria_id 
        ORDER BY k.cena ASC
    ) as wartosc_narastajaco_w_kat
FROM ksiazki k
JOIN kategorie kat ON k.kategoria_id = kat.kategoria_id;

SELECT 
    k.tytul,
    kat.nazwa AS kategoria,
    k.cena,    
    SUM(k.cena) OVER(
        PARTITION BY k.kategoria_id 
        ORDER BY k.cena ASC
    ) as wartosc_narastajaco_w_kat
FROM ksiazki k
JOIN kategorie kat ON k.kategoria_id = kat.kategoria_id;

Wynik: W każdej kategorii suma „resetuje się” i rośnie wraz z ceną kolejnych książek.

2. Średnia krocząca (ruchoma) cen książek Zadanie: Obok ceny książki wyświetl średnią cenę tej książki oraz 4 poprzednich (chronologicznie).

SELECT 
    tytul, rok_wydania, cena,
    AVG(cena) OVER(
        ORDER BY rok_wydania ASC
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) as srednia_ruchoma_5_ksiazek
FROM ksiazki;

SELECT 
    tytul, rok_wydania, cena,
    AVG(cena) OVER(
        ORDER BY rok_wydania ASC
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) as srednia_ruchoma_5_ksiazek
FROM ksiazki;

3. Zadanie dla chętnych (Analiza VIP z CTE) Zadanie: Klienci, których wydatki przekroczyły 200 zł już przy drugim zamówieniu.

WITH AnalizaWydatkow AS (
    SELECT 
        klient_id, zamowienie_id, wartosc_calkowita,
        -- Numerujemy zamówienia klienta:
        ROW_NUMBER() OVER(PARTITION BY klient_id ORDER BY data_zlozenia) as nr_zamowienia,
        -- Liczymy sumę narastającą wydatków klienta:
        SUM(wartosc_calkowita) OVER(PARTITION BY klient_id ORDER BY data_zlozenia) as suma_wydatkow
    FROM zamowienia
)
-- Główne zapytanie filtrujące CTE:
SELECT klient_id, suma_wydatkow
FROM AnalizaWydatkow
WHERE nr_zamowienia = 2 AND suma_wydatkow > 200;

WITH AnalizaWydatkow AS (
    SELECT 
        klient_id, zamowienie_id, wartosc_calkowita,
        -- Numerujemy zamówienia klienta:
        ROW_NUMBER() OVER(PARTITION BY klient_id ORDER BY data_zlozenia) as nr_zamowienia,
        -- Liczymy sumę narastającą wydatków klienta:
        SUM(wartosc_calkowita) OVER(PARTITION BY klient_id ORDER BY data_zlozenia) as suma_wydatkow
    FROM zamowienia
)
-- Główne zapytanie filtrujące CTE:
SELECT klient_id, suma_wydatkow
FROM AnalizaWydatkow
WHERE nr_zamowienia = 2 AND suma_wydatkow > 200;

To jest już naprawdę zaawansowany SQL. Jeśli to zrobiłeś, brawo!

LAG i LEAD

Witaj w Lekcji 13! Dziś poznasz funkcje, które są absolutnym „must-have” każdego analityka danych.

Problem biznesowy: „Czy rośniemy?”

Wyobraź sobie, że szef patrzy na listę zamówień klienta i pyta:

„Czy ten klient z każdym kolejnym zamówieniem wydaje więcej, czy mniej pieniędzy? Pokaż mi różnicę w kwocie w porównaniu do poprzedniego zamówienia.”

Jak to zrobić? Masz wiersz z zamówieniem nr 2. Żeby odpowiedzieć na pytanie, musisz znać wartość zamówienia nr 1. Ale ona jest w innym wierszu!

Przed erą funkcji okna musielibyśmy robić skomplikowane łączenie tabeli samej ze sobą (SELF JOIN), co jest trudne i powolne. Teraz mamy LAG.

LAG() – Spojrzenie w lusterko wsteczne

Funkcja LAG(kolumna) robi jedną prostą rzecz: sięga do poprzedniego wiersza (w ramach zdefiniowanego okna) i zwraca wartość wskazanej kolumny.

LAG (z ang. opóźnienie) wymaga, aby okno było uporządkowane (ORDER BY). Musimy wiedzieć, co jest „wcześniej”, a co „później”.

Przykład podstawowy: Pokaż mi datę i kwotę zamówienia, a obok… kwotę poprzedniego zamówienia tego samego klienta.

SQL

SELECT 
    klient_id,
    data_zlozenia,
    wartosc_calkowita AS aktualna_kwota,
    -- SIĘGAMY WSTECZ:
    LAG(wartosc_calkowita) OVER(
        PARTITION BY klient_id      -- Dla każdego klienta osobno...
        ORDER BY data_zlozenia ASC  -- ...patrząc chronologicznie
    ) as poprzednia_kwota
FROM zamowienia;

SELECT 
    klient_id,
    data_zlozenia,
    wartosc_calkowita AS aktualna_kwota,
    -- SIĘGAMY WSTECZ:
    LAG(wartosc_calkowita) OVER(
        PARTITION BY klient_id      -- Dla każdego klienta osobno...
        ORDER BY data_zlozenia ASC  -- ...patrząc chronologicznie
    ) as poprzednia_kwota
FROM zamowienia;

Analiza wyniku w DBeaverze:

• Spójrz na pierwsze zamówienie dowolnego klienta. W kolumnie poprzednia_kwota zobaczysz [NULL]. Dlaczego? Bo nie ma „poprzedniego” zamówienia dla pierwszego wiersza!
• Spójrz na drugie zamówienie. W poprzednia_kwota zobaczysz wartość z pierwszego zamówienia. Magia!

Czyli dzięki tej funkcji masz dostęp wprost do danych z poprzedniego rekordu w danym oknie – tutaj okno jest ograniczone przez klient_id.

Analityczne złoto – Obliczanie różnic (Delta)

Samo wyświetlenie poprzedniej kwoty to dopiero początek. Prawdziwa moc LAG polega na tym, że możemy użyć tej wartości w obliczeniach matematycznych od razu w SELECT.

Odpowiedź dla szefa: „Pokaż mi różnicę w kwocie w porównaniu do poprzedniego zamówienia.”

SELECT 
    klient_id,
    data_zlozenia,
    wartosc_calkowita,
    -- OBLICZENIE W LOCIE: Aktualna - Poprzednia
    wartosc_calkowita - LAG(wartosc_calkowita) OVER(
        PARTITION BY klient_id ORDER BY data_zlozenia ASC
    ) as zmiana_kwoty_vs_poprzednie
FROM zamowienia;

SELECT 
    klient_id,
    data_zlozenia,
    wartosc_calkowita,
    -- OBLICZENIE W LOCIE: Aktualna - Poprzednia
    wartosc_calkowita - LAG(wartosc_calkowita) OVER(
        PARTITION BY klient_id ORDER BY data_zlozenia ASC
    ) as zmiana_kwoty_vs_poprzednie
FROM zamowienia;

Wynik: Widzisz kolumnę, gdzie wartości dodatnie oznaczają wzrost wydatków, a ujemne – spadek w porównaniu do poprzedniej wizyty w sklepie. To jest esencja analityki biznesowej.

LEAD() – Spojrzenie w przyszłość

Funkcja LEAD(kolumna) działa identycznie jak LAG, tylko w drugą stronę. Sięga do następnego wiersza.

Przykład: Dla każdej książki danego autora, pokaż mi, jaka była jego kolejna książka (po dacie wydania).

SELECT 
    autor_id,
    tytul AS aktualna_ksiazka,
    rok_wydania,
    -- SIĘGAMY W PRZYSZŁOŚĆ:
    LEAD(tytul) OVER(
        PARTITION BY autor_id       -- W ramach autora
        ORDER BY rok_wydania ASC    -- Chronologicznie
    ) as nastepna_ksiazka_autora
FROM ksiazki;

SELECT 
    autor_id,
    tytul AS aktualna_ksiazka,
    rok_wydania,
    -- SIĘGAMY W PRZYSZŁOŚĆ:
    LEAD(tytul) OVER(
        PARTITION BY autor_id       -- W ramach autora
        ORDER BY rok_wydania ASC    -- Chronologicznie
    ) as nastepna_ksiazka_autora
FROM ksiazki;

Wynik: Ostatnia książka autora będzie miała NULL w kolumnie nastepna_ksiazka_autora.

Zaawansowane opcje (Offset i Default)

Funkcje LAG i LEAD są bardziej elastyczne. Przyjmują opcjonalne argumenty:

LAG(kolumna, ile_wierszy_wstecz, wartosc_domyslna)

1. ile_wierszy_wstecz (Offset): Domyślnie 1. Możesz wpisać 2, żeby sięgnąć dwa wiersze wstecz.
2. wartosc_domyslna (Default): Co wstawić zamiast NULL, gdy nie ma poprzedniego wiersza.

Przykład: Porównaj kwotę zamówienia z kwotą sprzed DWÓCH zamówień. Jeśli nie ma takiego zamówienia, wyświetl 0 zamiast NULL.

SELECT 
    klient_id,
    wartosc_calkowita,
    LAG(wartosc_calkowita, 2, 0) OVER( -- <-- 2 wiersze wstecz, 0 jeśli NULL
        PARTITION BY klient_id ORDER BY data_zlozenia
    ) as kwota_dwa_zamowienia_temu
FROM zamowienia;

SELECT 
    klient_id,
    wartosc_calkowita,
    LAG(wartosc_calkowita, 2, 0) OVER( -- <-- 2 wiersze wstecz, 0 jeśli NULL
        PARTITION BY klient_id ORDER BY data_zlozenia
    ) as kwota_dwa_zamowienia_temu
FROM zamowienia;

Zadanie Domowe (Lekcja 13)

Czas na analizę trendów!

1. Analiza tempa pisania autorów (LAG): Wyświetl listę książek (tytuł, autor_id, rok_wydania). Dodaj kolumnę, która pokaże, ile lat minęło od wydania poprzedniej książki tego samego autora. (Wskazówka: Odejmij rok wydania poprzedniej książki od roku wydania bieżącej).
2. Co czytać dalej? (LEAD): Korzystając z tabeli pozycje_zamowienia, wyświetl listę książek (ksiazka_id) w ramach każdego zamówienia (zamowienie_id), posortowaną np. po ID książki. Obok ID książki wyświetl ID następnej książki na liście w tym samym zamówieniu. (Wskazówka: PARTITION BY zamowienie_id).
3. Zadanie dla chętnych (Wykrywanie spadków): Znajdź wszystkie zamówienia, których wartość była niższa niż wartość bezpośrednio poprzedzającego je zamówienia tego samego klienta. Wyświetl ID klienta, datę tego „słabszego” zamówienia oraz o ile kwota była niższa (różnica). (Wskazówka: Użyj CTE, w którym obliczysz różnicę za pomocą LAG, a w głównym zapytaniu przefiltrujesz wyniki, gdzie różnica jest ujemna).

Powodzenia! Po tej lekcji będziesz w stanie odpowiedzieć na większość pytań typu „czy jest lepiej niż było?”. W następnej, ostatniej lekcji z tego działu, podsumujemy funkcje okna.