Użycie CTE dla poprawy czytelności złożonych zapytań

Wyobraź sobie, że musisz napisać duże zapytanie SQL, które robi kilka powiązanych operacji naraz. Możesz po prostu powkładać mnóstwo podzapytań jedno w drugie, ale efekt będzie jak spaghetti-code. Prawdziwy SQL-labirynt, w którym łatwo się zgubić, nawet jeśli sam go napisałeś.

CTE to Twój koło ratunkowe! CTE pozwala podzielić skomplikowane zapytanie na logiczne części, z których każda jest osobną, nazwaną sekcją. Dzięki temu Twój kod jest czytelny i łatwy w utrzymaniu.

Porównanie: Podzapytanie vs CTE

Na pierwszy rzut oka oba podejścia robią to samo — filtrują oceny po kursie i liczą średnią dla każdego studenta. Ale spójrz dokładniej: w wersji z podzapytaniem logika jest "schowana" w nawiasach, a w CTE wyniesiona na zewnątrz i dostała czytelną nazwę filtered_grades. Teraz wyobraź sobie, że takich kroków pośrednich jest nie dwa, a dziesięć!

Podzapytanie:

SELECT student_id, AVG(grade) AS avg_grade
FROM (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
) subquery
GROUP BY student_id;

CTE:

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
)
SELECT student_id, AVG(grade) AS avg_grade
FROM filtered_grades
GROUP BY student_id;

Znajdź 10 różnic. Oczywiście, CTE wygrywa jeśli chodzi o wygodę czytania!

Podział złożonych zapytań na etapy z użyciem CTE

CTE pozwala budować zapytanie krok po kroku, tak żeby na każdym etapie wynik był maksymalnie czytelny. Na przykład, jeśli chcesz dostać listę studentów z ich średnią oceną z kursów i dodać do tego info o ich nauczycielach, rozbij zadanie na kilka części.

Przykład:

WITH avg_grades AS (
    SELECT student_id, course_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id, course_id
),
course_teachers AS (
    SELECT course_id, teacher_id
    FROM courses
)

SELECT ag.student_id, ag.avg_grade, ct.teacher_id
FROM avg_grades ag
JOIN course_teachers ct ON ag.course_id = ct.course_id;

Czy to nie jest czytelne? Nawet jeśli wrócisz do tego zapytania za miesiąc, jego struktura będzie oczywista.

Użycie wielu CTE do dużego raportu

Przyjrzyjmy się przykładowi bardziej złożonego raportu. Załóżmy, że mamy bazę danych uniwersytetu i chcemy zrobić raport o najbardziej ogarniętych studentach, ich kursach i nauczycielach. Plan jest taki:

1. Najpierw znajdź studentów z wysoką średnią ocen (powyżej 90).
2. Potem powiąż ich z kursami.
3. Na końcu dodaj dane o nauczycielach.

Zapytanie z kilkoma CTE:

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, c.teacher_id
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
),
teachers AS (
    SELECT teacher_id, name AS teacher_name
    FROM teachers
)

SELECT ha.student_id, ha.avg_grade, sc.course_name, t.teacher_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN teachers t ON sc.teacher_id = t.teacher_id;

To zapytanie jest fajne, bo każdą część zadania zrobiliśmy jako osobny, logicznie zamknięty blok. Chcesz sprawdzić, kto ze studentów osiągnął imponujące wyniki? Zajrzyj do CTE high_achievers. Interesuje Cię ich powiązanie z kursami? To student_courses. Potrzebni nauczyciele? Wszystko w teachers. Takie podejście mocno ułatwia utrzymanie i modyfikację kodu.

Podział na etapy złożonych obliczeń

Czasem Twoje zapytania zawierają skomplikowane wyliczenia albo filtrowanie. Zamiast próbować upchnąć wszystko w jednym długim zapytaniu, rozbij je na kilka CTE.

Przykład:

WITH course_stats AS (
    SELECT course_id, COUNT(student_id) AS student_count, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY course_id
),
popular_courses AS (
    SELECT course_id
    FROM course_stats
    WHERE student_count > 50
)
SELECT c.course_name, cs.student_count, cs.avg_grade
FROM popular_courses pc
JOIN course_stats cs ON pc.course_id = cs.course_id
JOIN courses c ON c.course_id = pc.course_id;

Najpierw zbieramy statystyki kursów w course_stats, potem filtrujemy popularne kursy w popular_courses, a na końcu łączymy to z tabelą kursów. Taka metoda pozwala wyodrębnić etapy pośrednie i mocno ułatwia zrozumienie zapytania.

Kiedy CTE jest niezastąpione?

Oto kilka sytuacji, gdzie CTE szczególnie się przydaje:

1. Analiza i raportowanie. Na przykład, wyliczanie złożonych wskaźników z filtrowaniem po grupach.
2. Praca ze strukturami hierarchicznymi. Rekurencyjne CTE do budowania drzewa kategorii albo struktury organizacyjnej.
3. Ponowne użycie danych. Na przykład, jeśli ten sam zbiór danych jest używany na różnych etapach zapytania.

Typowe błędy przy użyciu CTE

Oczywiście, jak każde potężne narzędzie, CTE ma swoje ukryte pułapki.

Nadmierna materializacja danych. W PostgreSQL CTE domyślnie są "materializowane", czyli ich wynik jest wyliczany i tymczasowo zapisywany. To może spowolnić wykonanie, jeśli danych jest dużo. Żeby tego uniknąć, używaj indeksów i staraj się wybierać tylko niezbędne kolumny.

Nieprawidłowe joiny. Czasem złożone zapytania z wieloma CTE są trudne do optymalizacji. Zawsze sprawdzaj swoje zapytania za pomocą EXPLAIN albo EXPLAIN ANALYZE.

Nadmierne użycie CTE. Jeśli Twoje CTE robią się za długie i zagmatwane, to znak, że warto podzielić zapytania na kilka osobnych operacji.