Einfache CTEs zur Datenvorbereitung: Beispiele und echte Use Cases

Einfache CTEs zur Datenvorbereitung: Beispiele und echte Use Cases

Sieht so aus, als hättest du die Basics von CTE schon drauf und schreibst WITH vielleicht sogar schon fast automatisch. Heute gehen wir mal ein Stück tiefer — und schauen uns an, wie man CTEs für die Datenvorbereitung in echten Situationen nutzt. Stell dir vor, du willst einen Report oder einen komplexen SQL-Query bauen: Erst mal die Zutaten sortieren — und dann einen leckeren analytischen „Suppe“ kochen.

CTEs sind hier ein super Tool für Zwischenschritte: Filtern, Zählen, Aggregieren, Durchschnitt berechnen — alles, was du für eine sinnvolle Datenvorbereitung brauchst. Du kannst einen komplexen Query in verständliche logische Blöcke aufteilen, von denen jeder nur eine Sache macht: die richtigen Zeilen auswählen, den Durchschnitt berechnen oder Daten für das finale Select vorbereiten. Das macht den Code lesbarer, spart dir Wiederholungen und du brauchst keine temporären Tabellen, wenn du sie nicht willst.

Der Ansatz mit CTE ist besonders praktisch, wenn du Daten für Reports vorbereitest, komplexe Filter baust oder die Daten „aufräumen“ willst, bevor du weiterarbeitest. In dem Sinne ist CTE nicht nur ein technischer Trick, sondern eine richtige Strategie — Schritt für Schritt die Logik aufbauen, ohne den Überblick zu verlieren.

Bist du bereit? Dann kommen jetzt die Beispiele.

Daten filtern mit CTE

CTE ist ein super Weg, um die Daten, die du brauchst, aus einer großen Tabelle „rauszuziehen“, damit du danach nur noch mit dem arbeitest, was wirklich wichtig ist. Anstatt verschachtelte Queries zu schreiben, filterst du erst mal die Daten, gibst diesem Schritt einen Namen — und arbeitest dann ganz entspannt mit dem Ergebnis wie mit einer normalen Tabelle weiter.

Stell dir vor, wir haben eine Tabelle students, in der die Noten der Studierenden gespeichert sind:

Tabelle students

Sagen wir, du willst alle auswählen, die eine Note über 85 haben. Mit CTE geht das super transparent:

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

Ergebnis:

Was ist daran praktisch?

Du hast die richtigen Zeilen schon im Voraus ausgewählt und diesem Schritt einen Namen gegeben — excellent_students. Jetzt kannst du das Ergebnis weiterverwenden: zum Beispiel mit einer anderen Tabelle joinen, nochmal filtern oder den Durchschnitt berechnen. Alles bleibt lesbar, einfach und verwirrt nicht, gerade wenn der Query größer wird.

Daten aggregieren mit CTE

Jetzt schauen wir uns einen Fall an, wo du die Anzahl der Einträge zählen oder Durchschnittswerte berechnen willst. Zum Beispiel gibt es eine Tabelle enrollments, in der steht, welche Studierenden in welchen Kurs eingeschrieben sind.

Tabelle enrollments

Wir wollen wissen, wie viele Studierende in jedem Kurs eingeschrieben sind.

Beispiel-Query:

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

Ergebnis:

Wichtig hier:

• Wir haben die Daten nach course_id gruppiert und die Anzahl der Studierenden pro Kurs gezählt.
• Die Tabelle course_enrollments enthält jetzt diese Infos und kann für weitere Analysen genutzt werden.

Daten für Reports vorbereiten

Wenn du einen ausführlichen Report brauchst, der auf mehreren Verarbeitungsschritten basiert, ist CTE echt Gold wert. Du kannst die ganze Logik in verständliche Blöcke aufteilen und musst keine unnötigen temporären Tabellen bauen. Stell dir vor, du hast eine Tabelle grades mit Noten und eine Tabelle students mit Infos zu den Studierenden. Du willst einen Report, in dem nur die Studierenden stehen, deren Durchschnittsnote über 80 liegt.

Tabelle grades

Tabelle students

Statt eines riesigen verschachtelten Queries kannst du alles entspannt Schritt für Schritt bauen:

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;

Im ersten Schritt (avg_grades) haben wir den Durchschnitt für jede:n Studierende:n berechnet und gleich nur die genommen, die gute Ergebnisse hatten — über 80. Im zweiten Schritt (students_with_grades) haben wir diese Daten mit der Tabelle students verbunden, um Vor- und Nachnamen zu bekommen. Das finale SELECT gibt dir eine saubere Tabelle zurück, die du direkt in den Report packen kannst — alles schon berechnet, gefiltert und schick formatiert.

Ergebnis:

Genau so ein Ansatz macht CTEs so praktisch: Du kannst dich auf Logik und Struktur konzentrieren, ohne dich mit Hilfstabellen oder deren Löschung rumzuärgern.

Komplexe Metriken berechnen

Manchmal musst du verschiedene Daten in einem Query kombinieren. Zum Beispiel wollen wir für jeden Kurs berechnen:

1. Wie viele Studierende es gibt.
2. Den Durchschnitts-Grade pro Kurs.

Beispiel-Query:

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;

Fehler, die du vermeiden solltest

Wenn du mit CTE arbeitest, kann man sich leicht verzetteln und ein paar typische Fehler machen.

Der erste — zu viel Materialisierung. Wenn du zu viele CTEs baust, kann PostgreSQL die Ergebnisse als temporäre Tabellen speichern, selbst wenn du sie nur einmal brauchst. Dann wird der Query langsamer, als du willst.

Der zweite Fehler — Filter falsch anwenden. Wenn du Filter in der falschen Reihenfolge oder auf verschiedenen Ebenen unterschiedlich setzt, bekommst du am Ende vielleicht nicht das Ergebnis, das du erwartet hast. Zum Beispiel kannst du wichtige Daten zu früh rausfiltern.

Deshalb: Nutze CTEs am besten da, wo die Daten mehrere aufeinanderfolgende Transformationen durchlaufen — genau da spielt das Tool seine Stärken aus und hilft dir, sauberen, verständlichen und effizienten Code zu schreiben.