Kurs SQL – i co dalej? CTE

Zrobiłaś/eś już kurs SQL, znasz podstawowe zagadnienia, ale co dalej?

Tak to już bywa, że teoria teorią, a dopiero praktyka pokazuje z czym tak naprawdę musimy się mierzyć w pracy. Niestety większość kursów uczy tylko i wyłącznie składni, a nie głównych problemów dotyczących pracy z danymi oraz bazami. Ba! większość kursów nie wspomina nawet o CTE (ang. common table expressions), które przecież są częścią składową SQL, i do tego dość ważną. Interesujące, że wiedzy na ten temat brakuje nie tylko w kursach, ale CTE są ogólnie dość mało znane – często nawet ludzie pracujący już jako na SQL nie mają o nich pojęcia lub ich nie używają.

Zatem mówimy tutaj o mało znanym, ale za to szalenie istotnym aspekcie SQL. Naprawdę istotnym – nie przypominam sobie, aby napisała choć jedną query bez CTE odkąd zaczęłam pracę jako analityk danych.

Na czym polega problem SQL?

Na tym, na czym polega też jego siła – SQL jest tak prosty, że często ta prostota nas zbyt ogranicza.

Za pomocą SELECT statement możemy określić co wyciągamy i z jakiej tabeli, ewentualnie możemy to zagregować i nałożyć na to filtry – ilość operacji, które możemy wykonać nie pozostawia (delikatnie mówiąc) szerokiego wachlarzu możliwości.

Czego na przykład nie da się zrobić w jednym SELECT? Agregacji na agregacji.

Załóżmy, że chcemy sprawdzić ile średnio w każdym miesiącu kupowano produktów w transakcjach. Aby tego dokonać, musimy zagregować dane dwa razy:

1. Policzyć ilość produktów na każdą transkację:

SELECT transaction_id
     , count(product_id) AS count_products
FROM transactions
GROUP BY transaction_id

2. Następnie wziąć count_products z poprzednich obliczeń (załóżmy, że zapisaliśmy tę kolumnę w tabeli table_with_count_products) i na jej podstawie wyliczyć średnią na miesiąc:

SELECT month
     , avg(count_products) AS average_product_count
FROM table_with_count_products
GROUP BY month

SQL uniemożliwia nam wrzucenie tego do jednego SELECTA – nie możemy zrobić agregacji na agregacji w stylu: SELECT avg(count product_id). Jedyne opcje jak możemy rozwiązać ten problem to albo zapisując wynik z pierwszej query jako nową tabelę (co w sumie zrobiłam powyżej), albo używając rozwiązania, które jest proponowane przez większość kursów online – stosując SUBQUERY / SUBSELECT / nested query (zwał jak zwał).

Subquery

Rozwiązanie z użyciem subquery polega na zagnieżdżeniu jednej query w drugą:

1) bierzemy query nr 1

2) wsadzamy ją do SUBQUERY

3) piszemy na tym SUBQUERY kolejnego SELECT.

W kodzie wygląda to tak:

SELECT month
     , average(count_products) AS average_product_count
FROM
    (SELECT transaction_id
    	  , month –- miesiąc obecny, aby go użyć w zewnętrznej query
    	  , count(product_id) AS count_products
    FROM transactions
    GROUP BY transaction_id, month) AS table_with_count_products
GROUP BY month

Problem rozwiązany, można się rozejść.

A tak szczerze, to istnieją 2 problemy związane z używaniem SUBSELECTów i o ile jeszcze wyżej przedstawiona query może nie do końca to obrazować, to postaramy się je zrozumieć używając trochę wyobraźni.

1. Przedstawiony wyżej przykład wymaga użycia tylko 2 SELECTów. Jest to jednak typowy przykład akademicki (przepraszam!) – tak banalny, że niespotykany w prawdziwym życiu. W życiu zawodowym spotkacie się z dużo bardziej skomplikowanymi problemami i aby je rozwiązać musimy napisać dużo bardziej skomplikowane queries, np. musimy użyć aż 4 SELECTów i jeszcze niektóre z nich dodatkowo ze sobą zJOINować. Gdybyśmy próbowali napisać to przy użyciu subqueries, to zrobiłaby się z tego niesamowicie skomplikowana konstrukcja, a jest nawet bardzo prawdopodobne, że koniec końców ograniczenia SQL nie pozwoliłyby nam tego ze sobą zJOINować
2. Logika pisania SUBQUERIES powoduje, że obliczenia zaczynają się gdzieś w środku kodu w jakimś zagnieżdżeniu. Zresztą widać to już na naszym przykładzie – kod zaczyna się od liczenia średniej miesięcznej, która jest tak naprawdę drugim etapem obliczeń, więc powinna być przedstawiona jako druga w kolejności, a nie pierwsza. Jeżeli próbujemy czytać czyjś kod zawierający SUBQUERY od początku do końca, to musimy zazwyczaj przerwać nasz tok myślowy w połowie kodu, tam gdzie zaczyna się subquery i zresetować myśli na odrębnego SELECTa. Przypominam – nasz przykład jest relatywnie prosty, więc wyobraźcie sobie, co się zaczyna dziać przy większych konstrukcjach. Czytanie takiego SQL jest trudne, męczące i bez sensu.

Te 2 powyższe punkty powodują, że SUBQUERY jest po prostu złą praktyką.

Hmmm, gdyby tylko istniało inne rozwiązanie, aby można było pisać SQL sekwencyjnie….

Ok, kiepskie żarty na bok, przejdźmy do meritum.

CTE

CTE (ang. common table expressions) to składnia umożliwiająca sekwencyjne pisanie SQL. Możemy za jej pomocą zrobić szereg zależnych lub niezależnych od siebie transformacji danych w jednym query. Działa on trochę jak robienie pośrednich, tymczasowych tabel na potrzebę obliczeń do jednego zapytania. W sumie działa on tak samo jak SUBQUERIES, ale pozbywa się tych wszystkich upierdliwości o których wspominałam.

Syntaks CTE jest trywialny – z pierwszym CTE używamy WITH nazwa_cte AS, a przy kolejnych , nazwa_cte AS.

WITH cte1_name AS (SELECT * FROM table1)
   , cte2_name AS (SELECT * FROM table2)
   , cte3_name AS (SELECT * FROM table3)
SELECT * FROM cte3_name

Ostatni SELECT jest już zwykłym SELECTem – bez przecinka, nazwy CTE i nawiasów ().

Spróbujmy przepisać nasz wcześniejszy przykład na CTE. Najpierw liczymy ilość produktów na transakcję, a później obliczamy z tego średnią miesięczną:

WITH table_with_count_products AS (
  SELECT transaction_id
        , month
        , count(product_id) AS count_products
  FROM transactions
  GROUP BY transaction_id, month 
)
SELECT month
       , avg(count_products) AS average_product_count
FROM table_with_count_products
GROUP BY month

Czyli robimy praktycznie to samo co zrobiliśmy z użyciem SUBQUERY, tym razem jednak kod jest napisany w kolejności obliczeń. 

Być może jednak różnica między powyższym kodem napisanym na SUBQUERY i CTE nie robi na Tobie takiego wrażenia, żeby pisać o tym cały artykuł. Piękno CTE wychodzi jednak, gdy query są dłuższe, więc spróbujmy skomplikować trochę ten kod. Załóżmy, że nasz biznesowy stakeholder wymyśla i chciałby, aby jedno query wypluwało poniższe kolumny:

1. miesiąc
2. średnia miesięczna liczby produktów na transakcję
3. średnia miesięczna produktów z kategorii elektronika na transakcję
4. średnia miesięczna stosunku produktów z kategorii elektronika (punkt 3) na liczbę produktów (punkt 2) na transakcję (czyli stosunek obliczymy jako punkt 3 podzielony przez punkt 2 dla każdej transakcji)
5. liczba dni w miesiącu

Oto rozwiązanie przy użyciu CTE:

WITH all_products AS ( -- count all products per transaction
    SELECT transaction_id
          , month
          , count(product_id) AS count_all_products
    FROM transactions
    GROUP BY transaction_id, month
)
, electronic_products AS ( -- count electronic products per transaction
    SELECT t.transaction_id
          , count(t.product_id) AS count_electronic_products
    FROM transactions t 
    JOIN category c ON t.category_id = c.category_id
    WHERE c.category_name = 'ELECTRONICS'
    GROUP BY t.transaction_id
)
, ratio_electronic_to_all AS ( -- calculate ratio of electronic to all products per transaction
    SELECT ap.transaction_id
          , NVL(ep.count_electronic_products, 0) / ap.count_all_products AS ratio
          /* NVL used as if there was no electronic product.
             If ep.count_electronic_products is NULL, it will be replaced with 0 */
    FROM all_products ap
    LEFT JOIN electronic_products ep ON ap.transaction_id = ep.transaction_id
)
SELECT -- at the end calculate averages per month, add number of days
       ap.month
      , avg(ap.count_all_products)        AS average_product_count
      , avg(ep.count_electronic_products) AS average_eletronic_product_count
      , avg(reta.ratio)                   AS average_ratio_electronic_to_al
      , count(c.date)                     AS count_days
FROM all_products ap
LEFT JOIN electronic_products ep       ON ap.transaction_id = ep.transaction_id
LEFT JOIN ratio_electronic_to_all reta ON ap.transaction_id = reta.transaction_id
JOIN calendar c                        ON ap.month = c.month
GROUP BY ap.month

Najważniejsza informacja do zrozumienia to fakt, że CTE zachowuje się jak tabela w DB – w każdym kolejnym CTE możemy się do niego odnieść dokładnie w ten sam sposób jak odnosimy się do tabeli – używając nazwy CTE w części FROM.

Zauważ, że w pierwszym (all_products) i drugim (electronic_products) CTE odnoszę się tylko do nazwy tabeli istniejącej w DB (transactions) i wykonuję niezależne obliczenia. Za to w trzecim CTE (ratio_electronic_to_all) odnoszę się już do wcześniejszych obliczeń poprzez wywołanie w FROM nazw CTE (all_products i electronic_products).

W ostatnim SELECTcie nie dość, że odwołuję się do wszystkich CTEs (chociaż mogłabym jakiś pominąć gdybym chciała), to także odwołuję się do tabeli calendar, która jest zwykłą tabelą w bazie danych.

Czyż ta query nie jest piękna? Nie chcę nawet myśleć, jak by ta logika miała wyglądać z użyciem SUBQUERIES. Kod napisany za pomocą CTE ilustruje nam logikę obliczeń jak na tacy – wiemy po kolei co się działo, które obliczenia zależą od siebie, a które są od siebie odrębne. Możemy łatwo przeczytać kod od początku do końca, według wykonywanych kalkulacji i tak jak jest przetwarzany przez bazę danych, a nie musimy niepotrzebnie resetować naszych myśli i gubić tok myślowy przy każdym rozpoczęciu kolejnej SUBQUERY.

Nasz szalony stakeholder może sobie zażyczyć dodatkowych 5 kolumn – no problemo – możemy je łatwo dodać do powyższej query, nie ruszając istniejących już obliczeń.

Podsumowując

• CTE to składnia umożliwiająca nam sekwencyjne obliczenia w SQL
• sekwencyjność moglibyśmy także uzyskać stosując SUBQUERY, ale jest to zła praktyka. CTEs są o wiele bardziej czytelne i prostsze w obsłudze
• każde CTE działa jak tymczasowa tabela – możemy się później do niego odnieść w kodzie w ten sam sposób jak do rzeczywistej tabeli w DB – używając nazwy CTE w FROM

Newsletter

Btw, rozpoczęłam całkiem niedawno nowy newsletter! Jeżeli nie chcesz przegapić kolejnych treści, zapraszam do zapisu.

Zero spamu i bullshitu – same soczyste materiały!

Pamiętajcie, że po zapisaniu się do newslettera musicie potwierdzić subskrypcję poprzez kliknięcie w link aktywacyjny, który otrzymacie w pierwszym mailu. Niestety, jeżeli tego nie zrobicie, nie będę mogła do was napisać! Jeżeli nie otrzymaliście maila to proszę sprawdźcie skrzynkę ze spamem.