Lekcje kursu · Praca z danymi

Analityk danych — Praca z danymi

Sprzedaż sklepu w dwóch kanałach

Praca z danymi · Lekcja 4 z 9

SQL: wybierz i pogrupuj

SQL to język do zadawania pytań bazie danych. SELECT mówi, jakie kolumny chcesz zobaczyć. WHERE zostawia tylko pasujące wiersze. GROUP BY składa wiersze o tej samej dacie i kanale w jedną grupę. COUNT(*) liczy wiersze w każdej grupie, a ORDER BY ustala kolejność wyniku.

Przykład z pracy

Żeby zobaczyć liczbę opłaconych zamówień, wystarczy tabela „zamówienia”. W niej jedno zamówienie to jeden wiersz, więc COUNT(*) liczy właśnie zamówienia.

Chcesz uruchomić kod? Pobierz dane do PostgreSQL ↓. W osobnej, pustej bazie wykonaj plik startowy, a potem poniższe zapytanie. Możesz też od razu przejrzeć wynik.

SQL

SELECT data, kanal, COUNT(*) AS liczba_zamowien
FROM zamowienia
WHERE status = 'opłacone'
GROUP BY data, kanal
ORDER BY data, kanal;

Opłacone zamówienia według dnia i kanału
data	kanal	liczba_zamowien
10 listopada	Online	1
10 listopada	Sklep	1
11 listopada	Online	1
11 listopada	Sklep	1

Ćwiczenie praktyczne

Otwórz przykład SQL. Usuń „kanal” z SELECT, GROUP BY i ORDER BY. Opisz, jak zmienia się znaczenie jednego wiersza wyniku.

Sprzedaż sklepu w dwóch kanałach

Podejmij decyzję

Wróć do notatki z początku sprawy. Zapisz pierwsze działanie i wyjaśnij, jak pomoże rozwiązać sytuację.

Pokaż przykładową reakcję

Sprawdź status i numer zamówienia, a wartość policz z pozycji produktów. Każda pozycja wnosi własną wartość, a liczba zamówień pozostaje niezależna od liczby produktów.

Sprawdź się — opcjonalne pytanie

Gdzie wpiszesz warunek „tylko opłacone zamówienia”?

W WHERE
W GROUP BY
W ORDER BY

Pokaż odpowiedź i wyjaśnienie

W WHERE. WHERE filtruje wiersze przed grupowaniem.

Praca z danymi · Lekcja 5 z 9

SQL: połącz dwie tabele

Żeby pokazać datę i kanał przy każdym produkcie, potrzebujesz obu tabel. Użyj JOIN i wskaż po ON, że numer zamówienia ma być taki sam po obu stronach. Litery z i p to skróty nazw tabel. INNER JOIN pokazuje tylko dopasowania. LEFT JOIN zostawia też wszystkie zamówienia z lewej tabeli, nawet jeśli po prawej nie ma pozycji.

Przykład z pracy

Dla 101 zobaczysz dwa wiersze: po jednym dla notesu i długopisu. Przy LEFT JOIN zobaczysz też 104 i 106, z pustym polem produktu, bo w tym zestawie nie mają pozycji.

zamowienia · jeden wiersz na zamówienie
id_zamowienia	data	kanal	status
101	10 listopada	Sklep	opłacone
102	10 listopada	Online	opłacone
103	11 listopada	Online	opłacone
104	11 listopada	Online	anulowane
105	11 listopada	Sklep	opłacone
106	11 listopada	Online	oczekuje

pozycje_zamowien · jeden wiersz na pozycję
id_pozycji	id_zamowienia	produkt	ilosc	cena
1	101	Notes	2	30 zł
2	101	Długopis	1	20 zł
3	102	Kubek	1	70 zł
4	103	Notes	1	30 zł
5	103	Kubek	1	70 zł
6	105	Długopis	2	20 zł

zamowieniaid_zamowienia · 1pozycje_zamowienid_zamowienia · wiele

INNER JOIN — tylko pasujące wiersze

SQL

SELECT z.id_zamowienia, z.data, z.kanal,
       p.produkt, p.ilosc, p.cena
FROM zamowienia AS z
INNER JOIN pozycje_zamowien AS p
  ON z.id_zamowienia = p.id_zamowienia
ORDER BY z.id_zamowienia, p.id_pozycji;

Wynik: 6 wierszy
id_zamowienia	data	kanal	produkt	ilosc	cena
101	10 listopada	Sklep	Notes	2	30 zł
101	10 listopada	Sklep	Długopis	1	20 zł
102	10 listopada	Online	Kubek	1	70 zł
103	11 listopada	Online	Notes	1	30 zł
103	11 listopada	Online	Kubek	1	70 zł
105	11 listopada	Sklep	Długopis	2	20 zł

LEFT JOIN — także zamówienia bez pozycji

SQL

SELECT z.id_zamowienia, z.data, z.kanal,
       p.produkt, p.ilosc, p.cena
FROM zamowienia AS z
LEFT JOIN pozycje_zamowien AS p
  ON z.id_zamowienia = p.id_zamowienia
ORDER BY z.id_zamowienia, p.id_pozycji;

Wynik: 8 wierszy
id_zamowienia	data	kanal	produkt	ilosc	cena
101	10 listopada	Sklep	Notes	2	30 zł
101	10 listopada	Sklep	Długopis	1	20 zł
102	10 listopada	Online	Kubek	1	70 zł
103	11 listopada	Online	Notes	1	30 zł
103	11 listopada	Online	Kubek	1	70 zł
104	11 listopada	Online	`NULL`	`NULL`	`NULL`
105	11 listopada	Sklep	Długopis	2	20 zł
106	11 listopada	Online	`NULL`	`NULL`	`NULL`

Ćwiczenie praktyczne

Otwórz oba wyniki złączenia. Znajdź 101 oraz 104 i powiedz, skąd bierze się różnica między tymi dwoma przypadkami.

Sprawdź się — opcjonalne pytanie

Które złączenie pokaże wszystkie zamówienia, także te bez produktów w tabeli pozycji?

INNER JOIN
LEFT JOIN, z zamówieniami po lewej stronie
Wystarczy ORDER BY

Pokaż odpowiedź i wyjaśnienie

LEFT JOIN, z zamówieniami po lewej stronie. LEFT JOIN zachowuje wiersze z lewej tabeli. Brakujące kolumny z prawej strony mają wartość NULL.

Praca z danymi · Lekcja 6 z 9

Sprawdź sumę po złączeniu

Po złączeniu jeden wiersz wyniku oznacza pozycję, nie całe zamówienie. Wartość pozycji to ilość razy cena. Sumujemy te wartości po odfiltrowaniu nieopłaconych zamówień. Jeśli raport ma też pokazać liczbę zamówień, użyj COUNT(DISTINCT numer_zamówienia), czyli policz każdy numer tylko raz.

Przykład z pracy

Zamówienie 101 ma dwie pozycje za 60 i 20 zł. Ich suma to 80 zł. Gdyby kwota 80 zł była zapisana przy zamówieniu i powieliła się obok obu produktów, jej prosta suma błędnie dałaby 160 zł.

SQL

SELECT z.data, z.kanal,
       SUM(p.ilosc * p.cena) AS sprzedaz_zl,
       COUNT(DISTINCT z.id_zamowienia) AS liczba_zamowien
FROM zamowienia AS z
INNER JOIN pozycje_zamowien AS p
  ON z.id_zamowienia = p.id_zamowienia
WHERE z.status = 'opłacone'
GROUP BY z.data, z.kanal
ORDER BY z.data, z.kanal;

Sprzedaż i liczba opłaconych zamówień
data	kanal	sprzedaz_zl	liczba_zamowien
10 listopada	Online	70 zł	1
10 listopada	Sklep	80 zł	1
11 listopada	Online	100 zł	1
11 listopada	Sklep	40 zł	1
Razem	Wszystkie	290 zł	4

Ćwiczenie praktyczne

Porównaj raport SQL z tabelą przestawną z lekcji 3. Gdy kwoty są różne, sprawdź najpierw statusy, potem poziom wiersza i sposób sumowania.

Sprawdź się — opcjonalne pytanie

Dlaczego COUNT(*) po złączeniu może zawyżyć liczbę zamówień?

Bo jedno zamówienie może pojawić się przy kilku pozycjach
Bo data jest zapisana tekstem
Bo ORDER BY dodaje wiersze

Pokaż odpowiedź i wyjaśnienie

Bo jedno zamówienie może pojawić się przy kilku pozycjach. COUNT(*) widzi wszystkie wiersze wyniku, czyli w tym przypadku pozycje. DISTINCT pozwala policzyć każdy numer zamówienia raz.

Dokumentacja do kursu

Przejdź do przygotowania do rozmowy →