5 praktische SQL CTE-Beispiele

Common Table Expressions (CTEs) wurden in SQL eingeführt, um die Lesbarkeit und die Struktur von SQL-Abfragen zu verbessern, insbesondere von solchen, die mehrere Schritte erfordern, um die erforderliche Ausgabe zu erhalten. In diesem Artikel werden wir anhand mehrerer Beispiele zeigen, wie SQL CTEs Ihnen bei komplexen Berechnungen und hierarchischen Datenstrukturen helfen können.

Gemeinsame Tabellenausdrücke in SQL

Common Table Expressions (CTEs), auch WITH Klauseln genannt, ermöglichen die Erstellung benannter Unterabfragen, auf die in der Hauptabfrage weiter verwiesen wird. CTEs wurden in SQL eingeführt, um die Lesbarkeit und die Struktur einer SQL-Anweisung zu verbessern.

Die grundlegende CTE-Syntax lautet wie folgt:

WITH subquery_name AS
(SELECT … subquery ...)
SELECT … main query ...

Wir beginnen mit dem Schlüsselwort WITH, gefolgt von dem Namen, den wir der CTE (Unterabfrage) zuweisen. Dann folgt das Schlüsselwort AS und die Unterabfrage wird in Klammern gesetzt. Nachdem die CTE definiert ist, gehen wir zur Hauptabfrage über, in der wir diese CTE über ihren Namen referenzieren können.

Wenn Sie mit CTEs noch nicht vertraut sind, sollten Sie sich diesen Artikel ansehen, in dem die Funktionsweise von CTEs näher erläutert wird.

Es ist möglich, mehrere CTEs in einer Abfrage zu haben, ein CTE in einem anderen zu referenzieren (d. h. verschachtelte CTEs) oder sogar ein CTE in sich selbst zu referenzieren (rekursive CTEs). Dies gibt uns eine ganze Reihe von Werkzeugen und Möglichkeiten.

SQL-CTE-Beispiele

Um zu zeigen, wie CTEs Sie bei verschiedenen analytischen Aufgaben unterstützen können, werde ich fünf praktische Beispiele durchgehen.

Wir beginnen mit der Tabelle ordersmit einigen grundlegenden Informationen wie dem Bestelldatum, der Kunden-ID, dem Namen der Filiale, der ID des Mitarbeiters, der die Bestellung registriert hat, und dem Gesamtbetrag der Bestellung.

Jetzt schreiben wir ein paar SQL-Abfragen! Sie können SQL CTEs auch in diesem interaktiven Kurs üben Rekursive Abfragen Kurs üben, der alle Arten von CTEs behandelt.

Beispiel 1

In unserem ersten Beispiel wollen wir die Gesamtsumme jeder Bestellung mit der durchschnittlichen Bestellsumme in der entsprechenden Filiale vergleichen.

Wir können damit beginnen, den durchschnittlichen Bestellbetrag für jede Filiale mithilfe einer CTE zu berechnen und diese Spalte zur Ausgabe der Hauptabfrage hinzuzufügen:

WITH avg_per_store AS
  (SELECT store, AVG(amount) AS average_order
   FROM orders
   GROUP BY store)
SELECT o.id, o.store, o.amount, avg.average_order AS avg_for_store
FROM orders o
JOIN avg_per_store avg
ON o.store = avg.store;

Wie Sie sehen, beginnt unsere Abfrage mit einer CTE namens avg_per_store. Mit dieser CTE erstellen wir eine Tabelle, die alle Filialen und den durchschnittlichen Bestellwert pro Filiale auflistet. Dann wählen wir in der Hauptabfrage die Bestell-ID, den Namen der Filiale, den Bestellbetrag aus der ursprünglichen orders Tabelle und den durchschnittlichen Bestellwert für jede Filiale (avg_for_store) aus der zuvor definierten CTE.

Hier ist die Ausgabe:

Anhand dieser Tabelle können wir sehen, wie jede Bestellung im Vergleich zum durchschnittlichen Bestellwert in der entsprechenden Filiale abschneidet.

Gehen wir nun zu einem komplexeren Beispiel über.

Beispiel 2

Hier werden wir verschiedene Filialen vergleichen. Insbesondere wollen wir sehen, wie sich der durchschnittliche Bestellwert für jede Filiale im Vergleich zum Minimum und Maximum des durchschnittlichen Bestellwerts aller Filialen verhält.

Wie in unserem ersten Beispiel beginnen wir mit der Berechnung des durchschnittlichen Bestellbetrags für jede Filiale mithilfe einer CTE. Dann werden wir zwei weitere CTEs definieren:

• Zur Berechnung des Minimums des durchschnittlichen Bestellbetrags für alle Filialen.
• Zur Berechnung des Maximums des durchschnittlichen Bestellwerts aller Filialen.

Beachten Sie, dass diese beiden CTEs das Ergebnis der ersten CTE verwenden werden.

Schließlich werden in der Hauptabfrage alle drei CTEs verknüpft, um die benötigten Informationen zu erhalten:

WITH avg_per_store AS (
    SELECT store, AVG(amount) AS average_order
    FROM orders
    GROUP BY store),
    min_order_store AS (
    SELECT MIN (average_order) AS min_avg_order_store
    FROM avg_per_store),
    max_order_store AS (
    SELECT MAX (average_order) AS max_avg_order_store
    FROM avg_per_store)
SELECT avg.store, avg.average_order, min.min_avg_order_store,
max.max_avg_order_store
FROM avg_per_store avg
CROSS JOIN min_order_store min
CROSS JOIN max_order_store max;

Wie Sie sehen, bleibt die SQL-Abfrage auch mit mehreren verschachtelten CTEs übersichtlich und leicht zu verstehen. Wenn Sie Unterabfragen verwenden würden, müssten Sie eine Unterabfrage in die beiden anderen verschachteln und sie innerhalb derselben Abfrage mehrmals wiederholen. Hier, mit CTEs, definieren wir einfach alle drei CTEs am Anfang und verweisen dann bei Bedarf auf sie.

Hier ist die Ausgabe dieser Abfrage:

Sie können leicht erkennen, wie jedes Geschäft im Vergleich zu den anderen in Bezug auf den durchschnittlichen Bestellbetrag abschneidet. Wenn Sie nur drei Filialen haben, können wir sie natürlich auch einfach vergleichen, ohne die Spalten min_avg_order_store und max_avg_order_store hinzuzufügen. Wenn Sie jedoch die Leistung vieler Filialen anhand verschiedener Metriken analysieren müssen, kann dieser Ansatz sehr hilfreich sein.

Lesen Sie diesen Leitfaden, um die besten Praktiken für SQL CTE zu lernen.

Beispiel 3

In unserem nächsten Beispiel fahren wir mit dem Vergleich der Leistung unserer Filialen fort, allerdings mit einigen anderen Metriken. Nehmen wir an, unser Unternehmen betrachtet Bestellungen unter 200 $ als klein und Bestellungen von 200 $ oder mehr als groß. Nun wollen wir berechnen, wie viele große und kleine Bestellungen jede Filiale hatte.

Um diese Aufgabe mit WITH Klauseln zu lösen, benötigen wir zwei allgemeine Tabellenausdrücke:

• Um die Anzahl der großen Bestellungen für jede Filiale zu ermitteln.
• Um die Anzahl der kleinen Bestellungen für jede Filiale zu ermitteln.

Einige Filialen haben möglicherweise weder große noch kleine Bestellungen, was zu NULL Werten führt. Wir müssen sicherstellen, dass wir bei JOINs keine Filialen verlieren. Aus diesem Grund ziehe ich es vor, eine weitere CTE zu haben, die einfach eine Liste aller Geschäfte ausgibt. In der Hauptabfrage verknüpfen wir dann diese CTE mit den beiden CTEs, die die Metriken für große und kleine Bestellungen enthalten:

WITH stores AS
   (SELECT store
    FROM orders
    GROUP BY store),
  big AS
  (SELECT store, COUNT(*) AS big_orders
   FROM orders
   WHERE amount >= 200.00
   GROUP BY store),
  small AS
  (SELECT store, COUNT(*) AS small_orders
   FROM orders
   WHERE amount < 200.00
   GROUP BY store)
SELECT s.store, b.big_orders, sm.small_orders
FROM stores s
FULL JOIN big b
ON s.store = b.store
FULL JOIN small sm
ON s.store = sm.store;

In dieser Abfrage werden wir also:

• Definieren Sie die CTE stores um eine vollständige Liste der Geschäfte zu erhalten.
• Definieren Sie die CTE big um für jede Filiale die Anzahl der Bestellungen mit einem Gesamtbetrag von 200 $ oder mehr zu berechnen.
• Definieren Sie die CTE small um für jede Filiale die Anzahl der Bestellungen unter 200 $ zu berechnen.
• Verbinden Sie alle drei CTEs.

Hier ist die Ausgabe:

Wir sehen, dass die Filiale West sehr gut abschneidet; alle ihre Bestellungen liegen über 200 $. Die Filiale in der Mitte ist ebenfalls gut, mit zwei Bestellungen über 200 $ und einer Bestellung unter 200 $. Nur die Hälfte der Bestellungen in der Filiale Ost sind groß, mit zwei Bestellungen über 200 $ und zwei Bestellungen unter 200 $.

Beispiel 4

Für die nächsten beiden Beispiele verwenden wir die unten stehende Tabelle mit einigen grundlegenden Informationen über die Mitarbeiter unseres Unternehmens. Wir haben die Mitarbeiter-ID, den Vornamen, den Nachnamen, die ID des Vorgesetzten des Mitarbeiters, die Abteilung und den letzten Bonusbetrag.

Berechnen wir nun den durchschnittlichen Bonus nach Abteilung und zählen dann, wie viele Mitarbeiter einen Bonus über dem Durchschnitt ihrer Abteilung und wie viele einen darunter hatten.

Gemeinsame Tabellenausdrücke können bei solch komplexen Berechnungen sehr nützlich sein. In dieser SQL-Abfrage werden drei CTEs verwendet:

• Zur Berechnung des durchschnittlichen Bonusbetrags für jede Abteilung.
• Berechnung der Anzahl der Mitarbeiter, deren Boni über dem Durchschnitt der jeweiligen Abteilung liegen, nach Abteilung.
• Berechnung der Anzahl der Mitarbeiter, deren Boni unter dem jeweiligen Abteilungsdurchschnitt liegen, nach Abteilung.

In der Hauptabfrage verbinden wir alle drei CTEs.

WITH avg_bonus_department AS
    (SELECT department, AVG(bonus) AS average_bonus
    FROM employees
    GROUP BY department),
    above_average AS
    (SELECT e.department, count(*) AS employees_above_average
     FROM employees e
     JOIN avg_bonus_department avg
     ON e.department = avg.department
     WHERE bonus > average_bonus
     GROUP BY e.department),
     below_average AS
     (SELECT e.department, count(*) AS employees_below_average
     FROM employees e
     JOIN avg_bonus_department avg
     ON e.department = avg.department
     WHERE bonus < average_bonus
     GROUP BY e.department)
SELECT avg.department, avg.average_bonus, aa.employees_above_average, ba.employees_below_average
FROM avg_bonus_department avg
LEFT JOIN above_average aa
ON avg.department = aa.department
LEFT JOIN below_average ba
ON avg.department = ba.department;

Hier ist das Ergebnis der Abfrage:

Da es nur eine Person in der Finanzabteilung gibt, ist der durchschnittliche Bonus für die Abteilung genau gleich dem Bonus dieser Person. Folglich gibt es in der Finanzabteilung niemanden, dessen Bonus entweder über oder unter dem Durchschnitt liegt (was sich im Ergebnis als NULL-Werte widerspiegelt). Das Gleiche gilt für den CEO.

Für die Vertriebsabteilung können wir feststellen, dass der durchschnittliche Bonus 828,57 $ betrug und nur zwei von sieben Personen einen Bonus über dem Abteilungsdurchschnitt hatten.

Wir überlassen es Ihnen, die Ergebnisse für die Abteilungen Marketing und Operations auf die gleiche Weise zu interpretieren, und gehen dann zu einem noch komplexeren Beispiel mit einer rekursiven Abfrage über.

Beispiel 5

Gemeinsame Tabellenausdrücke können auf sich selbst verweisen, was sie zu einem perfekten Werkzeug für die Analyse hierarchischer Strukturen macht. Lassen Sie uns das an einem Beispiel sehen.

Unter Verwendung der Informationen aus der employees Tabelle und der orders Tabelle können wir die folgende Organisationsstruktur unseres Unternehmens zeichnen. Das Ladenpersonal wird als Teil des Verkaufsteams betrachtet. Außerdem können wir in der orders Tabelle sehen wir, welche Mitarbeiter in welchen Filialen Aufträge haben, so dass wir die Filiale ableiten können, zu der jeder Verkäufer gehört.

Nehmen wir nun an, dass wir die Ebene jedes Mitarbeiters in der Organisationsstruktur herausfinden müssen (d.h. Ebene 1 ist der CEO, Ebene 2 ist für seine direkten Untergebenen, usw.). Wir können eine Spalte hinzufügen, die dies mit einer rekursiven Abfrage anzeigt:

WITH RECURSIVE levels AS (
  SELECT
    id,
    first_name,
    last_name,
    superior_id,
    1 AS level
  FROM employees
  WHERE superior_id IS NULL
  UNION ALL
  SELECT
    employees.id,
    employees.first_name,
    employees.last_name,
    employees.superior_id,
    levels.level + 1
  FROM employees, levels
  WHERE employees.superior_id = levels.id
)

SELECT *
FROM levels;

Wie Sie sehen, verweist die CTE levels in dieser Abfrage auf sich selbst. Sie beginnt mit der Auswahl des Datensatzes, der dem Big Boss entspricht, also demjenigen, der keinen Vorgesetzten hat (d. h. superior_id IS NULL). Wir weisen der Ebene dieser Person 1 zu und verwenden dann UNION ALL um weitere Datensätze hinzuzufügen, und zwar einen Datensatz für jede Führungsebene in der Organisationsstruktur.

Hier ist die Ausgabe:

Das Thema rekursive Abfragen ist recht anspruchsvoll, daher werde ich hier nicht weiter darauf eingehen. Lesen Sie aber unbedingt diesen Artikel, in dem rekursive CTEs anhand von Beispielen erklärt werden, insbesondere wenn Sie mit hierarchischen Daten arbeiten.

Und in diesem Artikel finden Sie weitere SQL CTE-Beispiele.

Lassen Sie uns SQL CTEs üben!

Ich hoffe, diese Beispiele haben Ihnen gezeigt, wie praktisch CTEs für verschiedene analytische Aufgaben sein können. Sie tragen dazu bei, die Lesbarkeit und die Struktur Ihrer SQL-Abfragen zu verbessern, helfen bei verschachtelten und komplexen Berechnungen und sind nützlich für die effiziente Verarbeitung von hierarchischen Daten. In diesem Artikel erfahren Sie mehr darüber, wann Sie CTEs verwenden sollten.

Wenn Sie Common Table Expressions beherrschen wollen, empfehle ich Ihnen, mit diesem einzigartigen Rekursive Abfragen Kurs. Er enthält 114 interaktive Übungen zu allen Arten von CTEs, einschließlich einfacher CTEs, verschachtelter CTEs und rekursiver CTEs. Am Ende des Kurses werden Sie wissen, wie man SQL-Abfragen mit CTEs verwaltet, wie und wann man CTEs verschachtelt und wie man rekursive CTEs verwendet, um sich durch hierarchische Datenmodelle zu bewegen.

Wenn Sie weitere fortgeschrittene Werkzeuge für die Datenanalyse mit SQL beherrschen wollen, sollten Sie den Fortgeschrittenes SQL Kurs! Er geht über CTEs hinaus und umfasst auch Fensterfunktionen und GROUP BY-Erweiterungen in SQL. Und LearnSQL.de bietet viele verschiedene Möglichkeiten, diese fortgeschrittenen SQL-Konzepte online zu üben.

Vielen Dank für die Lektüre und viel Spaß beim Lernen!