0Pricing
SQL Interview Prep · Lekcja

CTE a podzapytanie i tabela tymczasowa

Kompromisy związane z materializacją, ponownym użyciem i działaniem optymalizatora

CTE a podzapytanie i tabela tymczasowa to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 3 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.

Trzy sposoby organizowania logiki

Gdy zapytanie wymaga wyniku pośredniego, mają Państwo do dyspozycji trzy popularne narzędzia: podzapytanie, CTE i tabelę tymczasową. Osoby prowadzące rozmowy rekrutacyjne proszą o ich porównanie, ponieważ wybór pokazuje, czy rozumieją Państwo materializację i działanie optymalizatora.

Ta lekcja przedstawia schemat podejmowania decyzji, który można odtworzyć pod presją.

Podzapytanie

Podzapytanie to zapytanie wbudowane w inne zapytanie, często umieszczane w FROM, WHERE lub SELECT. Należy ono do tego samego zapytania, a optymalizator traktuje je jako jedną całość.

  • Nie wymaga nazwy (tabele pochodne wymagają aliasu).
  • Optymalizator może połączyć je z zapytaniem zewnętrznym.
  • Przy głębokim zagnieżdżeniu staje się rozwlekłe i trudne do czytania.
SELECT *
FROM (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
) t
WHERE t.total > 1000;

CTE

CTE to nazwane podzapytanie w bloku WITH, którego zakres ogranicza się do jednego zapytania. Czyta się je łatwiej niż głęboko zagnieżdżone podzapytanie, a ponadto można się do niego odwołać wiele razy.

  • Ma nazwę, więc dokumentuje intencję autora.
  • Można się do niego odwołać więcej niż raz w tym samym zapytaniu.
  • Nadal obowiązuje tylko w obrębie jednego zapytania, a potem znika.
WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM spend
WHERE total > 1000;

Tabela tymczasowa

Tabela tymczasowa to rzeczywista, fizyczna tabela istniejąca przez czas trwania sesji (lub transakcji). Wypełniają ją Państwo jednym zapytaniem, a następnie odczytują w kolejnych, odrębnych zapytaniach.

  • Przetrwa wiele zapytań w ramach jednej sesji.
  • Można utworzyć dla niej indeksy i zebrać statystyki.
  • Wymaga operacji wejścia-wyjścia na dysku i jawnego usunięcia.
CREATE TEMP TABLE spend AS
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id;

SELECT * FROM spend WHERE total > 1000;

Materializacja: kluczowa różnica

Kluczowym pojęciem, o które pytają osoby prowadzące rozmowy rekrutacyjne, jest materializacja: to, czy wynik pośredni jest fizycznie zapisywany w określonym miejscu.

  • Podzapytania i CTE zwykle nie są materializowane — optymalizator często rozwija je bezpośrednio.
  • Tabela tymczasowa jest zawsze materializowana w pamięci masowej.
  • Niektóre bazy danych pozwalają wymusić materializację CTE lub jej zabronić za pomocą podpowiedzi.

Bariery optymalizacji i dawna pułapka PostgreSQL

Historycznie PostgreSQL traktował każde CTE jako barierę optymalizacji, materializując je i blokując przesuwanie predykatów w dół. Od wersji Postgres 12 proste, nierekurencyjne CTE używane raz są domyślnie rozwijane bezpośrednio; podpowiedzi MATERIALIZED i NOT MATERIALIZED pozwalają zmienić to zachowanie.

Wspomnienie o tym szczególe wyraźnie pokazuje poziom doświadczonego inżyniera.

WITH spend AS NOT MATERIALIZED (
    SELECT customer_id, SUM(amount) AS total
    FROM orders GROUP BY customer_id
)
SELECT * FROM spend WHERE total > 1000;

Ponowne użycie w jednym zapytaniu

Jeśli w jednym zapytaniu odwołują się Państwo kilka razy do tego samego wyniku pośredniego, CTE może być czytelniejsze niż powtarzanie podzapytania. Należy jednak uważać: rozwijane bezpośrednio CTE może być obliczane ponownie przy każdym odwołaniu.

Gdy ponowne obliczenia są kosztowne, wymuszenie materializacji (lub użycie tabeli tymczasowej) pozwala uniknąć wykonywania tej samej pracy dwa razy.

Ponowne użycie w wielu zapytaniach

CTE i podzapytania obowiązują tylko w jednym zapytaniu. Jeśli ten sam wynik jest potrzebny w kilku odrębnych zapytaniach, właściwym narzędziem będzie tabela tymczasowa.

Typowy przypadek to wieloetapowy proces ETL lub raport, w którym raz tworzą Państwo zbiór danych pomostowych, a następnie wykonują kilka analiz. Utworzenie indeksu na tabeli tymczasowej może wtedy przyspieszyć każde kolejne zapytanie.

Indeksy i statystyki

Indeksy i aktualne statystyki może przechowywać wyłącznie tabela tymczasowa. W przypadku ogromnego zbioru pośredniego, wielokrotnie łączonego z innymi danymi, może to mieć decydujące znaczenie.

  • CTE/podzapytanie: optymalizator korzysta z oszacowań opartych na tabelach źródłowych.
  • Tabela tymczasowa: można wykonać na niej ANALYZE i dodać indeksy dostosowane do kolejnych połączeń.

Dlatego w przypadku dużych, intensywnie ponownie wykorzystywanych wyników tabela tymczasowa może zapewnić lepszą wydajność mimo dodatkowych kroków.

Schemat podejmowania decyzji

Oto zwięzła odpowiedź na rozmowę rekrutacyjną:

  • Podzapytanie: jednorazowe, płytko zagnieżdżone, gdy czytelność jest wystarczająca.
  • CTE: poprawia czytelność lub jest używane kilka razy w jednym zapytaniu.
  • Tabela tymczasowa: używana w wielu zapytaniach, bardzo duża albo wymagająca indeksów/statystyk.

Domyślnie warto wybrać CTE ze względu na czytelność; po tabelę tymczasową należy sięgać, gdy materializacja lub ponowne użycie w wielu zapytaniach rzeczywiście przynosi korzyść.

Jak przedstawić kompromis

Należy unikać kategorycznych stwierdzeń, takich jak „CTE zawsze są wolniejsze”. Lepiej powiedzieć: CTE i podzapytania są zwykle rozwijane bezpośrednio, więc chodzi w nich głównie o czytelność; tabela tymczasowa jest materializowana i warto jej użyć, gdy ponownie wykorzystuję duży wynik w wielu zapytaniach albo potrzebuję indeksu.

Przyznanie, że to zachowanie zależy od silnika (a w Postgres także od wersji), pokazuje rzeczywiste zrozumienie tematu.

Szybkie sprawdzenie

Wybierz sytuację, w której tabela tymczasowa jest zdecydowanie lepszym rozwiązaniem.

Podsumowanie: CTE a podzapytanie i tabela tymczasowa

Wybór zależy od materializacji i zakresu.

  • Podzapytania i CTE: zwykle rozwijane bezpośrednio, obowiązują w jednym zapytaniu, wybierane ze względu na czytelność.
  • CTE zapewniają nazewnictwo i możliwość ponownego użycia w jednym zapytaniu.
  • Tabele tymczasowe: zawsze materializowane, zachowują się między kolejnymi zapytaniami i mogą mieć indeksy.
  • Postgres 12+ rozwija proste CTE bezpośrednio; podpowiedzi MATERIALIZED pozwalają nad tym zapanować.

Następnie: przekształcenie splątanego zagnieżdżonego zapytania w przejrzyste CTE.

Często zadawane pytania

Czy lekcja „CTE a podzapytanie i tabela tymczasowa” jest bezpłatna?

Tak — pełny tekst „CTE a podzapytanie i tabela tymczasowa” 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 „CTE a podzapytanie i tabela tymczasowa”?

Kompromisy związane z materializacją, ponownym użyciem i działaniem optymalizatora Ć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 3 z 4.

Ile czasu zajmuje lekcja „CTE a podzapytanie i tabela tymczasowa”?

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

  1. Pisanie pierwszego CTE
  2. Łączenie wielu CTE
  3. CTE a podzapytanie i tabela tymczasowa
  4. Refaktoryzacja zagnieżdżonych zapytań do CTE
← Powrót do SQL Interview Prep