SQL CTEs mit Beispielen erklärt

Erfahren Sie, wie Sie die Leistungsfähigkeit von Common Table Expressions (CTEs) nutzen können, um die Organisation und Lesbarkeit Ihrer SQL-Abfragen zu verbessern.

Die häufig verwendete Abkürzung CTE steht für Common Table Expression (Gemeinsamer Tabellenausdruck).

Um SQL Common Table Expressions in der Praxis kennenzulernen, empfehle ich den interaktiven Rekursive Abfragen Kurs auf LearnSQL.de. Er enthält über 100 praktische Übungen zu einfachen und komplexen rekursiven CTEs.

Was bewirkt ein CTE? Warum sollten Sie einen solchen in Ihrem SQL-Code verwenden? Lassen Sie uns diese Fragen beantworten.

Was ist ein CTE?

Ein Common Table Expression ist eine benannte temporäre Ergebnismenge. Sie erstellen einen CTE mit einer WITH-Abfrage und verweisen dann in einer SELECT-, INSERT-, UPDATE- oder DELETE-Anweisung auf ihn.

Nehmen wir an, Sie haben eine Tabelle namens schools mit den Spalten school_id, school_name, district_id, und der Anzahl der Schüler. Sie müssen eine Abfrage schreiben, die eine Liste von Schulen zusammen mit ihrer Bezirks-ID und der durchschnittlichen Anzahl von Schülern pro Schule in diesem Bezirk anzeigt.

Ihre Logik könnte folgendermaßen aussehen:

1. Erstellen Sie eine Tabelle mit einer Liste von Bezirken und der entsprechenden durchschnittlichen Anzahl von Schülern pro Schule.
2. Verbinden Sie diese Tabelle mit der Liste der Schulen und zeigen Sie die gewünschten Informationen an.
3. Löschen Sie die Tabelle mit der durchschnittlichen Anzahl der Schüler pro Schule für jeden Bezirk.

Wenn Sie ein CTE verwenden, müssen Sie keine Tabelle erstellen und löschen. Sie können einfach auf die temporäre Ergebnismenge verweisen, die durch die WITH-Abfrage erstellt wurde, wie Sie unten sehen:

WITH avg_students AS (
SELECT district_id, AVG(students) as average_students
FROM schools
GROUP BY district_id)
SELECT s.school_name, s.district_id, avg.average_students
FROM schools s
JOIN avg_students avg
ON s.district_id = avg.district_id;

Hier beginnen Sie also mit der Definition der temporären Ergebnismenge avg_students in der WITH-Abfrage. In den Klammern befindet sich eine SELECT-Anweisung, die diese Ergebnismenge definiert; sie enthält eine Liste von Bezirken und die entsprechende durchschnittliche Anzahl von Schülern pro Schule. Nach der schließenden Klammer beginnen Sie die eigentliche SELECT-Anweisung. Beachten Sie, dass Sie auf die temporäre Ergebnismenge wie auf eine normale Tabelle verweisen, indem Sie den ihr zugewiesenen Namen verwenden (avg_students). Die Ausgabe enthält den Namen der Schule, die ID ihres Bezirks und die durchschnittliche Anzahl der Schüler in diesem Bezirk.

Die temporäre Ergebnismenge wird "gelöscht", d. h. sie kann in keiner anderen SQL-Abfrage verwendet werden. Natürlich können Sie sie bei Bedarf erneut definieren.

Nachdem Sie nun ein grundlegendes Verständnis von Common Table Expressions und ihrer Syntax haben, ist es an der Zeit zu sehen, wie man CTEs in realen Geschäftsfällen verwendet.

CTEs in Aktion

Beginnen wir mit der Erkundung der Daten. Angenommen, Sie sind Datenanalytiker in einer Privatkundenbank und möchten die Boni analysieren, die den Mitarbeitern im letzten Monat gewährt wurden. Die folgende Tabelle ist der Ausgangspunkt:

Bonus_jan

Angenommen, Sie möchten den Bonus, der jedem Mitarbeiter gezahlt wurde, zusammen mit dem durchschnittlichen Bonus für seine Position sehen. Zu diesem Zweck müssen Sie zunächst den durchschnittlichen Bonus für jede Position berechnen. Dies kann in einer temporären Ergebnismenge (einer CTE) geschehen. Die gesamte Abfrage sieht dann wie folgt aus:

WITH avg_position AS (
    SELECT position, AVG(bonus) AS average_bonus_for_position
    FROM bonus_jan
    GROUP BY position)
SELECT b.employee_id, b.first_name, b.last_name, b.position, b.bonus, ap.average_bonus_for_position
FROM bonus_jan b
JOIN avg_position ap
ON b.position = ap.position;

Wie Sie sehen können, beginnen wir mit der Definition der temporären Ergebnismenge avg_position. Dann folgt die SELECT-Anweisung, in der Sie die Tabelle bonus_jan Tabelle mit der temporären Ergebnismenge avg_position verbinden, um Informationen über jeden Mitarbeiter, seine Prämie und die durchschnittliche Prämie für diese Position anzuzeigen:

Mehrere CTEs in einer Abfrage

Sie können mehrere Common Table Expressions in einer Abfrage haben - verwenden Sie einfach ein WITH-Schlüsselwort und trennen Sie die CTEs mit Kommas.

Nehmen wir an, Sie möchten den Bonus jedes Mitarbeiters mit dem durchschnittlichen Bonus für diese Position und dem durchschnittlichen Bonus für diese Region vergleichen. Dazu erstellen Sie zwei temporäre Ergebnissätze: einen mit der durchschnittlichen Prämie für jede Position und einen weiteren mit der durchschnittlichen Prämie für jede Region. Hier ist die gesamte Abfrage:

WITH avg_position AS (
    SELECT position, AVG(bonus) AS average_bonus_for_position
    FROM bonus_jan
    GROUP BY position),
    avg_region AS (
    SELECT region, AVG (bonus) AS average_bonus_for_region
    FROM bonus_jan
    GROUP BY region)    
SELECT b.employee_id, b.first_name, b.last_name, b.position, b.region, b.bonus, ap.average_bonus_for_position, ar.average_bonus_for_region
FROM bonus_jan b
JOIN avg_position ap
ON b.position = ap.position
JOIN avg_region ar
ON b.region = ar.region;

Nachdem Sie die temporären Ergebnissätze avg_position und avg_region definiert haben, schreiben Sie die Haupt-SELECT-Anweisung, um die durchschnittlichen Positions- und Regionsboni zusammen mit den Informationen der einzelnen Mitarbeiter anzuzeigen:

Verschachtelte CTEs

Common Table Expressions können auch verschachtelt werden. Das bedeutet, dass mehrere CTEs in derselben Abfrage vorhanden sind , wobei mindestens ein CTE auf einen anderen CTE verweist. Dies wird anhand eines Beispiels deutlicher werden.

Nehmen wir an, Sie möchten die Leistung der verschiedenen Filialen im Einzelhandelsnetz der Bank bewerten. Insbesondere möchten Sie den durchschnittlichen Bonus der Mitarbeiter für jede Filiale mit dem minimalen und maximalen durchschnittlichen Bonus für alle Filialen vergleichen.

Die Logik könnte folgendermaßen aussehen:

1. Berechnen Sie den durchschnittlichen Bonus der Mitarbeiter für jede Filiale (CTE: avg_per_outlet).
2. Ermitteln Sie den minimalen Durchschnittsbonus für alle Verkaufsstellen (CTE: min_bonus_outlet).
3. Ermitteln Sie den maximalen Durchschnittsbonus für alle Verkaufsstellen (CTE: max_bonus_outlet).
4. Geben Sie die ID jeder Verkaufsstelle zusammen mit dem Durchschnittsbonus für diese Verkaufsstelle sowie den minimalen und maximalen Durchschnittsbonus für alle Verkaufsstellen aus.

Um die CTEs min_bonus_outlet und max_bonus_outlet zu erstellen, müssen Sie auf die erste CTE, avg_per_outlet, verweisen. Hier ist die gesamte Abfrage:

WITH avg_per_outlet AS (
    SELECT outlet, AVG(bonus) AS average_bonus_for_outlet
    FROM bonus_jan
    GROUP BY outlet),
    min_bonus_outlet AS (
    SELECT MIN (average_bonus_for_outlet) AS min_avg_bonus_for_outlet
    FROM avg_per_outlet),
    max_bonus_outlet AS (
    SELECT MAX (average_bonus_for_outlet) AS max_avg_bonus_for_outlet
    FROM avg_per_outlet)    
SELECT ao.outlet, ao.average_bonus_for_outlet, min.min_avg_bonus_for_outlet,
max.max_avg_bonus_for_outlet
FROM avg_per_outlet ao
CROSS JOIN min_bonus_outlet min
CROSS JOIN max_bonus_outlet max;

Beachten Sie, dass es drei verschiedene Common Table Expressions gibt; zwei von ihnen (min_bonus_outlet und max_bonus_outlet) verweisen auf eine andere CTE (avg_per_outlet). Dies macht sie zu verschachtelten CTEs.

In der Haupt-SELECT-Anweisung zeigen wir die ID der Verkaufsstelle, den durchschnittlichen Bonus für alle Mitarbeiter in dieser Verkaufsstelle sowie den minimalen und maximalen durchschnittlichen Bonus für alle Verkaufsstellen an. Zu diesem Zweck werden alle drei temporären Ergebnismengen miteinander verknüpft. Hier ist die Ausgabe dieser Abfrage:

Weitere CTES-Beispiele finden Sie in den Einführungsleitfäden von LearnSQL.de, die erklären, was ein CTE ist und wann Sie es verwenden sollten.

Warum ein CTE verwenden?

Sie haben vielleicht bemerkt, dass Sie in den meisten Fällen eine oder mehrere Unterabfragen anstelle von CTEs verwenden könnten. Warum also eine CTE verwenden?

• Mit Common Table Expressions lassen sich lange Abfragen besser organisieren. Mehrere Unterabfragen sehen oft unübersichtlich aus.
• CTEs machen eine Abfrage auch lesbarer, da Sie einen Namen für jeden der in einer Abfrage verwendeten Common Table Expressions haben.
• CTEs organisieren die Abfrage so, dass sie die menschliche Logik besser widerspiegelt. Mit CTEs definieren Sie zunächst die temporäre(n) Ergebnismenge(n) und verweisen dann in der Hauptabfrage auf diese(n). Bei Unterabfragen beginnen Sie mit der Hauptabfrage und platzieren dann die Unterabfragen in der Mitte der Abfrage.
• Schließlich gibt es auch eine spezielle Kategorie von CTEs, die rekursiven CTEs, die sich selbst referenzieren können. Diese CTEs können Probleme lösen, die mit anderen Abfragen nicht gelöst werden können. Rekursive Abfragen sind besonders nützlich bei der Arbeit mit hierarchischen Daten.

Erfahren Sie mehr über rekursive CTEs in unseren ausführlichen Handbüchern über die Möglichkeiten von rekursiven SQL-Abfragen und hierarchischen Abfragen in PostgreSQL und Oracle.

Zeit zum Üben von Common Table Expressions!

Möchten Sie die Leistungsfähigkeit von CTEs in Ihren SQL-Abfragen nutzen? Wenn Sie CTEs wie ein Profi schreiben wollen, müssen Sie viel üben.

LearnSQL.de bietet einen umfassenden Kurs auf Rekursive Abfragen. Er umfasst 114 interaktive Übungen zu einfachen CTEs, verschachtelten CTEs und rekursiven CTEs. Dieser Kurs ist die perfekte Gelegenheit zu lernen, wie Sie Ihre SQL-Abfragen mit Common Table Expressions verwalten, wie und wann Sie CTEs verschachteln und wie Sie rekursive CTEs verwenden.

Möchten Sie mehr über SQL CTEs erfahren? Sehen Sie sich unsere einsteigerfreundlichen Anleitungen an:

• Was ist ein CTE?
• Wann sollte ich einen gemeinsamen Tabellenausdruck (CTE) verwenden?
• Wie organisiere ich SQL-Abfragen mit CTEs?

Viel Spaß beim Lernen!