WSTĘP

Już w piątek startujemy z kursem programowania, ten i nowy kurs będą się zazębiać z czasem. Podstawy prawie wszystkie już znasz, pobieranie danych, filtrowanie, łączenie, dziś rozszerzymy wiedzę o podzapytaniach, o czymś co w programowaniu ma nazwę instrukcji warunkowej, oraz stworzymy dynamiczne widoki CTE. W kolejnych lekcjach pójdziemy w głąb analityki, by finalnie dojść do tworzenia bazy i tabel od zera. Na razie uczę cię pracy z tym co już zapewne w pracy msz. Masz jakieś systemy CRM czy ERP i dzięki SQL możesz pobierać wprost dane. Kolejny krok to projektowanie własnych aplikacji.

Ale to w przyszłości. Na razie rozwiązanie prac domowych z części 7.

Rozwiązanie Zadania Domowego (z Lekcji 7)

Zadanie 1: (Grupowanie po wielu kolumnach): Policz, ile książek (COUNT) każdy autor (autor_id) napisał dla każdego wydawnictwa (wydawnictwo_id).

Rozwiązanie: Kluczem było tu użycie GROUP BY na dwóch kolumnach jednocześnie. W wersji „na wypasie” dodajemy JOIN, aby zobaczyć nazwy.

SELECT 
    a.imie || ' ' || a.nazwisko AS autor, 
    w.nazwa AS wydawnictwo, 
    COUNT(*) AS liczba_ksiazek
FROM ksiazki k
JOIN autorzy a ON k.autor_id = a.autor_id
JOIN wydawnictwa w ON k.wydawnictwo_id = w.wydawnictwo_id
GROUP BY autor, wydawnictwo  
ORDER BY autor, liczba_ksiazek DESC;

Zadanie 2: (Podzapytanie w WHERE): Zadanie: Znajdź wszystkich klientów (klienci), którzy mieszkają w tym samym mieście co 'Anna Nowak’, ale nie są Anną Nowak.

Rozwiązanie: To było klasyczne zadanie na podzapytanie (subquery), które wprowadziliśmy w Lekcji 7.

SELECT 
    imie, 
    nazwisko, 
    miasto
FROM klienci
WHERE miasto = (    
    SELECT miasto 
    FROM klienci 
    WHERE imie = 'Anna' AND nazwisko = 'Nowak'
)
AND (imie != 'Anna' OR nazwisko != 'Nowak'); 

Baza najpierw znajduje miasto Anny Nowak (Kraków), a następnie „podstawia” je do zapytania głównego, szukając innych osób z tego miasta.

Zadanie 3: (JOIN + HAVING): Zadanie: Pokaż nazwy kategorii (kategorie.nazwa) oraz średnią cenę książek w tych kategoriach, ale wyświetl tylko te kategorie, w których średnia cena (AVG(k.cena)) jest niższa niż 45 zł.

Rozwiązanie: Tutaj łączyliśmy wszystko: JOIN (by dostać nazwy), AVG (by policzyć średnią), GROUP BY (by zrobić to dla każdej kategorii) i HAVING (by przefiltrować wyniki po agregacji).

SELECT 
    kat.nazwa AS kategoria, 
    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
HAVING AVG(k.cena) < 45;

Instrukcja warunkowa

Jak już wspomniałem, rusza mój kurs programowania, lubię się uczyć i lubię uczyć innych. Dziś pierwszy raz, gdy nasz SQL zahaczy o klasyczne programowanie (które w samym SQL też występuje!

Co to jest logika warunkowa? Bardzo prosty przykład.

Masz plan!

Jeśli będzie padać to idziesz do kina, ale jeśli wyjdzie słońce i będzie ciepło ot idziesz nad jezior.

W programowaniu jest kilka instrukcji, które służą do podejmowania przez program decyzji w oparciu o jakieś informacje. w SQL tą instrukcją jest CASE …. WHEN …. THEN …. END

Opiszmy nasz plan ta instrukcją.

CASE
   WHEN bedzie_padac THEN "Idę do kina"
   WHEN wyjdzie_slonce THEN "Idę na spacer"
END as kino_czy_spacer

Czyli rozpoczynamy blok kodu pisząc CASE, dzięki temu komputer wie, że to co wyświetlimy w danej kolumnie będzie zależało od zawartości czy obliczeń. WHEN – czyli gdy coś się zadzieje, tu dajemy warunek, który zwraca prawdę lub fałsz i THEN czyli co ma się wydarzyć. Na końcu dajemy END, jako koniec instrukcji i as nazwa kolumny.

Przykład 1: Kategoryzacja cenowa książek

Stwórzmy raport, który podzieli nasze książki na „tanie”, „średnie” i „drogie”.

SELECT 
    tytul,
    cena,
    CASE
        WHEN cena < 35.00 THEN 'Tania'
        WHEN cena >= 35.00 AND cena < 50.00 THEN 'Średnia'
        ELSE 'Droga'
    END AS kategoria_cenowa
FROM ksiazki;

Zobacz, co się stało, stworzyliśmy w locie (w locie!) zupełnie nową kolumnę kategoria_cenowa, która nie istnieje fizycznie w bazie. To czysta logika warunkowa.

Przykład 2: CASE wewnątrz GROUP BY i COUNT (Poziom PRO)

CASE można łączyć z funkcjami agregującymi. To potężna technika. Chcemy policzyć, ile mamy książek „Tanich”, „Średnich” i „Drogich”.

Po prostu grupujemy po aliasie naszej nowej kolumny!

SELECT 
    CASE
        WHEN cena < 35.00 THEN 'Tania'
        WHEN cena >= 35.00 AND cena < 50.00 THEN 'Średnia'
        ELSE 'Droga'
    END AS kategoria_cenowa,
    COUNT(*) AS liczba_ksiazek
FROM ksiazki
GROUP BY kategoria_cenowa;

W ten sposób możesz tworzyć własne kategorie analityczne, niedostępne „od ręki” w bazie.

Pojawił się tez nowy element, ELSE – w przeciwnym wypadku, czyli gdy żaden z innych warunków się nie sprawdzi, to zadziała to co jest w ELSE.

Podzapytania na sterydach

W Lekcji 7 pokazałem podzapytania. Używaliśmy ich w WHERE. Ale można ich też użyć w FROM czy w select.

Tworzą one „wirtualną tabelę”.

Problem: Chcemy znaleźć średnią sumę wydatków per klient. Musimy to zrobić w dwóch krokach:

1. Policzyć SUM(wartosc_calkowita) dla każdego klient_id.
2. Z tych sum policzyć AVG().

Sposób 1: Podzapytanie w FROM (Drążymy podzapytania)

SELECT 
    AVG(laczna_sprzedaz_klienta) AS srednia_na_klienta
FROM (
    -- To jest podzapytanie, które działa jak wirtualna tabela
    SELECT 
        klient_id, 
        SUM(wartosc_calkowita) AS laczna_sprzedaz_klienta
    FROM zamowienia
    GROUP BY klient_id
    -- Koniec podzapytania
) AS WirtualnaTabelaSprzedazy;

To działa, ale jest nieczytelne. To właśnie ta „Incepcja”, o której mówiłem. Jeśli mielibyśmy 3 takie kroki, utonęlibyśmy w nawiasach.

Inny przykład, podzapytania w SELECT.

Problem: Chcemy wyświetlić listę wszystkich książek, a obok każdej z nich pokazać średnią cenę książek w jej kategorii. (Np. obok „Wiedźmina” (44.90 zł) chcemy widzieć średnią cenę dla całej „Fantastyki”).

SELECT 
    k_zew.tytul,
    k_zew.cena,
    (
        -- TO ZAPYTANIE WYKONA SIĘ DLA KAŻDEGO WIERSZA Z ZEWNĄTRZ!
        SELECT ROUND(AVG(k_wew.cena), 2) 
        FROM ksiazki k_wew
        WHERE k_wew.kategoria_id = k_zew.kategoria_id
    ) AS srednia_w_kategorii
FROM ksiazki k_zew;

Dlaczego to jest ZŁE?
Zapytanie wewnętrzne musi się uruchomić dla „Ksiąg Jakubowych” (aby policzyć średnią dla kat. 1), potem ponownie dla „Wiedźmina” (dla kat. 2), potem ponownie dla „Solaris” (dla kat. 3) i tak dalej… 50 razy! (bo mamy 50 książek).
Na naszej małej bazie to zajmie ułamek sekundy. Ale na bazie z milionem rekordów, którą wspomniałeś?

To jest przepis na katastrofę wydajnościową.

Sposób 2: CTE (Common Table Expression) – PO PROSTU LEPIEJ

Tu wchodzi CTE. CTE pozwala ci „wyciągnąć” to podzapytanie na górę i nadać mu nazwę. Działa jak zmienna albo funkcja w programowaniu – ale o tym w innym kursie lub później!

Zaczynamy od słowa kluczowego WITH.

-- Krok 1: Definiujemy naszą "zmienną" CTE o nazwie 'SprzedazKlientow'
WITH SprzedazKlientow AS (
    SELECT 
        klient_id, 
        SUM(wartosc_calkowita) AS laczna_sprzedaz
    FROM zamowienia
    GROUP BY klient_id
)
-- Krok 2: Teraz odpytujemy TĘ NAZWĘ, jakby to była zwykła tabela
SELECT 
    AVG(laczna_sprzedaz) AS srednia_na_klienta
FROM SprzedazKlientow;

I drugi przykład:

-- Krok 1: Definiujemy naszą "zmienną" CTE.
-- Liczymy średnią dla KAŻDEJ kategorii, ale robimy to TYLKO RAZ.
WITH SrednieWKsztalcie AS (
    SELECT 
        kategoria_id, 
        ROUND(AVG(cena), 2) AS srednia_w_kategorii
    FROM ksiazki
    GROUP BY kategoria_id
)
-- Krok 2: Teraz odpytujemy ZWYKŁĄ tabelę ksiazki 
-- i DOŁĄCZAMY (JOIN) do niej nasze gotowe wyniki.
SELECT 
    k.tytul,
    k.cena,
    s.srednia_w_kategorii
FROM ksiazki k
JOIN SrednieWKsztalcie s ON k.kategoria_id = s.kategoria_id;

Widzisz różnicę?!

1. Koniec z zagnieżdżaniem w nieskończoność.
2. Kolejność: Kod czyta się logicznie, od góry do dołu. Najpierw definiujemy logikę A, potem używamy logiki A, by zrobić logikę B.
3. Algorytm: To jest „myślenie algorytmiczne” – dzielisz problem na mniejsze kroki (WITH ... AS ...), a na końcu je łączysz.
4. Podzapytania wykonują się raz, i tworzą wirtualne tabele. wykonują się dzięki temu dużo szybciej.

CTE to absolutna podstawa nowoczesnego, CZYTELNEGO SQL-a.

Od dziś staraj się zawsze używać CTE zamiast podzapytań w FROM.

Czyli jeszcze raz:

WITH nazwa_naszej_nowej_wirtualnej_tabeli AS (

SELECT as tu, as nazwy, as kolumn, as w, as nowej, as tabeli

FROM itd

)

Na górze definiujemy, na dole używamy.

Zadanie Domowe (Lekcja 8)

Czas połączyć nową wiedzę!

1. (CASE): Napisz zapytanie, które wyświetli listę klientów (imie, nazwisko, miasto). Dodaj nową kolumnę Region. Jeśli miasto to 'Warszawa’, 'Kraków’ lub 'Gdańsk’, Region ma mieć wartość 'Metropolia’. W przeciwnym wypadku Region ma mieć wartość 'Inne’.
2. (CASE + GROUP BY): Używając zapytania z zadania 1 (skopiuj logikę CASE), policz (COUNT), ilu klientów mieszka w 'Metropolii’, a ilu w 'Innych’ regionach.
3. (CTE): Pokaż imię, nazwisko i łączną wartość zamówień tylko dla 3 klientów, którzy wydali najwięcej. Wskazówka: Najpierw w CTE policz SUM(wartosc_calkowita) dla każdego klient_id, posortuj DESC i weź LIMIT 3. Następnie poza CTE zrób JOIN do tabeli klienci, aby pobrać ich imiona.