Proste CTE do przygotowania danych: przykłady i realne case'y

Proste CTE do przygotowania danych: przykłady i realne case'y

Wygląda na to, że już ogarniasz podstawy CTE i może nawet piszesz WITH prawie z automatu. Dzisiaj zanurkujmy trochę głębiej — i zobaczmy, jak używać CTE do przygotowania danych w prawdziwych sytuacjach. Wyobraź sobie, że chcesz zrobić raport albo złożone zapytanie SQL: najpierw trzeba rozłożyć składniki — a dopiero potem ugotować smaczny analityczny „rosół”.

CTE tutaj to świetne narzędzie do kroków pośrednich: filtrowania, liczenia, agregacji, wyliczania średnich — wszystkiego, co potrzebne do sensownego przygotowania danych. Możesz rozbić skomplikowane zapytanie na czytelne logiczne bloki, z których każdy robi jedną rzecz: wybiera odpowiednie rekordy, liczy średnią albo przygotowuje dane do końcowego selecta. To ułatwia czytanie kodu, eliminuje powtarzające się fragmenty i pozwala uniknąć tymczasowych tabel, jeśli nie są ci potrzebne.

Takie podejście z CTE jest szczególnie przydatne, gdy przygotowujesz dane do raportów, budujesz złożone filtrowanie albo chcesz „oczyścić” dane przed dalszą obróbką. W tym sensie CTE to nie tylko techniczny trik, ale pełnoprawna strategia — budowania logiki krok po kroku, bez tracenia kontroli nad tym, co się dzieje.

Gotowy? Lecimy do przykładów.

Filtrowanie danych z użyciem CTE

CTE to świetny sposób, żeby „wyciągnąć” potrzebne dane z dużej tabeli i dalej pracować już tylko z tym, co naprawdę cię interesuje. Zamiast pisać wielkie zagnieżdżone zapytania, najpierw filtrujesz dane, nadajesz temu krokowi nazwę — i potem spokojnie używasz wyniku jak zwykłej tabeli.

Załóżmy, że mamy tabelę students, gdzie trzymamy oceny studentów:

Tabela students

Załóżmy, że chcesz wybrać wszystkich, którzy mają ocenę powyżej 85. Z CTE robisz to maksymalnie przejrzyście:

WITH excellent_students AS (
    SELECT student_id, first_name, last_name, grade
    FROM students
    WHERE grade > 85
)
SELECT * FROM excellent_students;

Wynik:

Co tu jest wygodne?

Już na starcie wybrałeś potrzebne wiersze i nadałeś temu krokowi nazwę — excellent_students. Teraz ten wynik możesz użyć dalej: np. połączyć z inną tabelą, zrobić kolejne filtrowanie albo policzyć średnią ocenę. Wszystko czytelne, proste i nie plącze się, zwłaszcza jeśli zapytanie jest duże.

Agregacja danych z użyciem CTE

Teraz przejdźmy do sytuacji, gdzie trzeba policzyć liczbę rekordów albo wyliczyć średnie. Na przykład mamy tabelę enrollments, gdzie są dane o tym, na jakie kursy zapisani są studenci.

Tabela enrollments

Chcemy się dowiedzieć, ilu studentów jest zapisanych na każdy kurs.

Przykład zapytania:

WITH course_enrollments AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
)
SELECT * FROM course_enrollments;

Wynik:

Tu ważne:

• Pogrupowaliśmy dane po course_id i policzyliśmy ilu studentów jest na każdym kursie.
• Tabela course_enrollments ma teraz te informacje i możesz ją wykorzystać do dalszej analizy.

Przygotowanie danych do raportów

Jeśli musisz zbudować szczegółowy raport, oparty na kilku krokach przetwarzania danych, CTE to prawdziwy game-changer. Pozwala rozbić całą logikę na czytelne bloki i nie tworzyć niepotrzebnych tymczasowych tabel. Wyobraź sobie, że masz tabelę grades z ocenami i tabelę students z info o studentach. Trzeba zrobić raport, w którym będą tylko ci studenci, których średnia ocen jest powyżej 80.

Tabela grades

Tabela students

Zamiast wielkiego zagnieżdżonego zapytania możesz spokojnie złożyć wszystko krok po kroku:

WITH avg_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 80
),
students_with_grades AS (
    SELECT s.student_id, s.first_name, s.last_name, ag.avg_grade
    FROM students s
    JOIN avg_grades ag ON s.student_id = ag.student_id
)
SELECT * FROM students_with_grades;

W pierwszym kroku (avg_grades) policzyliśmy średnią ocenę dla każdego studenta i od razu odfiltrowaliśmy tylko tych, którzy mają dobre wyniki — powyżej 80. W drugim kroku (students_with_grades) połączyliśmy te dane z tabelą students, żeby mieć imię i nazwisko. W efekcie końcowy SELECT zwraca czyściutką tabelę, którą możesz wrzucić prosto do raportu — wszystko już policzone, przefiltrowane i ładnie podane.

Wynik:

Właśnie takie podejście sprawia, że CTE jest wygodne: możesz skupić się na logice i strukturze, nie rozpraszając się na pomocnicze akcje typu tworzenie i kasowanie tymczasowych tabel.

Liczenie złożonych metryk

Czasem trzeba połączyć różne dane w jednym zapytaniu. Na przykład, chcemy policzyć dla każdego kursu:

1. Liczbę studentów.
2. Średnią ocenę na kursie.

Przykład zapytania:

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
),
course_avg_grades AS (
    SELECT e.course_id, AVG(g.grade) AS avg_grade
    FROM enrollments e
    JOIN grades g ON e.student_id = g.student_id
    GROUP BY e.course_id
)
SELECT cc.course_id, cc.student_count, cag.avg_grade
FROM course_counts cc
JOIN course_avg_grades cag ON cc.course_id = cag.course_id;

Błędy, których warto unikać

Pracując z CTE, łatwo się pogubić i zrobić kilka typowych błędów.

Pierwszy — nadmierna materializacja. Jeśli tworzysz za dużo CTE, PostgreSQL może zapisać ich wyniki jako tymczasowe tabele, nawet jeśli są potrzebne tylko raz. W efekcie zapytanie będzie działać wolniej, niż byś chciał.

Drugi błąd — złe stosowanie filtrów. Jeśli filtry są nakładane w złej kolejności albo na różnych etapach różnie, końcowy wynik może być nie taki, jak się spodziewasz. Na przykład możesz przypadkiem odrzucić ważne dane za wcześnie.

Dlatego CTE najlepiej używać tam, gdzie dane przechodzą kilka kolejnych transformacji — właśnie wtedy to narzędzie pokazuje pełnię swoich zalet i pomaga pisać czysty, czytelny i efektywny kod.