SQL Interview Prep · Lekcja

Pisanie pierwszego CTE

Podstawowa składnia WITH i sytuacje, w których CTE poprawia czytelność w porównaniu z podzapytaniem

Pisanie pierwszego CTE to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 1 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Interview Prep, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Czym właściwie jest CTE

Wspólne wyrażenie tabelowe (CTE) to nazwany, tymczasowy zbiór wyników zdefiniowany za pomocą słowa kluczowego WITH, który istnieje tylko przez czas wykonywania pojedynczego zapytania. Rekruterzy lubią CTE, ponieważ pokazują, czy potrafi się jasno strukturyzować logikę.

CTE można postrzegać jako nadanie nazwy podzapytaniu, aby można było odwoływać się do niego jak do tabeli w następującej po nim instrukcji głównej. Nie tworzy ono trwałego obiektu i znika natychmiast po zakończeniu zapytania.

Podstawowa składnia WITH

Każde CTE zaczyna się od WITH, nazwy, słowa kluczowego AS i zapytania ujętego w nawiasy. Po zamykającym nawiasie należy napisać zwykłą instrukcję, która używa CTE za pomocą jego nazwy.

WITH cte_name AS ( ... ) definiuje blok.
Zapytanie znajdujące się bezpośrednio po nawiasie jest zapytaniem głównym.
Nazwa CTE zachowuje się jak nazwa tabeli, z której można wykonać SELECT.

WITH recent_orders AS (
    SELECT *
    FROM orders
    WHERE order_date >= '2024-01-01'
)
SELECT *
FROM recent_orders;

Dlaczego po prostu nie użyć podzapytania

Tę samą logikę można zapisać jako podzapytanie wbudowane w klauzuli FROM. Dlaczego więc rekruterzy pytają o CTE?

Czytelność: nazwany krok czyta się od góry do dołu jak przepis.
Wielokrotne użycie: można odwołać się do tego samego CTE wiele razy zamiast powtarzać podzapytanie.
Łatwiejsze debugowanie: można wykonać SELECT tylko na CTE, aby je sprawdzić.

Właściwa odpowiedź podczas rozmowy rekrutacyjnej zwykle brzmi: należy używać CTE, gdy ułatwia ono czytanie i utrzymywanie zapytania.

Przykład z rozwiązaniem: filtrowanie, a następnie agregowanie

Załóżmy, że zadanie brzmi: znajdź całkowity przychód z zamówień złożonych w tym roku. CTE pozwala wydzielić etap filtrowania, a następnie wykonać agregację na nazwanym wyniku.

Zapytanie główne traktuje recent_orders tak, jakby była to rzeczywista tabela, dzięki czemu agregacja pozostaje przejrzysta i oczywista.

WITH recent_orders AS (
    SELECT amount
    FROM orders
    WHERE order_date >= '2024-01-01'
)
SELECT SUM(amount) AS total_revenue
FROM recent_orders;

Nadawanie nazw kolumnom wynikowym

Nazwy kolumn udostępnianych przez CTE można zmienić, wpisując je bezpośrednio po nazwie CTE. Jest to przydatne, gdy zapytanie wewnętrzne zwraca wyrażenia lub gdy potrzebne są bardziej zrozumiałe nazwy w dalszej części zapytania.

Jeśli zostanie podana lista kolumn, musi odpowiadać liczbie kolumn zwracanych przez zapytanie wewnętrzne. W przeciwnym razie baza danych zgłosi błąd.

WITH revenue (region, total) AS (
    SELECT region, SUM(amount)
    FROM orders
    GROUP BY region
)
SELECT region, total
FROM revenue
ORDER BY total DESC;

CTE to po prostu nazwane zapytanie

Jeden z modeli mentalnych, które robią dobre wrażenie na rekruterach: CTE jest logicznie równoważne podstawieniu jego definicji bezpośrednio w miejscu użycia. Baza danych może zdecydować o włączeniu definicji bezpośrednio do zapytania lub o jej materializacji, ale pod względem semantycznym wynik jest taki sam, jak gdyby podzapytanie zostało wklejone w to miejsce.

Oznacza to, że wszystko, co jest dozwolone w zwykłym SELECT, jest również dozwolone wewnątrz CTE: złączenia, GROUP BY, WHERE, funkcje okna i wiele innych elementów.

Głębszy przykład: CTE ze złączeniem

CTE są szczególnie przydatne, gdy trzeba wcześniej przygotować jedną stronę złączenia. W tym przypadku najpierw tworzymy liczbę zamówień dla każdego klienta, a następnie dołączamy ją z powrotem do tabeli klientów, aby każdy wiersz klienta zawierał jego łączną liczbę zamówień.

Zwróćmy uwagę, że zapytanie główne czyta się niemal jak zdanie: pobierz klientów i połącz ich z liczbą zamówień.

WITH order_counts AS (
    SELECT customer_id, COUNT(*) AS num_orders
    FROM orders
    GROUP BY customer_id
)
SELECT c.name, oc.num_orders
FROM customers c
JOIN order_counts oc
    ON oc.customer_id = c.id;

Zakres CTE w zapytaniu

Blok WITH musi znajdować się przed instrukcją, która z niego korzysta. CTE jest widoczne wyłącznie w pojedynczej instrukcji następującej bezpośrednio po jego definicji.

Nie można odwołać się do CTE w późniejszym, osobnym zapytaniu.
CTE zdefiniowanego dla SELECT nie można ponownie użyć w innym SELECT uruchomionym później.
Zakres kończy się wraz ze średnikiem zamykającym instrukcję.

CTE współpracują z INSERT, UPDATE i DELETE

Częste pytanie dodatkowe: CTE nie są ograniczone do SELECT. W większości nowoczesnych baz danych można dołączyć klauzulę WITH również do instrukcji modyfikujących dane.

Pozwala to obliczyć zbiór wierszy raz, a następnie wykonać na nim operację, co jest znacznie czytelniejsze niż zagnieżdżone podzapytanie w klauzuli WHERE.

WITH stale AS (
    SELECT id
    FROM sessions
    WHERE last_seen < NOW() - INTERVAL '30 days'
)
DELETE FROM sessions
WHERE id IN (SELECT id FROM stale);

Częste błędy początkujących

Rekruterzy zwracają uwagę na następujące błędy:

Brak zapytania głównego po CTE; sam blok WITH nie jest kompletną instrukcją.
Umieszczenie średnika między CTE a zapytaniem głównym.
Oczekiwanie, że CTE będzie zachowywać się jak trwały obiekt w wielu instrukcjach.
Niezgodność opcjonalnej listy nazw kolumn z kolumnami zapytania wewnętrznego.

Jak mówić o CTE podczas rozmowy rekrutacyjnej

Gdy pojawi się prośba o uporządkowanie nieczytelnego podzapytania, należy opisać tok rozumowania: Wydzielę to podzapytanie filtrujące do CTE o nazwie recent_orders, aby agregacja była czytelna.

Pokazanie, że stosuje się CTE dla czytelności i wielokrotnego użycia, a nie bezrefleksyjnie, sygnalizuje dojrzałość na poziomie średniozaawansowanym. Warto wspomnieć, że CTE samo w sobie nie przyspiesza zapytania; jego główną zaletą jest czytelność.

Szybkie sprawdzenie

Sprawdź swoją znajomość podstawowej składni CTE i zakresu ich widoczności.

Podsumowanie: pierwsze CTE

Poznali Państwo sposób, w jaki CTE używa konstrukcji WITH name AS ( ... ), aby nadać nazwę tymczasowemu zbiorowi wyników, a następnie odwołuje się do niego jak do tabeli w kolejnym zapytaniu.

CTE poprawiają czytelność, możliwość ponownego użycia i łatwość debugowania w porównaniu z podzapytaniami wbudowanymi.
Ich zakres ogranicza się do jednego zapytania — później znikają.
Działają z SELECT oraz z INSERT/UPDATE/DELETE.
Same w sobie nie zwiększają wydajności.

Następnie: łączenie kilku CTE w potok.

Często zadawane pytania

Czy lekcja „Pisanie pierwszego CTE” jest bezpłatna?

Tak — pełny tekst „Pisanie pierwszego CTE” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Interview Prep, przejdź na CoddyKit PRO. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Co nauczysz się w „Pisanie pierwszego CTE”?

Podstawowa składnia WITH i sytuacje, w których CTE poprawia czytelność w porównaniu z podzapytaniem Ćwiczysz SQL Interview Prep z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć SQL Interview Prep?

Nie wymagamy żadnego doświadczenia. SQL Interview Prep w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 1 z 4.

Ile czasu zajmuje lekcja „Pisanie pierwszego CTE”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji SQL Interview Prep?

Tak. Każda lekcja SQL Interview Prep zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

Pisanie pierwszego CTE
Łączenie wielu CTE
CTE a podzapytanie i tabela tymczasowa
Refaktoryzacja zagnieżdżonych zapytań do CTE

← Powrót do SQL Interview Prep