Mikrokurs: SQL dla analityków danych 0000-MIKRO-SQLDAD-UP

MODUŁ 1. ŚRODOWISKO BAZ DANYCH I PODSTAWY SQL

Cele modułu:
• zrozumienie, czym jest relacyjna baza danych,
• poznanie różnic między tabelą SQL a obiektem DataFrame,
• zrozumienie różnicy między bazą in-process a systemem klient–serwer,
• napisanie i wykonanie pierwszych zapytań SQL,
• uruchamianie zapytań DuckDB z poziomu Pythona.

Zakres tematyczny:

1. Baza danych i relacyjny model danych
• baza danych, tabela, relacja, wiersz i kolumna,
• schemat bazy danych,
• katalog, tabela, widok i zapytanie,
• typ danych kolumny,
• podobieństwa i różnice między tabelą SQL a Pandas DataFrame,
• deklaratywny charakter języka SQL.

2. Architektury baz danych
• baza in-process i embedded,
• baza klient–serwer,
• lokalny plik bazy danych i baza w pamięci,
• proces aplikacji a proces serwera,
• połączenie lokalne i połączenie sieciowe,
• opóźnienie sieciowe i koszt przesyłania wyników,
• współdzielenie danych przez wielu użytkowników,
• podstawowe różnice między DuckDB, SQLite i PostgreSQL,
• przykładowe zastosowania DuckDB i PostgreSQL.

3. Pierwsze zapytania
• SELECT, INSERT, UPDATE
• FROM,
• wybór kolumn,
• aliasy kolumn i tabel,
• WHERE,
• operatory porównania,
• AND, OR i NOT,
• IN i BETWEEN,
• LIKE,
• ORDER BY,
• LIMIT,
• DISTINCT.

4. Typy danych i wartości brakujące
• liczby całkowite i zmiennoprzecinkowe,
• tekst,
• wartości logiczne,
• data i czas,
• wartość NULL,
• porównywanie wartości z NULL,
• IS NULL i IS NOT NULL,
• wprowadzenie do trójwartościowej logiki SQL.

5. SQL z poziomu Pythona
• instalacja i import biblioteki duckdb,
• utworzenie bazy w pamięci,
• utworzenie lub otwarcie pliku bazy DuckDB,
• wykonanie zapytania przy użyciu połączenia,
• pobranie wyniku jako Pandas DataFrame,
• wykonanie zapytania bezpośrednio na obiekcie DataFrame.
• porównanie zapytania SQL z odpowiadającym mu kodem Pandas.

6. Transakcje bazodanowe – wstęp.

MODUŁ 2. TRANSFORMOWANIE, AGREGOWANIE I CZYSZCZENIE DANYCH

Cele modułu:
• wykonywanie obliczeń na kolumnach,
• grupowanie i agregowanie danych,
• tworzenie podstawowych wskaźników analitycznych,
• poprawne przetwarzanie wartości NULL,
• stosowanie funkcji tekstowych oraz funkcji daty i czasu.

Zakres tematyczny:

1. Wyrażenia SQL
• operacje arytmetyczne,
• kolejność wykonywania działań,
• tworzenie kolumn obliczanych,
• aliasowanie wyników,
• rzutowanie typów przy użyciu CAST,
• podstawowe konsekwencje niezgodności typów.

2. Logika warunkowa i wartości brakujące
• CASE WHEN,
• COALESCE,
• NULLIF,
• zastępowanie wartości brakujących,
• rozróżnienie między NULL, pustym tekstem i wartością zero,
• wpływ NULL na obliczenia i agregacje.

3. Funkcje tekstowe
• zmiana wielkości liter,
• łączenie tekstu,
• wycinanie fragmentów tekstu,
• usuwanie zbędnych znaków,
• podstawowe dopasowanie wzorców.

4. Daty i czas
• typy DATE, TIME i TIMESTAMP,
• wyodrębnianie roku, miesiąca i dnia,
• różnice pomiędzy datami,
• grupowanie danych według okresów,
• podstawowe problemy związane z czasem i strefami czasowymi.

5. Agregacje
• COUNT,
• COUNT DISTINCT,
• SUM,
• AVG,
• MIN i MAX,
• GROUP BY,
• grupowanie według więcej niż jednej kolumny,
• HAVING,
• filtrowanie przed agregacją i po agregacji,
• różnica między WHERE i HAVING.

6. SQL i Pandas
• GROUP BY a Pandas groupby,
• CASE WHEN a funkcje NumPy i Pandas,
• wykonanie agregacji w DuckDB i przekazanie małego wyniku do Pandas,
• unikanie niepotrzebnego pobierania pełnych danych do pamięci Pythona.

MODUŁ 3. MODEL RELACYJNY, ŁĄCZENIE TABEL I KONTROLA POPRAWNOŚCI

Cele modułu:
• rozumienie relacji między tabelami,
• poprawne łączenie danych,
• rozpoznawanie błędów wynikających z niewłaściwej liczności relacji,
• poznanie podstaw definiowania tabel i ograniczeń,
• wprowadzenie do analizy wydajności operacji JOIN.

Zakres tematyczny:
1. Projektowanie danych
• klucz główny,
• klucz obcy,
• klucz naturalny i techniczny,
• relacja jeden-do-jednego,
• relacja jeden-do-wielu,
• relacja wiele-do-wielu,
• tabela pośrednicząca,
• ziarnistość danych, czyli znaczenie pojedynczego wiersza,
• podstawowa normalizacja,
• denormalizacja na potrzeby analiz,
• tabela faktów i tabela wymiarów,
• schemat gwiazdy jako typowy model analityczny.

2. Definiowanie i modyfikowanie danych
• CREATE TABLE,
• podstawowe typy kolumn,
• NOT NULL,
• PRIMARY KEY,
• FOREIGN KEY,
• UNIQUE,
• CHECK,
• INSERT,
• UPDATE i DELETE,
• CREATE TABLE AS SELECT,
• tabele tymczasowe,
• podstawowe zastosowanie widoków.

3. Łączenie tabel
• INNER JOIN,
• LEFT JOIN,
• RIGHT JOIN,
• FULL OUTER JOIN,
• CROSS JOIN,
• warunek ON,
• łączenie po wielu kolumnach,
• self join,
• wprowadzenie do EXISTS, semi join i anti join.

4. Typowe błędy przy łączeniu
• niezamierzony iloczyn kartezjański,
• duplikowanie rekordów,
• relacje wiele-do-wielu,
• brakujące rekordy po operacji INNER JOIN,
• wartości NULL po operacji LEFT JOIN,
• agregowanie danych po połączeniu tabel o różnej ziarnistości,
• kontrola liczby rekordów przed połączeniem i po nim.

5. Pierwsze zagadnienia wydajnościowe
• koszt skanowania tabel,
• wpływ liczby wierszy na koszt JOIN,
• selektywność filtrów,
• wczesne ograniczanie zakresu danych,
• wybieranie wyłącznie potrzebnych kolumn,
• wprowadzenie do polecenia EXPLAIN,
• podstawowe operatory planu: scan, filter, projection i join.

MODUŁ 4. ZAAWANSOWANE ZAPYTANIA ANALITYCZNE

Cele modułu:
• dzielenie złożonych problemów na mniejsze etapy,
• tworzenie czytelnych zapytań wieloetapowych,
• stosowanie funkcji okna,
• rozwiązywanie typowych problemów analitycznych bez ręcznych pętli w Pythonie.

Zakres tematyczny:
1. Podzapytania
• podzapytania w klauzuli FROM,
• podzapytania w WHERE,
• podzapytania skalarne,
• IN i EXISTS,
• podzapytania skorelowane – omówienie zastosowania i potencjalnego kosztu.

2. Common Table Expressions
• klauzula WITH,
• definiowanie kilku CTE,
• dzielenie analizy na etapy,
• poprawa czytelności i testowalności zapytania,
• różnica między CTE, podzapytaniem, widokiem i tabelą tymczasową.

3. Operacje zbiorowe
• UNION,
• UNION ALL,
• INTERSECT,
• EXCEPT,
• zgodność liczby i typów kolumn,
• koszt usuwania duplikatów,
• wybór między UNION i UNION ALL.

4. Funkcje okna
• klauzula OVER,
• PARTITION BY,
• ORDER BY wewnątrz okna,
• ROW_NUMBER,
• RANK i DENSE_RANK,
• LAG i LEAD,
• sumy narastające,
• średnie ruchome,
• udział rekordu w wyniku grupy,
• podstawowe ramy okna.

5. Typowe wzorce analityczne
• Top N w każdej grupie,
• pierwszy i ostatni zakup klienta,
• porównanie rekordu z poprzednim okresem,
• narastająca wartość sprzedaży,
• deduplikacja danych,
• segmentacja klientów,
• podstawy analizy retencji i aktywności kohortowej.

6. Czytelność i debugowanie SQL
• formatowanie zapytań,
• jednoznaczne aliasy,
• unikanie SELECT * w analizach produkcyjnych,
• testowanie kolejnych CTE,
• kontrola wyników pośrednich,
• stosowanie komentarzy.

MODUŁ 5. WYDAJNOŚĆ SQL, INDEKSY I ANALIZA DUŻYCH ZBIORÓW

Cele modułu:
• rozumienie podstawowych źródeł kosztu zapytania,
• odczytywanie uproszczonych planów wykonania,
• rozumienie zastosowania i kosztu indeksów,
• poznanie różnic między optymalizacją systemów transakcyjnych i analitycznych,
• wydajne odpytywanie plików Parquet i zbiorów Apache Arrow.

Zakres tematyczny:

1. Źródła kosztu zapytania
• liczba przetwarzanych wierszy,
• liczba odczytywanych kolumn,
• rozmiar danych,
• skanowanie sekwencyjne,
• filtrowanie,
• sortowanie,
• agregowanie,
• operacje JOIN,
• przesyłanie wyników do procesu Pythona,

2. Plany wykonania
• EXPLAIN,
• EXPLAIN ANALYZE,
• plan logiczny i fizyczny,
• skan tabeli,
• skan indeksowy,
• filter i projection,
• hash join,
• agregacja,
• sortowanie,
• estymowana i rzeczywista liczba rekordów,
• wyszukiwanie miejsca ograniczającego wydajność.

3. Podstawy indeksowania
• indeks jako dodatkowa struktura dostępu do danych,
• skan sekwencyjny a skan indeksowy,
• selektywność warunku,
• koszt utworzenia indeksu,
• dodatkowe zużycie miejsca,
• wpływ indeksów na INSERT, UPDATE i DELETE,
• sytuacje, w których indeks nie poprawia wydajności,
• konieczność weryfikowania wykorzystania indeksu w planie zapytania.

4. Rodzaje i warianty indeksów

• B-tree,
• indeksy hash,
• indeksy jednokolumnowe,
• indeksy wielokolumnowe,
• znaczenie kolejności kolumn,
• indeksy unikalne,
• indeksy częściowe,
• indeksy na wyrażeniach,
• indeksy pokrywające i odczyt bez sięgania do tabeli,
• indeksy typu block-range i min–max.

Ogólne informacje o indeksach typu:
• GIN,
• GiST,
• SP-GiST,
• indeksy pełnotekstowe,
• indeksy przestrzenne,
• indeksy bitmapowe występujące w niektórych systemach analitycznych.

5. Indeksy w DuckDB
• automatyczne indeksy min–max, nazywane zonemaps,
• pomijanie bloków danych niespełniających warunku filtra,
• wpływ uporządkowania danych na skuteczność zonemaps,
• indeks ART,
• zastosowanie ART w wyszukiwaniu punktowym i bardzo selektywnych zapytaniach,
• indeksy tworzone dla ograniczeń PRIMARY KEY i UNIQUE,
• różnice między indeksami DuckDB i klasycznymi indeksami PostgreSQL.

6. Dane kolumnowe i większe zbiory
• CSV a Parquet,
• format wierszowy a format kolumnowy,
• kompresja,
• metadane i statystyki w plikach,
• column pruning,
• predicate pushdown,
• partycjonowanie plików,
• analiza wielu plików Parquet,
• znaczenie uporządkowania danych,
• Apache Arrow jako kolumnowy format wymiany danych w pamięci,
• przetwarzanie danych partiami,
• ograniczanie kopiowania i konwersji danych,
• analiza większych danych na pojedynczej maszynie a przetwarzanie rozproszone.

Uwaga: w tym kursie pojęcie „big data” oznacza przede wszystkim świadomą pracę z dużymi zbiorami na pojedynczej maszynie: ograniczanie zakresu odczytu, wykorzystywanie formatów kolumnowych, przetwarzanie wielu plików oraz unikanie ładowania wszystkich danych do Pandas. Systemy klastrowe, takie jak Spark, będą jedynie krótko wskazane jako kolejny etap nauki.

MODUŁ 6. SQL Z POZIOMU PYTHONA I PROJEKT ANALITYCZNY

Cele modułu:
• zbudowanie kompletnego procesu analitycznego,
• bezpieczne przekazywanie parametrów do zapytań,
• zarządzanie połączeniami i transakcjami,
• wymiana danych między SQL, Pandas i Apache Arrow,
• wybór odpowiedniej architektury bazy danych dla konkretnego zastosowania.

Zakres tematyczny:
1. Praca z połączeniem bazodanowym
• utworzenie połączenia,
• baza w pamięci i baza zapisana w pliku,
• cykl życia połączenia,
• wykonywanie zapytań,
• pobieranie wyników,
• obsługa błędów,
• używanie menedżera kontekstu,
• podstawowe założenia standardu Python DB-API,
• różnica między połączeniem do DuckDB i zdalnym połączeniem do PostgreSQL.

2. Parametry i bezpieczeństwo
• przekazywanie parametrów oddzielnie od tekstu zapytania,
• unikanie składania SQL za pomocą konkatenacji tekstów,
• pojęcie SQL injection,
• hasła i dane dostępowe,
• zmienne środowiskowe,
• podstawowe zasady zarządzania sekretami,
• różnica między nazwami obiektów SQL a wartościami przekazywanymi jako parametry.

3. Transakcje
• pojęcie transakcji,
• BEGIN, COMMIT i ROLLBACK,
• atomowość operacji,
• podstawowe znaczenie ACID,
• sytuacje wymagające transakcji,
• różnice między analizą tylko do odczytu a modyfikowaniem danych,
• podstawowe pojęcie współbieżności,
• ograniczenia współbieżnego zapisu w bazach in-process,
• wieloużytkownikowy charakter baz klient–serwer.
W trybie in-process DuckDB koncentruje współbieżny odczyt i zapis w ramach jednego procesu, podczas gdy PostgreSQL został zaprojektowany jako serwer obsługujący połączenia wielu klientów. Jest to jedna z głównych przesłanek przy wyborze silnika dla aplikacji współdzielonych przez wielu użytkowników.

4. Wymiana danych z Pythonem
• zapytanie SQL na Pandas DataFrame,
• pobranie wyniku jako DataFrame,
• pobranie wyniku jako tabela Apache Arrow,
• pobieranie wyników partiami,
• konwersja do tablic NumPy,
• zapis wyniku do CSV lub Parquet,
• pozostawienie dużych transformacji po stronie silnika SQL,
• przekazywanie do Pandas wyłącznie wyniku potrzebnego do dalszej analizy lub wizualizacji.

5. Wybór silnika
DuckDB jako baza danych do
• lokalnej analizy danych,
• notebooków i skryptów analitycznych,
• przetwarzania plików CSV i Parquet,
• aplikacji, w których baza może działać w tym samym procesie co kod analityczny.
PostgreSQL jako baza danych do:
• aplikacji obsługujących wielu użytkowników,
• centralnego przechowywania danych,
• pracy przez połączenie sieciowe,
• systemów wymagających rozbudowanego zarządzania użytkownikami i uprawnieniami,
• aplikacji wykonujących wiele jednoczesnych transakcji,
• usług i aplikacji działających niezależnie od procesu analitycznego.

6. Projekt końcowy