SQL od Zera, cz. 12: Świat funkcji okien na grubo – suma i średnia krocząca

Wstęp

Na wstępnie, przepraszam za dwudniowe opóźnienie, im dalej w las, tym lekcji się dłużej przygotowuje, a ten tydzień miałem wyjątkowo intensywny.

W Lekcji 11 nauczyliśmy się patrzeć przez „okno” i używać ROW_NUMBER(), żeby numerować wiersze w grupach (np. znaleźć najnowsze zamówienie).

W Lekcji 12 pójdziemy o krok dalej. Przestaniemy tylko numerować, a zaczniemy liczyć wewnątrz okna.

To jest moment, w którym nauczysz się robić rzeczy, które w Excelu zajmują dużo czasu (kopiowanie formuł, przesuwanie komórek), a w SQL zajmują jedną linijkę (no, prawie;) )

Rozwiązanie Zadania Domowego (z Lekcji 11)

Najnowsza książka autora

Poniżej siła CTE z ROW_NUMBER. Tworzymy query, które sortuje ksiaki według autora i daty powstania, tworzy dla autor_id okno (partition) i dodaje numeracje, następnie to CTE pobieramy i tylko te wiersze, gdzie rankieng_autora = 1. Gdybyśmy sortowali rosnąco ( ASC), 1 pokazywało by najstarszą książkę.

WITH ranked AS (
    SELECT 
        autor_id,
        tytul,
        rok_wydania,
        ROW_NUMBER() OVER(
            PARTITION BY autor_id
            ORDER BY rok_wydania DESC
        ) as ranking_autora
    FROM ksiazki
)
SELECT * FROM ranked WHERE ranking_autora = 1;

WITH ranked AS (
    SELECT 
        autor_id,
        tytul,
        rok_wydania,
        ROW_NUMBER() OVER(
            PARTITION BY autor_id
            ORDER BY rok_wydania DESC
        ) as ranking_autora
    FROM ksiazki
)
SELECT * FROM ranked WHERE ranking_autora = 1;

Najdroższa książka w kategorii

SELECT 
    kategoria_id,
    tytul,
    cena,
    ROW_NUMBER() OVER(
        PARTITION BY kategoria_id 
        ORDER BY cena DESC
    ) as ranking_ceny_w_kategorii
FROM ksiazki;

SELECT 
    kategoria_id,
    tytul,
    cena,
    ROW_NUMBER() OVER(
        PARTITION BY kategoria_id 
        ORDER BY cena DESC
    ) as ranking_ceny_w_kategorii
FROM ksiazki;

Powyżej tworzymy okno po id kategorii, i sortujemy znów malejąco, dzięki czemu najdroższe pozycje są na górze, i mają row_number 1

Zadanie dla chętnych

to wersja powyższego tożsama z tym, co pokazałem w zadaniu pierwszym.

Lekcja 12

Funkcje okna potrafią znacznie więcej niż tylko liczyć „raz, dwa, trzy”. Potrafią wykonywać agregacje (sumy, średnie, liczniki) wewnątrz tych okien, w sposób dynamiczny.

Co to znaczy „dynamiczny”? To znaczy, że wynik zmienia się z każdym kolejnym wierszem.

Najlepszym przykładem jest Suma Narastająca (Running Total).

Temat 1: Suma Narastająca – Ile zarobiliśmy „do dziś”?

Wyobraź sobie, że szef chce wykres sprzedaży. Nie interesuje go tylko, ile zarobiliśmy konkretnego dnia. Interesuje go, jak narastała nasza sprzedaż od początku roku.

• Dzień 1: Sprzedaż 100 zł. Suma narastająca: 100 zł.
• Dzień 2: Sprzedaż 50 zł. Suma narastająca: 150 zł (100 + 50).
• Dzień 3: Sprzedaż 200 zł. Suma narastająca: 350 zł (150 + 200).

Jak to zrobić w SQL bez skomplikowanych pętli? Używamy SUM(...) jako funkcji okna!

Kluczem jest tutaj ORDER BY wewnątrz OVER(...).

• Jeśli napiszesz SUM(kwota) OVER(), funkcja widzi „całe okno naraz” i wszędzie wstawi sumę całkowitą.
• Jeśli napiszesz SUM(kwota) OVER(ORDER BY data), funkcja „otwiera okno” stopniowo, wiersz po wierszu, zgodnie z datami.

Przykład: Suma narastająca wszystkich zamówień.

SELECT 
    zamowienie_id,
    data_zlozenia,
    wartosc_calkowita,
    --  TO JEST SUMA NARASTAJĄCA:
    SUM(wartosc_calkowita) OVER(
        ORDER BY data_zlozenia ASC
    ) as sprzedaz_narastajaco
FROM zamowienia;

SELECT 
    zamowienie_id,
    data_zlozenia,
    wartosc_calkowita,
    --  TO JEST SUMA NARASTAJĄCA:
    SUM(wartosc_calkowita) OVER(
        ORDER BY data_zlozenia ASC
    ) as sprzedaz_narastajaco
FROM zamowienia;

Analiza wyniku w DBeaver: Zobaczysz, że kolumna sprzedaz_narastajaco rośnie z każdym kolejnym zamówieniem. W ostatnim wierszu jej wartość będzie równa sumie całkowitej wszystkich zamówień.

A teraz z PARTITION BY!

Szef mówi: „Super, ale chcę widzieć, jak narasta sprzedaż dla każdego klienta osobno„.

Nic prostszego! Dodajemy PARTITION BY klient_id. Baza podzieli dane na „okna” klientów i w każdym oknie zacznie liczyć sumę od zera.

SELECT 
    klient_id,
    data_zlozenia,
    wartosc_calkowita,
    SUM(wartosc_calkowita) OVER( -- sumuj narastająco
        PARTITION BY klient_id      -- Dla każdego klienta osobno...
        ORDER BY data_zlozenia ASC  -- ...sumuj chronologicznie
    ) as wydatki_klienta_narastajaco
FROM zamowienia
ORDER BY klient_id, data_zlozenia;

SELECT 
    klient_id,
    data_zlozenia,
    wartosc_calkowita,
    SUM(wartosc_calkowita) OVER( -- sumuj narastająco
        PARTITION BY klient_id      -- Dla każdego klienta osobno...
        ORDER BY data_zlozenia ASC  -- ...sumuj chronologicznie
    ) as wydatki_klienta_narastajaco
FROM zamowienia
ORDER BY klient_id, data_zlozenia;

Temat 2: Ramy Okna (Window Frames) – ROWS BETWEEN...

To jest temat dla prawdziwych speców. Skup się 😉

Gdy używamy ORDER BY w funkcji okna (jak wyżej przy sumie narastającej), SQL domyślnie przyjmuje pewną definicję „ramy okna”. Ta domyślna definicja brzmi:

„Od początku grupy (lub początku tabeli) aż do bieżącego wiersza”.

W SQL zapisuje się to tak: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Dlatego suma narastała. Ale my możemy tę ramę zmienić! Możemy powiedzieć: „Chcę, żeby okno obejmowało tylko bieżący wiersz i 2 poprzednie„.

Składnia:

SQL

OVER (
    ORDER BY kolumna_sortowania
    ROWS BETWEEN X PRECEDING AND Y FOLLOWING -- o tutaj
)
-- X PRECEDING - X wierszy wstecz
-- Y FOLLOWING - Y wierszy w przód
-- CURRENT ROW - bieżący wiersz

OVER (
    ORDER BY kolumna_sortowania
    ROWS BETWEEN X PRECEDING AND Y FOLLOWING -- o tutaj
)
-- X PRECEDING - X wierszy wstecz
-- Y FOLLOWING - Y wierszy w przód
-- CURRENT ROW - bieżący wiersz

Temat 3: Średnia Krocząca (Moving Average)

Po co nam te ramy? Najlepszym przykładem jest Średnia Krocząca. Służy ona do wygładzania danych, żeby zobaczyć trend, a nie jednorazowe wyskoki.

Scenariusz: Chcemy zobaczyć 3-dniową średnią kroczącą wartości zamówień. Czyli dla każdego zamówienia chcemy policzyć średnią z jego wartości ORAZ wartości dwóch poprzednich zamówień.

Definicja ramy: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.

SELECT 
    zamowienie_id,
    data_zlozenia,
    wartosc_calkowita,    
    AVG(wartosc_calkowita) OVER( -- ŚREDNIA KROCZĄCA
        ORDER BY data_zlozenia ASC
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- Bieżący + 2 poprzednie 
    ) as srednia_kroczaca_3_zamowienia
FROM zamowienia;

SELECT 
    zamowienie_id,
    data_zlozenia,
    wartosc_calkowita,    
    AVG(wartosc_calkowita) OVER( -- ŚREDNIA KROCZĄCA
        ORDER BY data_zlozenia ASC
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- Bieżący + 2 poprzednie 
    ) as srednia_kroczaca_3_zamowienia
FROM zamowienia;

Jak to działa?

• Dla 1. zamówienia: Średnia z (Zamówienie 1).
• Dla 2. zamówienia: Średnia z (Zamówienie 1, 2).
• Dla 3. zamówienia: Średnia z (Zamówienie 1, 2, 3).
• Dla 4. zamówienia: Średnia z (Zamówienie 2, 3, 4). Okno się przesuwa!

To jest potężne narzędzie analityczne, używane w finansach, logistyce i sprzedaży.

Zadanie Domowe (Lekcja 12)

Czas na analitykę finansową!

1. Suma narastająca per kategoria: Wyświetl listę książek (tytuł, kategoria, cena). Dodaj kolumnę, która pokaże, jak narasta wartość książek (suma ich cen) wewnątrz każdej kategorii, sortując książki od najtańszej do najdroższej. (Wskazówka: SUM(cena) OVER(PARTITION BY ... ORDER BY ...)).
2. Średnia krocząca cen książek: Wyświetl wszystkie książki posortowane po roku wydania. Obok ceny każdej książki wyświetl średnią cenę tej książki oraz 4 poprzednich książek (chronologicznie wg roku wydania). (Wskazówka: AVG(cena) OVER(ORDER BY rok_wydania ROWS BETWEEN ...)).
3. Zadanie dla chętnych (Analiza VIP): Korzystając z tabeli zamowienia, znajdź klientów, których łączne wydatki narastająco przekroczyły 200 zł już przy ich drugim zamówieniu. (Wskazówka: Będziesz potrzebował CTE, w którym policzysz ROW_NUMBER oraz sumę narastającą, a potem w głównym zapytaniu przefiltrujesz wyniki).

Powodzenia! W następnej lekcji (ostatniej z funkcjami okna!) nauczymy się zaglądać w przyszłość i przeszłość za pomocą LAG i LEAD. a w lekcji 14 zrobimy taką powtórkę, plus encyklopedyczna listę różnych funkcji, być może w formie fiszki do druku również (jak zdążę).

Lekcja 15 i 16 będą prawdopodobnie, ostatnimi z cotygodniowych lekcji, dodamy funkcje programistyczne triggery, funkcje i procedury plus kursory.

Dalsza część nauki będzie powiązana już z lekcjami programowania, oraz będę co jakiś czas dodawał lekcji, czasem patrzące wstecz, czasem pokazujące jakieś skomplikowane konstrukty, ale to wszystko już będzie mniej cyklicznie.