Tworzenie tymczasowych tabel z użyciem WITH

Tymczasowe tabele to świetny sposób na przechowanie wyników pośrednich, żeby potem coś z nimi zrobić. To jak w programowaniu: zamiast kopiować ten sam ciężki kawałek kodu, wrzucasz go raz do zmiennej i potem używasz, kiedy chcesz. W świecie SQL rolę takich "zmiennych" często pełnią właśnie tymczasowe tabele.

Ale CTE robi to jeszcze prościej — pozwala ogarnąć tymczasowe tabele bez zbędnego zamieszania:

• Nie musisz się martwić o usuwanie.

  CTE żyje tylko podczas wykonywania zapytania. Jak tylko SQL skończy działać — CTE znika, jak niewidzialny asystent, który wszystko ogarnął i wyparował bez śladu.

• Kod jest czytelniejszy.

  Stworzenie CTE jest dużo prostsze niż zabawa z tworzeniem i usuwaniem fizycznej tymczasowej tabeli. Wszystko masz na widoku, wszystko jasne.

• Idealne na "jednorazowe" zadania.

  Jeśli musisz tylko przetworzyć dane na jednym etapie — CTE sprawdzi się idealnie. Szybko, czysto, bez efektów ubocznych.

Składnia CTE jako tymczasowej tabeli

Najpierw przypomnijmy sobie składnię CTE:

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

Tutaj WITH tworzy tymczasowe "wyrażenie tabelowe", które jest dostępne w całym zapytaniu, w którym zostało zdefiniowane. To wyrażenie wygląda jak tabela, ale żyje tylko do końca wykonania zapytania.

Przykład: policzmy prymusów

Stwórzmy tymczasową tabelę za pomocą CTE, żeby znaleźć studentów, którzy mają średnią ocen (grade) powyżej 90. Potem wyświetlimy ich listę.

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

Co tu robimy:

• używamy WITH, żeby stworzyć tymczasową tabelę high_achievers.
• grupujemy w CTE oceny (grades) dla każdego studenta (student_id) i liczymy średnią ocenę.
• w zapytaniu SELECT * po prostu odwołujemy się do tymczasowej tabeli high_achievers, jak do zwykłej tabeli.

Porównanie CTE i tymczasowych tabel

Czasem pojawia się pytanie: jaka jest różnica między użyciem CTE a tymczasowych tabel tworzonych przez CREATE TEMP TABLE?

Oto przykład klasycznej tymczasowej tabeli (CREATE TEMP TABLE) dla tych samych danych:

CREATE TEMP TABLE high_achievers_temp AS
	SELECT student_id, AVG(grade) AS avg_grade
	FROM grades
	GROUP BY student_id
	HAVING AVG(grade) > 90;

SELECT *
FROM high_achievers_temp;

DROP TABLE high_achievers_temp;  -- Nie zapomnij usunąć tabeli!

I analogiczne zapytanie przez CTE:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

Kiedy używać CTE, a kiedy — tymczasowych tabel

CTE świetnie się sprawdza, gdy chcesz szybko przygotować dane pośrednie i od razu z nimi popracować — bez zbędnego zamieszania. Nie musisz się martwić o usuwanie: CTE znika samo, jak tylko zapytanie się wykona. Od razu widać je w strukturze SQL, nie kryje się gdzieś na zewnątrz jak tymczasowa tabela. To sprawia, że zapytania są czystsze, prostsze i bardziej czytelne, szczególnie jeśli robisz kilka kroków przetwarzania danych pod rząd. A do tego CTE można łączyć, zagnieżdżać i budować z nich złożoną logikę — o tym pogadamy później.

Taka struktura działa super, gdy zapytanie jest jednorazowe, a dane są potrzebne tylko w ramach tego zapytania. Jeśli jednak planujesz użyć wyniku kilka razy w różnych częściach systemu albo chcesz przechować dane pośrednie przez całą sesję — tymczasowe tabele będą pewniejszym wyborem. Zwłaszcza jeśli danych jest dużo i zależy ci na wydajności: w takich przypadkach fizyczne tymczasowe tabele działają stabilniej i szybciej.

Wszystko zależy od zadania: CTE to szybkie, eleganckie narzędzie do lokalnego przetwarzania danych. A tymczasowa tabela to roboczy koń do bardziej skomplikowanych i dłuższych scenariuszy.

Przykład: Agregowanie danych

Załóżmy, że mamy tabelę enrollments, w której są wpisy o tym, jacy studenci są zapisani na jakie kursy. Chcemy się dowiedzieć, ilu studentów jest zapisanych na każdy kurs, ale tylko dla kursów z więcej niż 5 studentami.

Przez CTE robimy to tak:

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
    HAVING COUNT(student_id) > 5
)

SELECT *
FROM course_counts
ORDER BY student_count DESC;

Wiele CTE: Budowanie etapów

A co jeśli trzeba rozbić zadanie na kilka etapów? Na przykład najpierw wybrać studentów z wysoką średnią ocen, a potem znaleźć ich kursy? Proste!

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)

SELECT ha.student_id, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Teraz w zapytaniu mamy dwa CTE:

1. high_achievers znajduje prymusów.
2. student_courses przypisuje studentów do kursów.

Efekt — lista studentów z wysoką średnią ocen i ich kursami.

Typowe błędy przy używaniu CTE

Za dużo danych. CTE są materializowane w pamięci PostgreSQL. Jeśli tworzysz CTE z ogromnym wynikiem, może to spowolnić zapytanie albo przekroczyć limit pamięci.

Nadużywanie. Używanie CTE tam, gdzie zwykłe podzapytanie byłoby prostsze, może tylko namieszać.

Zapomniane indeksy. Jeśli dane w CTE są pobierane z dużych tabel bez indeksów, to spowolni zapytanie.