Rozwiązanie Zadania Domowego (z Lekcji 10)

Zadanie 1: Tworzenie Widoków (Views)

Cel: Stwórz kilka raportów używając wiedzy z poprzednich lekcji (średnie itp.) i zapisz je jako widoki.

Oto dwie propozycje raportów, które warto mieć „pod ręką”.

Raport A: Średnia cena książek w kategoriach (prosty)

Ten widok przyda się, żeby szybko sprawdzić, które kategorie są najdroższe.

SQL

CREATE VIEW raport_srednie_ceny AS
SELECT 
    kat.nazwa AS kategoria,
    COUNT(k.ksiazka_id) AS liczba_ksiazek,
    ROUND(AVG(k.cena), 2) AS srednia_cena
FROM ksiazki k
JOIN kategorie kat ON k.kategoria_id = kat.kategoria_id
GROUP BY kat.nazwa
ORDER BY srednia_cena DESC;

-- Jak użyć:
-- SELECT * FROM raport_srednie_ceny;

Zadanie 2: Ewolucja tabeli recenzje (ALTER + UPDATE)

Cel: Dodaną w zadaniu domowym z poprzedniej lekcji tabelę recenzje, uzbrój w klucz obcy do tabeli ksiazki, i potem za pomocą UPDATE, powiąż dane z recenzjami z konkretną książką.

To jest test bojowy z inżynierii danych w SQLite!

Krok 1: Dodanie kolumny z Kluczem Obcym

Używamy „triku”, który odkryliśmy w Lekcji 10. Dodajemy kolumnę, która domyślnie przyjmuje NULL, dzięki czemu SQLite pozwala nam od razu zdefiniować REFERENCES.

SQL

-- Pamiętaj o strażniku!
PRAGMA foreign_keys = ON;

-- Modyfikujemy tabelę recenzje (zakładam, że ma kolumny id, autor, ocena, tresc)
ALTER TABLE recenzje 
ADD COLUMN ksiazka_id INTEGER REFERENCES ksiazki(ksiazka_id);

Po wykonaniu tego, w tabeli recenzje pojawi się kolumna ksiazka_id wypełniona NULL-ami.

Krok 2: Powiązanie recenzji z książkami (UPDATE)

Teraz musimy ręcznie przypisać istniejące recenzje do konkretnych książek. Zakładam, że masz w tabeli recenzje jakieś rekordy (np. o ID 1, 2, 3) oraz w tabeli ksiazki książki o ID 1 (Wiedźmin), 3 (Solaris) itp.

SQL

-- Przypisujemy recenzję nr 2 do książki nr 3 (Solaris)
UPDATE recenzje SET ksiazka_id = 3 WHERE id = 2;

-- Przypisujemy recenzję nr 3 do książki nr 5 (Harry Potter)
UPDATE recenzje SET ksiazka_id = 5 WHERE id = 3;

Co też zaczynamy?

Czołgiem w Lekcji 11!

Dziś otwieramy drzwi do zupełnie nowego poziomu SQL. Wchodzimy w świat Funkcji Okna (Window Functions).

To jest moment, w którym przestajesz być „””osobą, która zna podstawy SQL”””, a stajesz się analitykiem, który potrafi rozwiązywać trudne problemy w elegancki sposób.

Problem, którego GROUP BY nie rozwiąże

Zacznijmy od bardzo życiowego problemu. Szef przychodzi i mówi:

„Chcę zobaczyć listę wszystkich naszych klientów. Obok każdego klienta chcę widzieć datę i kwotę jego NAJNOWSZEGO zamówienia„.

Myślisz: „Proste! Użyję GROUP BY i MAX(data_zlozenia)„.

SQL

-- Próba z GROUP BY
SELECT 
    klient_id, 
    MAX(data_zlozenia) as najnowsza_data,
    wartosc_calkowita -- <--- PROBLEM!
FROM zamowienia
GROUP BY klient_id;

Dlaczego to nie działa dobrze? GROUP BY „zgniata” wszystkie zamówienia klienta w jeden wiersz. Owszem, MAX() poprawnie znajdzie najnowszą datę. Ale skąd baza ma wiedzieć, którą wartosc_calkowita wyświetlić? Zazwyczaj weźmie losową wartość z grupy, a nie tę, która pasuje do najnowszej daty.

Można oczywiście porobić różne podzapytania, ale nie jest to ani eleganckie ani wydaje.

Potrzebujemy narzędzia, które pozwoli nam „zajrzeć” do grupy wierszy, poukładać je i wybrać ten jeden konkretny, nie niszcząc przy tym reszty danych.

Temat 1: Czym jest Funkcja Okna i OVER(...) – Numerowanie w grupach (PARTITION BY i ORDER BY)

Funkcje okna pozwalają wykonywać obliczenia na zbiorze wierszy powiązanych z bieżącym wierszem.

Kluczem jest magiczne słowo OVER(...). To ono definiuje „okno”, przez które patrzymy na dane.

Najprostszą i najbardziej użyteczną funkcją okna na początek jest ROW_NUMBER(). Robi ona dokładnie to, co mówi jej nazwa: nadaje numery wierszom (1, 2, 3…).

Przykład. Bo bez tego ciężko, mamy np. katalog pojazdów silnikowych. Mamy tam kategorie jak Samochody osobowe, ciągniki rolnicze, czołgi i motocykle.

Dla każdej z kategorii mamy wiele produktów. Mamy też daty dodania tych produktów.

Chcemy całość posortować po kategorii i dacie dodania produktu, a następnie chcemy mieć niezależne numery w każdej kategorii, czyli 1 motocykl, 2 motocykl, 1 czołg itd.

Piszemy więc SELECT, kolejne kolumny….. ,

ROW_NUMBER() OVER ( i taki jest początek funkcji okna, wybieramy funkcję row_number, i określamy, po czym ona ma działać, czyli kształt okna.

PARTITION BY kategoria — czyli okno jest określone przez kategorię produktu

ORDER BY data_dodania ASC — i sortujemy dane okno, czyli dany wycinek danych po dacie dodania rosnąco, ASC czyli nasjtarsze na górze (DESC odwrotnie)

Wróćmy do problemu: „Najnowsze zamówienie każdego klienta”.

Chcemy, aby baza danych:

1. Wzięła zamówienia pierwszego klienta.
2. Posortowała je od najnowszego do najstarszego.
3. Ponumerowała je: najnowsze dostaje nr 1, kolejne nr 2 itd.
4. Potem przeszła do drugiego klienta i zresetowała licznik (znów 1, 2, 3…).

I określamy to wewnątrz klauzuli OVER(...):

• PARTITION BY klient_id: To instrukcja „Podziel dane na niezależne grupy (okna) dla każdego klienta”. To działa jak GROUP BY, ale nie zgniata wierszy.
• ORDER BY data_zlozenia DESC: To instrukcja „Wewnątrz każdej grupy posortuj wiersze malejąco po dacie”.

Zobaczmy to w akcji:

SQL

SELECT 
    klient_id,
    data_zlozenia,
    wartosc_calkowita,
    -- TO JEST NASZA FUNKCJA OKNA:
    ROW_NUMBER() OVER(
        PARTITION BY klient_id      -- Dla każdego klienta osobno...
        ORDER BY data_zlozenia DESC -- ...ponumeruj od najnowszego
    ) as ranking_zamowien
FROM zamowienia;

Analiza wyniku: Spójrz na wyniki w DBeaverze. Zobaczysz wszystkie zamówienia. Obok każdego pojawiła się kolumna ranking_zamowien. Dla każdego klienta zamówienie z numerem 1 to jest właśnie to najnowsze!

Temat 3: Wybieramy tylko najnowsze (Połączenie z CTE)

Mamy już ranking. Wiemy, że interesują nas tylko wiersze, gdzie ranking_zamowien = 1.

Ale uwaga: nie możemy użyć wyniku funkcji okna w klauzuli WHERE tego samego zapytania (np. WHERE ranking_zamowien = 1 nie zadziała).

Musimy użyć triku, który poznaliśmy w Lekcji 8 – CTE (Common Table Expression).

1. W CTE przygotujemy sobie dane z rankingiem.
2. W głównym zapytaniu odfiltrujemy tylko te z numerem 1.

SQL

WITH ZamowieniaZRankingiem AS (
    -- Krok 1: Przygotowujemy ranking w CTE
    SELECT 
        klient_id,
        data_zlozenia,
        wartosc_calkowita,
        ROW_NUMBER() OVER(PARTITION BY klient_id ORDER BY data_zlozenia DESC) as ranking
    FROM zamowienia
)
-- Krok 2: Wybieramy tylko te, które są "numerem jeden"
SELECT 
    klient_id,
    data_zlozenia AS data_najnowszego,
    wartosc_calkowita AS kwota_najnowszego
FROM ZamowieniaZRankingiem
WHERE ranking = 1;

I mamy TO! Otrzymaliśmy listę unikalnych klientów wraz z pełnymi danymi ich ostatniego zamówienia. To jest bardzo częsty i potężny wzorzec projektowy w SQL.

Na dziś wystarczy. Nie było dużo, ale temat na początku wydaje się trudny. Poćwiczcie koniecznie i do zobaczenia za tydzień (albo w weekend na lekcji programowania 4a – tak, rozbijam lekcję na dwie części, bo będą przepełnione).

Zadanie Domowe (Lekcja 11)

Czas przećwiczyć ROW_NUMBER()!

1. Najnowsza książka autora: Napisz zapytanie, które dla każdego autora (autor_id) znajdzie jego najnowszą książkę (na podstawie rok_wydania). Wyświetl autor_id, tytul, rok_wydania oraz kolumnę z rankingiem (ROW_NUMBER). Najnowsza książka powinna mieć numer 1. (Wskazówka: PARTITION BY autor_id ORDER BY rok_wydania DESC).
2. Najdroższa książka w kategorii: Wyświetl listę wszystkich książek. Dodaj kolumnę rankingową, która ponumeruje książki od najdroższej do najtańszej wewnątrz każdej kategorii osobno.
3. Zadanie dla chętnych (CTE + Window Function): Korzystając z zapytania z Zadania 2 i wiedzy o CTE, wyświetl ostatecznie tylko tytuły i ceny najdroższych książek w każdej kategorii (czyli tylko te z rankingiem 1).