Lesson 5

Lekcja 5: CTE — Spraw, Żeby Twoje Zapytania Były Tak Zorganizowane, Jak Angielskie Popołudniowe Picie Herbaty!

Witaj znowu, miłośniku danych! Dzisiaj zagłębimy się w świat Common Table Expressions (CTE) — arystokratyczny sposób, żeby Twoje skomplikowane zapytania SQL wyglądały schludnie i elegancko. Wyobraź sobie CTE jako kamerdynera dla SQL: przygotowuje wszystko z wyprzedzeniem, sprawia, że dane płyną gładko, i robi to z klasą prawdziwego dżentelmena.

Co to jest CTE i po co mi to?

CTE to takie tymczasowe zestawy wyników z nazwą, które istnieją tylko podczas wykonywania Twojego zapytania. Wyobraź sobie, że zaprosiłeś kamerdynera na herbatę. Wszystko pięknie poukłada (Twoje dane), poda do stołu (Twój CTE), a potem cichutko zniknie, kiedy skończysz. A Ty cieszysz się herbatą, nie ruszając nawet małego palca!

CTE są idealne do:

Podzielenia skomplikowanych zapytań na bardziej czytelne części.
Wielokrotnego użytku tych samych zapytań bez powtarzania.
Nadania Twojemu SQL-owi elegancji godnej wieczornej filiżanki Earl Grey!

Tworzenie CTE: Użycie Klauzuli `WITH`

CTE definiuje się za pomocą słowa kluczowego WITH, po którym podajesz nazwę (tak jakbyś nadawał tytuł swojemu kamerdynerowi). Potem określasz zapytanie, które tworzy CTE, i używasz go, jakby to była osobna tabela:

WITH misje_rycerzy AS (
  SELECT imie, krolestwo, COUNT(misja) AS misje_ogolem
  FROM misje
  GROUP BY imie, krolestwo
)
SELECT * FROM misje_rycerzy;

W tym przykładzie misje_rycerzy zlicza liczbę misji dla każdego rycerza i królestwa. Gdy już jest zdefiniowane, możesz go użyć w swoim głównym zapytaniu, co sprawia, że całość wygląda znacznie bardziej schludnie.

Wiele CTE: Jak Zaprosić Więcej Kamerdynerów do Pomocy

Można zdefiniować wiele CTE, łącząc je przecinkami. Wyobraź sobie, że chcesz zobaczyć, którzy rycerze są weteranami (ponad 3 misje) i które królestwa prowadzą w odwadze:

WITH misje_rycerzy AS (
  SELECT imie, krolestwo, COUNT(misja) AS misje_ogolem
  FROM misje
  GROUP BY imie, krolestwo
),
rycerze_weterani AS (
  SELECT imie, krolestwo
  FROM misje_rycerzy
  WHERE misje_ogolem > 3
)
SELECT rycerze_weterani.imie, rycerze_weterani.krolestwo
FROM rycerze_weterani
JOIN misje_rycerzy ON rycerze_weterani.imie = misje_rycerzy.imie;

Tutaj misje_rycerzy to nasz pierwszy CTE, a rycerze_weterani jest zbudowany na jego podstawie. To jak zaprosić drugiego kamerdynera, żeby upewnić się, że wszystko działa jak należy!

Rekursywne CTE: Kiedy Jeden Kamerdyner To Za Mało

CTE stają się jeszcze bardziej użyteczne, kiedy używasz ich rekurencyjnie. Wyobraź sobie, że chcesz stworzyć drzewo genealogiczne rycerzy: Sir Artur szkolił Sir Lancelota, który z kolei trenował Sir Galahada, i tak dalej. Dzięki rekurencyjnemu CTE możesz przejść przez całe drzewo od góry do dołu:

WITH RECURSIVE genealogia_rycerzy AS (
  SELECT imie, mentor, 1 AS pokolenie
  FROM rycerze
  WHERE mentor IS NULL
  
  UNION ALL
  
  SELECT r.imie, r.mentor, g.pokolenie + 1
  FROM rycerze r
  JOIN genealogia_rycerzy g ON r.mentor = g.imie
)
SELECT * FROM genealogia_rycerzy;

Ten zapytanie buduje drzewo genealogiczne, zaczynając od najwyższego rycerza (który nie ma mentora) i przechodząc przez pokolenia. To jak mieć armię kamerdynerów, gdzie każdy dba o swoją gałąź rodziny!

Dlaczego CTE Są Lepsze Niż Podzapytania?

Podzapytania bywają przydatne, ale jeśli używasz ich zbyt często, szybko robi się bałagan. CTE z kolei sprawiają, że Twój SQL wygląda schludnie i przejrzyście:

Czytelność: CTE rozbijają skomplikowane zapytania na łatwe do zrozumienia części.
Wielokrotność Użytku: Definiujesz raz, używasz ile razy chcesz.
Łatwe do Debugowania: Łatwiej znaleźć, gdzie coś poszło nie tak.

Więc następnym razem, gdy będziesz walczył z zapytaniem-potworem, wezwij kamerdynera CTE, żeby ogarnął bałagan w Twoim SQL-u.

Złóżmy Wszystko Razem: Wielki Bal CTE

Oto końcowy przykład, który pokaże, jak to wszystko działa:

Chcesz znaleźć wszystkich rycerzy, którzy wykonali więcej niż 3 misje, i dowiedzieć się, które królestwo ma największy odsetek takich weteranów. Rozbijmy to na kilka eleganckich CTE:

WITH misje_rycerzy AS (
  SELECT imie, krolestwo, COUNT(misja) AS misje_ogolem
  FROM misje
  GROUP BY imie, krolestwo
),
rycerze_weterani AS (
  SELECT imie, krolestwo
  FROM misje_rycerzy
  WHERE misje_ogolem > 3
),
odwaga_krolestwa AS (
  SELECT krolestwo, COUNT(*) AS liczba_weteranow, 
         (COUNT(*)::float / (SELECT COUNT(*) FROM rycerze WHERE rycerze.krolestwo = odwaga_krolestwa.krolestwo)) * 100 AS procent_weteranow
  FROM rycerze_weterani
  GROUP BY krolestwo
)
SELECT krolestwo, procent_weteranow
FROM odwaga_krolestwa
ORDER BY procent_weteranow DESC;

Ta elegancka struktura pokazuje, jak CTE mogą sprawić, że nawet najbardziej złożone zapytania stają się łatwe do ogarnięcia.

Czego Się Dzisiaj Nauczyliśmy?

Dziś zagłębiliśmy się w świat CTE:

Podstawowe CTE: Organizują zapytania na czytelne części.
Wiele CTE: Można je łączyć, żeby poprawić przejrzystość.
Rekursywne CTE: Idealne do pracy z hierarchicznymi danymi.

Co Dalej?

W następnym odcinku odkryjemy tajemnice Window Functions — narzędzi, które wyniosą Twoją analizę danych na poziom królewski!

Gotowy dodać elegancji do swoich zapytań z pomocą CTE? Załóż najlepszy SQL-garnitur i szykuj się na kolejny rozdział!