Wstęp: menu lekcji

Czołgiem na 6 już lekcji.

Jeśli jesteś tu ze mną, to znaczy, że to szósty tydzień twojej nauki jeśli lecisz równo z kursem.

Na wcześniejszych lekcjach pobieraliśmy dane, łącząc różne tabele, a ostatnio nawet mnożyliśmy wartości z różnych kolumn. Dziś idziemy krok dalej, i pokażę ci jak dane podsumowywać.

Będziemy analizować ilu mamy klientów z jakich miast.

Jakie pozycje w ofercie są najpopularniejsze.

Jaka jest średnia cena książki z danej kategorii.

To wstęp do analizy danych i tworzenia raportów.

Pamiętaj, aby wszystkie zapytania wykonywać w DBeaver, w edytorze SQL połączonym z naszą bazą, jeśli trafiłeś na tą lekcję wprost, to polecam albo zobaczyć kurs od początku: SQL od Zera: Twój Pierwszy Krok w Świat Danych (Nawet Jeśli Nie Jesteś z IT) albo od lekcji gdzie instalujemy aplikacje o której mówię: SQL od Zera, cz. 4: Troszkę podsumowania i koniec z piaskownicą! Instalujemy lokalne narzędzia i Twoją pierwszą bazę danych

Temat 1: Funkcje agregujące – co to takiego?

W skrócie, takie funkcje to narzędzia, które robią coś, wykonują obliczenia na jakimś zbiorze i zwracają jedną wartość.

Najpierw czytelny przykład, załóżmy że mamy tabelę zawierającą zamówienia z miejscem zamówienia i kwotą (to zupełny przykład).

id	zamowienie	miesiac	kwota
1	2025-01/01	styczeń	45.32
2	2025-01/02	styczeń	51.44
3	2025-01/03	styczeń	186.45
4	2025-02/01	luty	11
5	2025-02/02	luty	23.54
6	2025-02/03	luty	65
7	2025-03/01	marzec	18.23
8	2025-03/02	marzec	79.99
9	2025-03/03	marzec	730.01

I teraz wyjaśnię na tym przykładzie jak działają podstawowe funkcję agregujące, mam nadzieję, że w bardzo prosty sposób.

Pierwsze o co chcemy zapytać, to ile w ogóle mamy rekordów.

select count(*) from tabela

Wynik to 9 bo tyle mamy wierszy / rekordów w naszej tabeli. Symbol * oznacza, że zliczamy po prostu wiersze.

Zsumujmy teraz całkowity utarg, dodajmy wszystkie kwoty

select sum(kwota) from tabela

Wynik to suma wszystkich wartości czyli 1210,98 (45.32 + 51.44 + 186.45 + 11 + 23.54 + 65 + 18,23 + 79.99 + 730.01) PS, policzyłem to w pamięci, jak jest błąd, popraw mnie w komentarzu;)

Podliczmy średnia cenę zamówienia

select avg(kwota) from tabela

Średnia to (45.32 + 51.44 + 186.45 + 11 + 23.54 + 65 + 18,23 + 79.99 + 730.01) dzielone przez ilość rekordów czyli 9 = 1210.98/9 = 134.55

Jeszcze dwie na dziś, maksymalna i minimalna, zaczynamy od poszukania największej wartości.

select max(kwota) from tabela

Da wynik 730.11 a z kolei

select min(kwota) from tabela

Da wynik 11.

Podsumowując, najważniejsze funkcje, które musisz znać:

• COUNT() – zlicza wiersze (rekordy).
• SUM() – sumuje wartości w kolumnie (tylko dla liczb).
• AVG() – oblicza średnią wartość w kolumnie (tylko dla liczb).
• MAX() – znajduje maksymalną (najwyższą) wartość.
• MIN() – znajduje minimalną (najniższą) wartość.

I tu uwaga, w większości, o ile nie we wszystkich bazach, wielkość znaków nie ma znaczenia, ale dobrym zwyczajem jest pisanie dużymi literami, czego widzisz, że wyżej nie czynię 🫢(przy okazji zupełni niezwiązanej z SQL, dziś dzięki koleżance z pracy poznałem ekstra stronę, skąd można kopiować emotki: https://emojiteka.pl)

Temat 2: Grupowanie

Teraz będę pokazywał już przykłady z naszej bazy.

Grupowanie to potęga. Cóż nam z tych danych wcześniej, to nic nie mówiąca statystyka, ale, może chcemy sprawdzić ile było zamówień w każdym miesiącu? Albo, ile jest zamówień w danym statusie!

Odpalmy nasze dane.

Zanim zrobimy grupowanie po miesiącu zauważ, że nie mamy po prostu miesiąca w prawdziwych danych, mamy datę i godzinę. Nasza baza jest bardzo prosta, i nie używamy tu nawet formatu daty, wiec musimy sobie pomóc, i użyjemy funkcji strftime(’%Y-%m’, data_zlozenia). W życiu wiele razy się spotkasz z sytuacją, iż dane będą w bazie z w złym formacie i trzeba sobie z tym radzić.

nasze query

SELECT strftime('%Y-%m', data_zlozenia) FROM zamowienia;

Zwróci dokładnie to czego potrzebujemy – ten element ’%Y-%m’ to po prostu formatowanie dat. %Y to rok z czterema cyframi a %m to miesiąc.

teraz więc pogrupujmy ze sobą wiersze po tej dacie

SELECT strftime('%Y-%m', data_zlozenia) data_zamowienia
FROM zamowienia 
GROUP BY strftime('%YY%m', data_zlozenia) ;

Tłumaczę co się stało.

Wykonaliśmy funkcję zmieniająca TEXT na format rok-miesiąc

Klauzula na końcu GROUP BY (grupuj po) łączy ze sobą wiersze, gdzie wybrana kolumna jest taka sama.

W powyższym przypadku otrzymamy tylko

ok, mamy po jednym razie już miesiące, nie powtarzają się, ale czas zliczyć, ile było tam rekordów, więc dodajemy count(*)

SELECT strftime('%Y-%m', data_zlozenia) data_zamowienia,
       count(*) ile 
FROM zamowienia 
GROUP BY strftime('%YY%m', data_zlozenia) ;

Co nam to mówi? Że następuje grupowanie, ale dane z wierszy nie zginą, jeśli wykonamy na grupowanych rekordach funkcje agregującą!

To samo, sprawdźmy ile jest w jakim statusie:

SELECT status, 
       count(*) ile
FROM zamowienia 
GROUP BY status ;

Mamy teraz pogrupowane dane po statusie zamówienia, i zliczone wiersze w każdym ze statusów.

Dodajmy teraz sumowanie. Zgrupujemy dane po mieście zamawiającego, pokażemy ile było rekordów, i zrobimy SUMe zamówień z poszczególnych miast.

SELECT k.miasto, 
       count(*) ile, 
       sum(z.wartosc_calkowita ) suma 
FROM zamowienia z 
     join klienci k on z.klient_id = k.klient_id 
GROUP BY k.miasto ;

Co tu się dzieje?

• Wybieramy tabele zamówienia i dodajemy alias z
• Łączymy z tabelą klienci, której dajemy alias k a łączymy po klient_id (on z.klient_id = k.klient_id )
• następnie grupujemy po k.miasto
• i oczywiście na górze, wybieramy k.miasto, zliczamy wiersze count(*) oraz robimy sum(z.wartosc_calkowita ) suma .

Myślę, że sądziłeś, że takie operacje są trudniejsze, prawda?

Ale zaraz, posortujemy to po ilości zamówień! będzie czytelniej.

Dodaj na samym końcu query (ale przed 😉 – order by ile;

To bardzo ważne, gdy wykonujemy grupowanie, musimy takie rzeczy dodawać w odpowiedniej kolejności. Najpierw się grupuje, a potem sortuje dopiero już pogrupowane.

Poeksperymentuj.

Użyj teraz średniej na miasto, a potem znajdź max i min.

Mamy informacje, że najwięcej książek sprzedaje się w Warszawie, ale wcale w kwotach Warszawa nie prowadzi. A inne miasta? Katowice i łódź? Może trzeba się tam bardziej reklamować?

Na koniec tych obliczeń sprawdźmy klientów. Kto najwięcej u nas wydaje, i kto robi najwięcej zakupów. to może być jedna osoba, lub rożne.

SELECT k.imie||' '||k.nazwisko as nazwa,
       count(*) ile, 
       sum(z.wartosc_calkowita ) suma 
FROM zamowienia z 
join klienci k on z.klient_id = k.klient_id 
GROUP BY nazwa 
order by ile;

Temat 3: Filtrowanie po grupowaniu – HAVING

Prosta sprawa, w powyższym, wyobraźmy sobie, ze mamy milion klientów, i nie interesują nas ci, co zamawiają rzadko. np. interesuje nas, gdy klient zamówił ponad 1 sztukę.

po Group by dodajemy klauzulę HAVING

SELECT k.imie||' '||k.nazwisko as nazwa,
       count(*) ile, 
       sum(z.wartosc_calkowita ) suma 
FROM zamowienia z 
join klienci k on z.klient_id = k.klient_id 
GROUP BY nazwa 
HAVING count(*)>1
order by ile;

I Klienci z mniejsza ilością zamówień niż 2 się już nie pokazują.

Zadanie domowe

Od tej lekcji będę dawał zadania domowe, a na następnej pokazywał rozwiązanie.

1. Pokaż klientów (ID lub lepiej imię i nazwisko), którzy wydali w sumie ponad 200 zł
2. Pokaż miasta, z których mamy WIĘCEJ NIŻ jednego klienta
3. Pokaż kwotę maksymalnego zamówienia dla każdego miasta

PODSUMOWANIE

Prawidłowa kolejność wykonywania operacji to – > Grupowanie (GROUP BY) -> pogrupowane filtrujemy (HAVING) -> przefiltrowane sortujemy (ORDER BY)

Oraz pogrupowane, możemy zliczać na różne sposoby, obliczać SUM, max, min, avg i wiele, wiele innych.

Do za tydzień!