CTE w SQL: WITH i raport miesięczny krok po kroku

Zapytanie rośnie. Najpierw liczysz zamówienia, potem porównujesz sposoby dostawy, a na końcu potrzebujesz najlepszego miesiąca. Możesz zagnieżdżać kolejne SELECT-y, ale w pewnym momencie trudno odtworzyć, co jest wejściem do którego obliczenia. Wtedy chętnie nadaję etapom nazwy. Do tego właśnie przydaje mi się CTE.

Common Table Expression brzmi poważniej niż sama konstrukcja. Po słowie WITH definiujesz nazwane wyrażenie, a potem odwołujesz się do niego w tym samym poleceniu. Pokażę Ci tę zasadę na raporcie miesięcznym. Będzie komplet danych, wynik do sprawdzenia i miesiąc bez zamówień, który łatwo zgubić przy niewłaściwym źródle kalendarza.

Przykłady są syntetyczne i przeznaczone do SQLite. Uruchamiaj bloki kolejno w jednym pustym połączeniu. Starszy materiał korzystał z MySQL i DATE_FORMAT; tutaj używam jednolicie zapisanych dat tekstowych i funkcji SQLite. Wyniku sprawdzenia tego kodu nie należy traktować jako testu składni MySQL ani innego silnika.

CTE ma nazwę i zakres jednego polecenia

Najprostsza forma to WITH nazwa AS (...) SELECT .... W nawiasie znajduje się zapytanie definiujące wynik, do którego możesz odwołać się po nazwie. Końcowy SELECT korzysta z tej nazwy podobnie jak ze źródła danych. Całość stanowi jedno polecenie zakończone średnikiem, a nie dwa niezależne zapytania.

CTE nie tworzy trwałej tabeli, którą później znajdziesz na liście obiektów bazy. Nazwa jest dostępna w zakresie tego polecenia. Po jego zakończeniu następny samodzielny SELECT nie może po prostu przeczytać wcześniej nazwanej części. Jeśli potrzebujesz wyniku między poleceniami, trzeba rozważyć inny mechanizm, na przykład tabelę tymczasową.

Nie zakładam też, że każde CTE zostaje fizycznie zapisane w pamięci i odczytane dokładnie raz. Dokumentacja SQLite WITH opisuje zwykłe CTE jako konstrukcję działającą podobnie do widoku o zakresie jednego polecenia. Sposób wykonania zależy od planera. Nazwa porządkuje logikę, ale nie jest deklaracją miejsca przechowywania danych.

Dane: zamówienia, braki i miesiąc bez sprzedaży

Każdy wiersz poniżej oznacza jedno zamówienie. ship_mode ma dwie możliwe wartości w naszym ćwiczeniu. amount jest umowną liczbą punktów. Jedno zamówienie nie ma podanej wartości, a inne ma wartość zero. Zachowam tę różnicę w raporcie, zamiast zamieniać wszystkie puste pola na zera.

-- example: fixture
CREATE TEMP TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    ordered_at TEXT,
    ship_mode TEXT,
    amount INTEGER
);
INSERT INTO orders VALUES
    (1, 10, '2026-01-03', 'First Class', 100),
    (2, 10, '2026-01-08', 'First Class',  50),
    (3, 20, '2026-01-11', 'Second Class', 80),
    (4, 20, '2026-02-02', 'Second Class',120),
    (5, 30, '2026-02-09', 'First Class',NULL),
    (6, 30, '2026-03-01', 'First Class',  0);

W styczniu są trzy zamówienia, w lutym dwa, w marcu jedno. Kwiecień nie występuje w danych, ale raport ma obejmować wszystkie cztery miesiące. To osobne wymaganie dotyczące zakresu czasu. Nie da się go odczytać z samych zdarzeń, bo brak zdarzenia nie tworzy wiersza w tabeli zamówień.

Daty mają konsekwentnie format YYYY-MM-DD. Wycięcie pierwszych siedmiu znaków daje tu rok i miesiąc. To jawne uproszczenie ćwiczenia, a nie uniwersalny sposób parsowania dowolnej daty. Przy rzeczywistym źródle sprawdzam typ, strefę czasową i moment przypisania zdarzenia do okresu. Zdarzenie blisko północy może należeć do innego dnia w innej strefie.

Pierwszy etap: przygotuj wspólne pola

Zacznę od nazwania wspólnego przygotowania. CTE prepared wyznacza miesiąc i wybiera pola potrzebne dalej. Końcowy odczyt pokazuje jego wynik, więc mogę ocenić etap samodzielnie. Na tym etapie wciąż mam jeden wiersz na zamówienie; dopiero później zmienię szczegółowość przez grupowanie.

-- example: prepared
WITH prepared AS (
    SELECT
        order_id,
        customer_id,
        SUBSTR(ordered_at, 1, 7) AS order_month,
        ship_mode,
        amount
    FROM orders
)
SELECT order_id, order_month, ship_mode, amount
FROM prepared
ORDER BY order_id;

Oczekuję sześciu wierszy. Pierwsze trzy mają miesiąc 2026-01, dwa następne 2026-02, ostatni 2026-03. Kwota piątego zamówienia pozostaje NULL. Taki odczyt kontrolny pomaga wykryć problem wcześniej, zanim kilka kolejnych agregacji schowa jego źródło pod przekonującymi sumami.

Nazwa prepared jest w tym krótkim przykładzie wystarczająca, ale w większym raporcie wybrałbym nazwę opisującą konkretną transformację. Nie tworzę etapu tylko po to, żeby skrócić kod o kilka linii. Chcę mieć granicę, przy której potrafię powiedzieć: takie są kolumny, taka liczba wierszy i takie znaczenie pojedynczej obserwacji.

Kolejny etap może korzystać z poprzedniego

Teraz agreguję przygotowane dane według miesiąca i sposobu dostawy. monthly odwołuje się do prepared. W liście po WITH kolejne definicje oddzielam przecinkami. Po ostatniej definicji nie ma przecinka ani średnika; od razu pojawia się końcowy odczyt. To częste miejsce pomyłki przy pierwszych próbach.

-- example: monthly
WITH prepared AS (
    SELECT
        customer_id,
        SUBSTR(ordered_at, 1, 7) AS order_month,
        ship_mode,
        amount
    FROM orders
),
monthly AS (
    SELECT
        order_month,
        ship_mode,
        COUNT(*) AS order_count,
        COUNT(DISTINCT customer_id) AS customers,
        COUNT(amount) AS known_amounts,
        SUM(amount) AS total_amount
    FROM prepared
    GROUP BY order_month, ship_mode
)
SELECT *
FROM monthly
ORDER BY order_month, ship_mode;

Po agregacji jeden wiersz oznacza kombinację miesiąca i sposobu dostawy. Styczniowe dwa zamówienia First Class złożył jeden klient. Dlatego liczba zamówień i liczba klientów różnią się mimo pracy na tym samym materiale. Nie dodawałbym automatycznie liczby klientów z kilku grup, bo jedna osoba może pojawić się w więcej niż jednej.

known_amounts pokazuje, ile zamówień ma znaną wartość. W lutowym First Class jest jedno zamówienie i zero znanych wartości. SUM zwraca wtedy NULL. Marcowe zero jest natomiast sumą znanej wartości. Reguły agregatów SQLite wyjaśniają różnicę między liczeniem wierszy, liczeniem niepustych wartości i sumowaniem.

Zbuduj raport z jawnym kalendarzem

Raport ma objąć styczeń, luty, marzec i kwiecień. Definiuję więc listę miesięcy niezależnie od zamówień. W realnym modelu może pochodzić z tabeli kalendarza. W ćwiczeniu cztery wartości mieszczą się bezpośrednio w CTE. Dzięki temu czytelnik widzi pełny zakres bez konieczności pobierania dodatkowej tabeli.

-- example: report
WITH months(order_month) AS (
    VALUES ('2026-01'), ('2026-02'), ('2026-03'), ('2026-04')
),
monthly AS (
    SELECT
        SUBSTR(ordered_at, 1, 7) AS order_month,
        ship_mode,
        COUNT(*) AS order_count,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY SUBSTR(ordered_at, 1, 7), ship_mode
),
first_class AS (
    SELECT * FROM monthly WHERE ship_mode = 'First Class'
),
second_class AS (
    SELECT * FROM monthly WHERE ship_mode = 'Second Class'
)
SELECT
    m.order_month,
    COALESCE(fc.order_count, 0) AS first_orders,
    fc.total_amount AS first_amount,
    COALESCE(sc.order_count, 0) AS second_orders,
    sc.total_amount AS second_amount
FROM months AS m
LEFT JOIN first_class AS fc ON fc.order_month = m.order_month
LEFT JOIN second_class AS sc ON sc.order_month = m.order_month
ORDER BY m.order_month;

Złączenia lewe zachowują miesiące bez dopasowania. COALESCE nadaje zerową liczbę zamówień tam, gdzie nie ma grupy. Kwot nie zastępuję automatycznie: brak grupy i grupa z nieznanymi kwotami wymagają rozróżnienia. W tabeli pomaga liczba zamówień, a przy mieszance znanych i brakujących kwot potrzebna byłaby również informacja o kompletności z poprzedniego etapu.

Przyjrzyj się lutemu i kwietniowi. W lutym pierwsza kategoria ma zamówienie, ale nieznaną kwotę. W kwietniu nie ma zamówień żadnej kategorii. Jeżeli odbiorca chce wyświetlać zero dla pustego miesiąca, można zapisać taką regułę osobno. Nie powinna ona przy okazji przedstawiać nieznanej kwoty istniejącego zamówienia jako znanego zera.

Dlaczego lista miesięcy z orders nie wystarczy

Gdyby months powstało przez wybór różnych miesięcy z orders, kwiecień nigdy nie trafiłby do raportu. Dalszy LEFT JOIN nie potrafi zachować wiersza, którego nie było w lewym źródle. To częsta pomyłka: poprawny rodzaj złączenia nie naprawia niepełnej populacji wejściowej.

Podobny problem występuje przy raportach klientów, produktów i dni pracy. Jeżeli chcesz pokazać również osoby bez zakupów, potrzebujesz listy osób, a nie tylko identyfikatorów występujących w transakcjach. CTE ułatwia nazwanie takiej listy, ale decyzja o jej pochodzeniu nadal należy do autora analizy.

Zakres kalendarza też jest decyzją. Cztery miesiące w przykładzie są jawne i skończone. Nie rozszerzam go automatycznie do największej daty w źródle, bo pojedynczy błędny rok mógłby zmienić raport. W prawdziwym procesie ustalam okres raportowania i sprawdzam zdarzenia poza nim jako osobną kontrolę jakości danych.

Co oznacza największa suma miesięczna

Mogę użyć miesięcznej agregacji do następnego obliczenia. Poniżej wybieram największą znaną sumę dla każdej kategorii. Dwa końcowe CTE agregują bez GROUP BY, więc każde zwraca jeden wiersz. Ich połączenie przez CROSS JOIN daje zatem jeden wiersz z dwiema wartościami.

-- example: maxima
WITH monthly AS (
    SELECT
        SUBSTR(ordered_at, 1, 7) AS order_month,
        ship_mode,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY SUBSTR(ordered_at, 1, 7), ship_mode
),
first_best AS (
    SELECT MAX(total_amount) AS best_amount
    FROM monthly
    WHERE ship_mode = 'First Class'
),
second_best AS (
    SELECT MAX(total_amount) AS best_amount
    FROM monthly
    WHERE ship_mode = 'Second Class'
)
SELECT
    fc.best_amount AS first_best_amount,
    sc.best_amount AS second_best_amount
FROM first_best AS fc
CROSS JOIN second_best AS sc;

Wynik to 150 i 120. To dwie niezależne największe sumy, które mogą pochodzić z różnych miesięcy. Zapytanie nie zwraca nazw tych miesięcy ani nie rozstrzyga remisów. Gdy pytanie dotyczy konkretnego najlepszego miesiąca, trzeba zachować jego identyfikator i określić, czy pokazać wszystkie remisy, czy wybrać jeden według dodatkowej reguły.

Przy braku wartości agregacja MAX zwróci NULL, ale nadal będzie to jeden wiersz. Dlatego ten konkretny CROSS JOIN nie pomnoży raportu. Nie przenoś jednak tej obserwacji na dowolne dwa CTE. Jeżeli jedno zwróci cztery wiersze, a drugie pięć, iloczyn kartezjański da dwadzieścia kombinacji. Zawsze sprawdzam przewidywaną liczność obu stron.

CTE, podzapytanie, widok i tabela tymczasowa

Wiele zwykłych CTE można zapisać jako podzapytanie w FROM. Wybór często dotyczy czytelności i sposobu organizacji logiki. Dla jednego krótkiego przekształcenia podzapytanie bywa wygodne. Przy kilku etapach wolę nazwy, do których mogę odwoływać się podczas rozmowy o raporcie. Nie robię jednak z CTE obowiązkowej dekoracji każdego SELECT-a.

Widok zapisuje definicję zapytania jako obiekt bazy, a tabela tymczasowa przechowuje dane zgodnie z regułami silnika. CTE ma krótszy zakres: jedno polecenie. Te konstrukcje rozwiązują częściowo podobne potrzeby, ale różnią się cyklem życia, możliwością ponownego użycia i sposobem wykonania. Wybór zaczynam od tego, jak długo i przez kogo wynik ma być używany.

Jeżeli kilka raportów korzysta z tej samej definicji biznesowej, kopiowanie długiego CTE między plikami może prowadzić do rozjazdu wersji. Warto wtedy rozważyć wspólną warstwę modelu. Sama możliwość wklejenia kodu nie jest powodem, żeby utrzymywać pięć niezależnych definicji tego samego wskaźnika. Czytelność pojedynczego pliku trzeba pogodzić ze spójnością całego procesu.

Czy WITH zmienia plan wykonania

Czytelność i wydajność oceniam osobno. Silnik może rozwinąć zwykłe CTE, użyć wyniku pośredniego albo zastosować inne przekształcenia zgodne z regułami danego dialektu. Nie wnioskuję z kolejności nazw, że baza fizycznie wykona wszystko od góry do dołu i zapisze każdy wynik na później.

Kiedy zapytanie jest wolne, oglądam plan i mierzę rzeczywiste wykonanie na reprezentatywnych danych. Warto porównać liczbę odczytanych wierszy, warunki filtrowania i złączenia. Mała próbka z tego artykułu służy do kontroli poprawności. Nie nadaje się do ogłaszania, że jedna forma SQL jest szybsza w całym systemie.

Rekurencyjne CTE stanowią osobny temat. Pozwalają opisywać między innymi hierarchie, ale wymagają kontroli zakończenia i ewentualnych cykli. Nasz raport używa wyłącznie zwykłych CTE. Nie potrzebujemy rekurencji tylko dlatego, że etapy korzystają z wcześniejszych nazw. Zależność między etapami i rekurencja to różne konstrukcje.

Jak sprawdzać raport etapami

Najpierw uruchamiam końcowy odczyt z pierwszego CTE i porównuję go ze źródłem. Potem sprawdzam agregację: ile jest grup, co oznacza wiersz i czy suma zgadza się z ręcznym rachunkiem. Dopiero po tej kontroli dokładam złączenia. Dzięki temu błąd ma niewiele miejsc, w których mógł powstać.

Przy każdej granicy zapisuję oczekiwaną szczegółowość. monthly ma jeden wiersz na miesiąc i kategorię. Po ograniczeniu do First Class zostaje najwyżej jeden wiersz na miesiąc. To właśnie pozwala bezpiecznie połączyć wynik z kalendarzem. Jeśli etap pośredni nagle zachowa identyfikator zamówienia, liczność może się zmienić i końcowy raport zacznie powielać okresy.

Sprawdzam również pusty zbiór. Bez zamówień raport oparty na kalendarzu powinien nadal pokazać cztery miesiące, z zerową liczbą zamówień i brakującymi kwotami. Agregacja maksymalnych sum powinna zwrócić jeden wiersz z dwoma brakami. To różne oczekiwane kształty wyniku; nie wystarczy ogólnie stwierdzić, że program „obsługuje puste dane”.

Typowe błędy podczas rozbudowy WITH

Jeżeli silnik nie znajduje nazwy CTE, sprawdzam najpierw zakres uruchomionego tekstu. Samo zaznaczenie końcowego SELECT-a w edytorze pomija potrzebne definicje. Podobny efekt daje średnik postawiony po ostatnim nawiasie. Zapytanie czytające nazwany etap musi należeć do tego samego polecenia co jego definicja. To prosta kontrola, którą warto wykonać przed szukaniem problemu w bazie.

Do diagnostyki zmieniam tylko końcowy odczyt. Zamiast raportu wybieram dane z monthly, zachowując wszystkie potrzebne wcześniejsze definicje. Dzięki temu nie kopiuję fragmentów do kilku okien i nie ryzykuję sprawdzania innej wersji kodu niż ta, która buduje raport. Po kontroli przywracam właściwy końcowy SELECT i ponownie sprawdzam całość.

Jeśli liczby nagle rosną, sprawdzam unikalność klucza po każdej stronie złączenia. W naszym przykładzie tym kluczem jest miesiąc po ograniczeniu kategorii. Dwa wiersze dla stycznia po jednej stronie i trzy po drugiej mogą utworzyć sześć połączeń. Dopisanie DISTINCT do końcowego wyniku nie jest wyjaśnieniem, skąd pojawiło się powielenie. Wróciłbym do etapu, który miał zapewniać jeden wiersz na miesiąc.

Innym sygnałem jest poprawna suma przy błędnej liczbie klientów. Klient 10 złożył w styczniu dwa zamówienia, więc policzenie wierszy daje dwa, a policzenie różnych identyfikatorów daje jeden. Żadna z tych liczb nie jest uniwersalnie lepsza. Trzeba ustalić, czy nazwa kolumny i pytanie odbiorcy mówią o zakupach, osobach czy jeszcze innym poziomie obserwacji.

Warto również odróżniać kolejność definicji od kolejności prezentacji. Nazwanie etapu first_class przed second_class nie ustala kolejności miesięcy w wyniku. Dlatego końcowy raport ma własne ORDER BY. Zależności obliczeń, przewidywany plan silnika i układ tabeli dla czytelnika to trzy rzeczy, które sprawdzam osobno. Takie rozdzielenie ułatwia diagnozę bez zgadywania, co SQL powinien był zrobić.

Ćwiczenie i starsze materiały

Dopisz zamówienie Second Class z marca o wartości 40. Zanim uruchomisz raport, przewidź zmianę: marcowa liczba zamówień tej kategorii powinna wynieść jeden, a suma 40. Największa suma Second Class nadal wynosi 120. Kwiecień powinien pozostać w raporcie bez zmian. To sprawdza jednocześnie agregację, kalendarz i niezależność obliczenia maksimum.

Następnie dodaj do lutowego First Class znane 25 punktów. Suma stanie się 25, chociaż jedna kwota nadal jest nieznana. Wróć do known_amounts i sprawdź kompletność: jedno znane pole przy dwóch zamówieniach. Ten przypadek pokazuje, dlaczego sama suma nie wystarcza do oceny jakości raportu. Potrzebujesz także informacji, z czego została policzona.

Poniższy film jest polskim materiałem towarzyszącym pierwotnemu artykułowi. Zachowuję go jako dodatkowe wyjaśnienie CTE; przykłady SQLite z tego tekstu mają własny, kompletny zestaw danych. Nie trzeba pobierać dawnej bazy, żeby je wykonać.

Historyczne pliki to Tabela orders w formacie CSV i pierwotny kod MySQL na GitHubie. Traktuj je jako odrębny wariant ćwiczenia. Podstawy składni przypomina SELECT, a sposób porządkowania większej kwerendy omawiam w poradniku formatowania SQL.