Nauka SQL wymaga planu

Nauka SQL wymaga planu. To niby oczywiste, a jednak często wskakujemy do tematu trochę ad hoc. Z tego artykułu dowiesz się, co każdy kurs SQL powinien zawierać.

Oczywiście należy zacząć od instalacji SQLa na własnym komputerze. Wirtualne środowisko nie wystarczy. Dlaczego? Bo gdy przyjdzie do setupu bazy danych w swojej pracy będziesz się czuł jak dziecko we mgle. Będziesz znał tylko to, co dostałeś na tacy. OK, zainstalowane? To jedziemy z planem.

Wersja do oglądania 📺

1. Select * from table

Pierwszą rzecz, którą musisz opanować w SQLu, to zwykłe, proste zapytania. Musisz umieć napisać zwykłego SELECTa, tak? SELECT coś tam, FROM coś tam, żeby wyrzuciło Ci tabelkę.

Chodzi o to, żebyś po prostu rozumiał, że jeżeli puszczamy pewną kwerendę w kliencie, to odpytuje bazę danych i zwraca Ci wiersze i kolumny. Mam przystępny artykuł na ten temat.

Dobrze, żebyś na tym etapie umiał:

  • aliasować kolumny, żeby headery się inaczej nazywały,
  • użyć ORDER BY aby posortować wyniki
  • użyć LIMIT, żeby żeby wyświetlić ileś rzędów

Przykład kodu SQL:

select
	customer_id	as customer
	,product_id as purchase
	,sales 		as sales_line_amount
from orders
order by 3 desc
limit 10

Rezultat kwerendy SQL:

Nauka SQL - przykład select

Są to dosyć proste operacje, a pozwolą Ci używać pierwszych słów kluczowych, poza SELECT i FROM.

2. Nauka podstawowego filtrowania w SQL – WHERE

Drugi krok do opanowania to filtrowanie. I teraz chodzi mi o takie najprostsze filtrowanie, czyli o WHERE. Od razu polecam Ci artykuł, gdzie na prostych przykładach zobaczysz jak działają WHERE i GROUP BY.

Wszystko to, co się wstawia po słowie WHERE, tak zwany where clause. Pozwoli nam on filtrować wyniki, które mają zostać wyświetlone. I tutaj ważne jest to, że oczywiście w WHERE możemy używać takich operatorów logicznych jak AND i OR, co oznacza, że możemy również używać nawiasów, tak?

I na przykład mówić, że „weź mi rzędy, w których ten i ten warunek jest spełniony” i to dajemy w nawias lub „ten i ten warunek” i to dajemy w nawias. I wtedy jedna z tych dwóch par musi zostać spełniona.

Jeżeli jesteśmy przy WHERE, to musimy się nauczyć filtrować nie tylko liczby, ale i daty. Jeżeli filtrujemy daty, no to pewnie musimy się je nauczyć filtrować tak, że zakresy tych dat, czyli na przykład używamy słowa BETWEEN. Oczywiście filtrujemy także teksty i tutaj dobrze byłoby nauczyć się nie tylko filtrowania za pomocą słowa LIKE, ale też jakiegoś prostego regexpa, które nam rozpozna coś takiego, jak regular expressions. Nauka SQL w dużej mierze opiera sią na umiejętności brania odpowiednich danych. Pamiętaj zatem, że żaden sensowny kurs SQL bez solidnego przerobienia możliwości w where clause nie ma sensu.

Przykład kodu SQL:

select
	order_id
	,order_date
	,customer_name
	,city
from orders
where 
	order_date between '2013-01-01' and '2013-01-15'
and customer_name like '%a%'
and	(
		city = 'Athens'
	or	city = 'Houston'
	)

Rezultat kwerendy SQL:

kurs SQL przykład where clause

3. GROUP BY i HAVING

Trzeci etap Twojej nauki SQLa to agregacja grupowania i filtrowanie tych grup.

GROUP BY

Agregacja to jest takie okropne słowo, którego ja nie lubię, bo ono jest takie bardzo bezpłciowe, a chodzi o bardzo proste rzeczy.

Chodzi o to, że sobie coś potrafisz zsumować, że potrafisz wyciągnąć średnią czy medianę, że potrafisz policzyć COUNT, czy COUNT DISTINCT. Zależy nam tu na opanowaniu tego typu obliczeń, czyli tutaj mówimy zazwyczaj o SUM, MEAN, MAX, AVERAGE, COUNT, COUNT DISTINCT.

Razem z agregacją przychodzi nam grupowanie. Musisz się nauczyć grupować, czyli używać słowa GROUP BY. Przy grupowaniu może się pojawić coś takiego, że niektórzy grupując, piszą całe nazwy kolumn, a niektórzy używają tylko liczb, które odpowiadają jakby miejscu, na którym ta kolumna jest, czyli możesz zobaczyć coś takiego jak na przykład GROUP BY 1, 2, 3, co oznacza, że pogrupuj po pierwszej kolumnie, drugiej i trzeciej. Nauka SQL to jak widzisz czasem nauka standardów pisania (bo zadziała i jedno i drugie). Więcej o tym temacie, jakim jest formatowanie SQLa znajdziesz tu.

HAVING

Koncept jest relatywnie prosty, ale jakby musisz wiedzieć, że agregacje i grupowanie idą ze sobą w parze. I do tego trzeciego punktu dochodzi również filtrowanie tych grup, czyli używanie kolejnego słowa kluczowego HAVING.

W tym przypadku nie filtrujemy pojedynczych rzędów z surowych danych, tylko filtrujemy grupy. Wyobraź sobie, że że mamy produkty i sumujemy sprzedaż w tych wszystkich produktach. I później chcemy pokazać tylko te produkty, które osiągnęły łączną sprzedaż powyżej 1000$. Wtedy właśnie użyjemy słówka HAVING, po to, aby przefiltrować rezultat naszego zapytania już po zagregowaniu i zgrupowaniu.

Przykład kodu SQL:

select
	product_id
	,sum(sales) as total_sales
from orders
group by 1
having
	sum(sales) > 1000
order by 2 asc
limit 5

Rezultat kwerendy SQL:

nauka sql - group by i having

4. JOINy: bez tego żaden kurs SQL nie ma sensu

Punkt czwarty jest chyba jakby esencją SQLa, tym co się ludziom najczęściej kojarzy z SQLem, czyli JOINy.

W skrócie czwarty punkt to zbieranie wyników z wielu tabel, nie tylko z jednej, czyli nie tylko użyjemy słowa FROM, ale użyjemy JOIN… ON…, JOIN… ON… I tych JOINów będzie kilka:

  • inner join
  • left join
  • right join
  • full join
  • outer join
  • cross join

Ten punkt jest trudny do opanowania.

Ode mnie dostajesz darmowy SQL Join cheat sheet, który pomoże Ci opanować podstawowe joiny. Znajdziesz go w sekcji materiały do pobrania.

Same JOINy to moment nauki SQLa, gdzie w zasadzie moim zdaniem możesz mówić, że basic SQL kojarzysz. Przypomina to np. opanowanie tabel przestawnych w Excelu (btw., plan nauki Excela tutaj). Dlatego też, jeżeli interesuje Cię jakiś podstawowy kurs SQL, to JOINy naprawdę powinny się tam znaleźć

Przykład kodu SQL:

select distinct
	o.order_id
	,r.returned
	,p.person
from orders o
inner join returns 	r on o.order_id = r.order_id
left join people 	p on o.region = p.region
limit 5
join kurs sql przykład

5. DROP, TRUNCATE, CREATE, UPDATE, DELETE

Jeżeli chodzi o piąty punkt, to na tym etapie warto już nie tylko sczytywać wyniki, ale robić też coś z obiektami w bazie danych. Innymi słowy chodzi nam o naukę czyli nauczyć się takich komend jak DROP, TRUNCATE, UPDATE, CREATE, DELETE.

W skrócie wygląda to tak:

  • DROP – usuwa obiekt z bazy danych (tabelę, indeks, widok, etc.)
  • TRUNCATE – usuwa zawartość tabeli
  • UPDATE – aktualizuje obiekt w bazie danych
  • DELETE – usuwa podane rekordy z tabeli
  • CREATE – tworzy obiekt w bazie danych

Tak, nauka SQL często oznacza w zasadzie naukę słów kluczowych i ich kolejności 😉

Będziesz potrzebował jakiejś tabelki pomocniczej, będziesz potrzebował jakiegoś widoku, zaktualizować jakąś kolumnę – to wszystko się przyda.

Przykład kodu SQL:

CREATE TABLE Managers 
	(
    manager_id 	int
    ,name 		varchar(255)
    ,region		varchar(255)
    )

6. Subquery i CTE, czyli nauka SQL dla zaawansowanych

Kolejny, szósty już punkt to Subqueries i CTE, czyli common table expressions. No i tutaj już wchodzimy na trochę głębsze wody. Mimo tego, że ten temat jest trochę zaawansowany, uważam, że szanujący się kurs SQL powinien go mieć na swojej agendzie. Dlaczego?

Często sam JOIN Ci nie wystarczy, żeby sczytać wyniki z kilku miejsc. Będziesz np. potrzebował kilku kroków agregowania bądź filtrowania, bądź grupowania danych, tak, żeby otrzymać żądany wynik.

SQL Subquery

Jedną z opcji są Subqueries, czyli takie zagnieżdżone SELECTy, gdzie piszesz SELECT… FROM… I cała ta fraza jest umieszczona w kolejnym FROM albo jest umieszczona w WHERE Clause, czyli służy nam do filtrowania wyników.

Przykład kodu SQL:

select count(*) from 
(
select
	order_id
	,order_date
	,customer_name
	,city
from orders
where 
	order_date between '2013-01-01' and '2013-01-15'
and customer_name like '%a%'
and	(
		city = 'Athens'
	or	city = 'Houston'
	)	
) data_needed

Rezultat kwerendy SQL:

CTE (Common Table Expressions)

Innym podejściem są common table expressions, CTE, o których zresztą jest osobny artykuł. CTE, czyli Common Table Expressions, umożliwiają nam tworzenie takich tabel tymczasowych w locie.

To znaczy: jak sobie piszemy CTE, piszemy sobie kolejne SELECTy, to SQL zapamiętuje rezultat. Później, możemy sobie z tych rezultatów w trakcie jednej kwerendy korzystać, tak jakbyśmy mieli te tabele już dostępne w bazie danych.

Ja CTE bardzo lubię, bo w odróżnieniu od tych zagnieżdżonych SELECTów, czyli subqueries są one bardzo czytelne. Można coś napisać jako krok 1, krok 2, krok 3, tak? I wtedy łatwiej się to czyta. Więc to jest do nauczenia się jako punkt szósty.

Przykład kodu SQL:

with
first_class as
    (
    select
        date_format(order_date, '%Y-%m-01') as order_month
        ,count(distinct customer_id)        as customers
        ,sum(sales)                         as sales
    from orders
    where ship_mode = 'First Class'
    group by 1
    ),
second_class as
    (
    select
        date_format(order_date, '%Y-%m-01') as order_month
        ,count(distinct customer_id)        as customers
        ,sum(sales)                         as sales
    from orders
    where ship_mode = 'Second Class'
    group by 1
    ),
months as
    (
    select distinct date_format(order_date, '%Y-%m-01') as order_month
    from orders
     )
select
    m.order_month   as order_month
    ,fc.customers   as first_class_customers
    ,sc.customers   as second_class_customers
    ,fc.sales       as first_class_sales
    ,sc.sales       as second_class_sales
from months m
left join first_class     fc on fc.order_month = m.order_month
left join second_class    sc on sc.order_month = m.order_month
order by 1 asc
limit 5

Rezultat kwerendy SQL:

CTE przykład

7. Window functions – funkcje analityczne w SQL

Window functions to już rzecz zupełnie zaawansowana. Polega ona na tym, że jak sumujemy sobie różne rzeczy, to jak wspominałem wcześniej, musisz je grupować. Ale może się zdarzyć tak, że chciałbyś, żeby pewna suma była zawsze albo średnia, albo inny agregat, był zawsze wymieniamy tylko w konkretem rzędzie, czyli bez użycia słowa GROUP BY. Wtedy używasz czegoś, co się nazywa Window functions.

Przy funkcjach analitycznych silnik SQLa będzie robił to grupowanie na podstawie jednego, konkretnego rzędu i podanych parametrów. Tam zobaczysz takie rzeczy jak, mamy użycie tego agregatu czy na przykład SUM, a później będziesz miał coś w rodzaju OVER (Partition by…) i później ORDER BY, jeżeli to jest potrzebne. I tutaj dzięki temu będziesz mógł sumować, agregować rzeczy, uśredniać je, wyciągać maksymalną, minimalną wartość na poziomie jednego, konkretnego rzędu tak, że będziesz brał tylko konkretne wartości do tego, co jest potrzebne Ci w danym agregacie.

Rząd przy którym pojawi się obliczenie jest tak naprawdę po prostu punktem odniesienia do “okna”, w którym te obliczenia będą miały miejsce.

Przykład kodu SQL

select
	order_id
	,order_date
	,customer_name
	,sales
	,sum(sales) over(partition by customer_id) as customer_sales
	,sum(sales)	over() as total_profit
from orders
order by order_date desc
limit 10

Rezultat kwerendy SQL

window functions przykład

Przy okazji, nauczysz się na tym etapie tworzenia rankingów. Zaczniesz korzystać z takich rzeczy jak DENSE_RANK albo ROW_NUMBER, albo po prostu RANK, w zależności od tego jaki “rodzaj” rankingu jest Ci potrzebny.

Innym przykładem funkcji z tej grupy jest funkcja LAG pozwalająca znaleźć poprzedni rekord. Mam o niej artykuł 😀

Tu akurat nie chcę być jakoś bardzo surowy jeśli chodzi o kursy. Myślę, że może istniejć dobry kurs SQL z samymi podstawami, który tego zagadnienia nie omawia.

8. Optymalizacja

Wreszcie punkt 8, ostatni. I na tym etapie już umiesz naprawdę bardzo dużo rzeczy. Warto by było, myślę, że już wcześniej, ale w tym miejscu już na pewno, myśleć o optymalizacji.

No bo tak. Napiszesz pewnego SELECTa, że napiszesz sobie jakąś kwerendę i ona działa, to super. Pytanie, czy ona działa w sposób optymalny, czy to jest najszybszy sposób uzyskania tych informacji?

I to już jest o tyle trudne, że nie uczysz się jakby konkretnych funkcji. Uczysz się tego, żeby na przykład patrzeć w sensowny sposób na swoje WHERE Clause, czy czasem nie da się tego łatwiej napisać albo czy nie używasz bezsensownych funkcji. Patrzeć na to, czy w optymalny sposób używasz JOINów.

Warto jest na tym etapie również kojarzyć, jak działają indeksy i jak sobie ten indeks założyć, zdjąć, co indeks daje, a w czym indeks przeszkadza, tak żeby również tam, gdzie trzeba, optymalizować to wyszukiwanie.

No i, moim zdaniem, częścią tego punktu jest również ładne pisanie swojego SQLa.

Nauka SQL: podsumowanie

Jeżeli chcesz pracować jako analityk danych to nauka SQL jest nieunikniona. Wiem, że możesz sobie stawiać rozkminy typu “hmmm.. a może Python?“, ale zaufaj mi ostatecznie potrzebujesz SQL. Co za tym idzie, pewnie będziesz szukał jakiś materiałów szkoleniowych.

A zatem, taki kurs SQL (albo Twój prywatny plan nauki) powinien zawierać takie elementy jak

  1. Select * from table – czyli podstawy
  2. WHERE
  3. GROUP BY i HAVING
  4. JOINy
  5. DROP, TRUNCATE, CREATE, UPDATE, DELETE
  6. Subquery i CTE
  7. Window functions (nie obowiązkowo)
  8. Optymalizacja SQLa (nie obowiązkowo)

Mam nadzieję, że taki plan jest dla Ciebie przydatny. Daj mi znać w komentarzu, jesli myślisz, że czegoś tu jednak brakuje. A skoro jesteśmy przy listach, to obczaj ten artykuł o kolejności wykonywania komend w SQL.

To tyle w tym temacie. Analizujcie w pokoju!  📊📈

Podobał Ci się ten artykuł?
Podziel się nim w Social Mediach:
>>> udostępnij go na LinkedIn i pokaż, że codziennie uczysz się czegoś nowego 💻
>>> wrzuć go na Facebooka, to się może przydać któremuś z Twoich znajomych 💗
>>> Przypnij sobie tą stronkę to zakładek, może się przydać w przyszłości 💡

Wolisz oglądać 📺 niż czytać – nie ma problemu 😉
>>> Obserwuj i oglądaj KajoData na YouTube

A jeśli chcesz się dowiedzieć czegoś więcej o SQL – zajrzyj tutaj.

3 Responses

  1. Wiesz co? Chyba tylko humanista potrafi w taki sposób jak Ty wytłumaczyć zagadnienia ścisłe, to jest przepiękne połączenie!!! Jak będziesz kiedyś w Lublinie to stawiam piwo bo jesteś po prostu gość 🙂

      1. No to wbijaj w wolnym czasie, oprowadzę 🙂 o analizie danych też można pogadać i giełdzie (widziałem że też się interesujesz :-))

Leave a Reply

Your email address will not be published.