25th Jun 2024 9 Leseminuten 5 praktische SQL CTE-Beispiele Kateryna Koidan SQL SQL lernen cte Inhaltsverzeichnis Gemeinsame Tabellenausdrücke in SQL SQL-CTE-Beispiele Beispiel 1 Beispiel 2 Beispiel 3 Beispiel 4 Beispiel 5 Lassen Sie uns SQL CTEs üben! 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. orders iddatecustomer_idstoreemployee_idamount 1012021-07-01234East11198.00 1022021-07-01675West13799.00 1032021-07-01456West14698.00 1042021-07-01980Center1599.00 1052021-07-02594Center161045.45 1062021-07-02435East11599.00 1072021-07-02246West14678.89 1082021-07-03256East12458.80 1092021-07-03785East1299.00 1102021-07-03443Center16325.50 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: idstoreamountavg_for_store 101East198.00338.70 102West799.00725.30 103West698.00725.30 104Center99.00489.98 105Center1045.45489.98 106East599.00338.70 107West678.89725.30 108East458.80338.70 109East99.00338.70 110Center325.50489.98 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: storeaverage_ordermin_avg_order_storemax_avg_order_store Center489.98338.70725.30 East338.70338.70725.30 West725.30338.70725.30 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: storebig_orderssmall_orders Center21 East22 West3NULL 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. employees idfirst_namelast_namesuperior_iddepartmentbonus 1JohnDaviesNULLCEO2545.00 2MarkTaylor1Finance1100.00 3KateWilson1Operations900.00 4OliviaWatson3Operations450.00 5JamesAddington1Sales1900.00 6RachaelWhite1Marketing1250.00 7SaraClinton6Marketing1000.00 11JohnSmith5Sales800.00 12NoahJones11Sales500.00 13StevenBrown5Sales900.00 14LiamWilliams13Sales700.00 15PaulLee5Sales500.00 16PatrickEvans15Sales500.00 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: departmentaverage_bonusemployees_above_averageemployees_below_average CEO2545.00NULLNULL Marketing1125.0011 Finance1100.00NULLNULL Operations675.0011 Sales828.5725 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: idfirst_namelast_namesuperior_idlevel 1JohnDaviesNULL1 2MarkTaylor12 3KateWilson12 5JamesAddington12 6RachaelWhite12 4OliviaWatson33 7SaraClinton63 11JohnSmith53 13StevenBrown53 15PaulLee53 12NoahJones114 14LiamWilliams134 16PatrickEvans154 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! Tags: SQL SQL lernen cte