Der MySQL CTE und seine Verwendung

Common Table Expressions (CTEs) in MySQL verleihen diesem beliebten Datenbankmanagementsystem noch mehr Vielseitigkeit. Erfahren Sie, wie Sie CTEs zur Vereinfachung komplexer Abfragen und zur Implementierung von Rekursionen einsetzen können.

In allen Programmiersprachen gibt es Möglichkeiten, Probleme zu vereinfachen, indem man sie in Teile zerlegt, die einzeln gelöst werden können. Die Benutzer können die Teilergebnisse zusammenführen, um ein einziges Endergebnis zu erhalten. Dank der so genannten Common Table Expressions (CTEs) ist MySQL jetzt eine Ausnahme. Ein MySQL-CTE ermöglicht es Ihnen, einer temporären Ergebnismenge einen Namen zuzuweisen und dann in den Anweisungen SELECT, INSERT, UPDATE oder DELETE auf diese Ergebnismenge zu verweisen (als wäre sie eine Tabelle oder ein View).

CTEs sind seit 1999 Teil des SQL-Standards. Ihre effektive Implementierung in den Dialekten der einzelnen relationalen Datenbankmanagementsysteme (RDBMS) erfolgte jedoch erst nach und nach, als die Benutzer diese neue Funktion für nützlich hielten. MySQL war eines der letzten populären RDBMS, das die Unterstützung für CTEs einführte. Erst in Version 8, die im April 2018 veröffentlicht wurde, wurde die Unterstützung für CTEs in MySQL eingeführt.

Wie wir weiter unten sehen werden, besteht eine der Hauptanwendungen von MySQL CTE darin, Rekursion zu implementieren. Sie können unseren interaktiven Kurs über rekursive Abfragen in MySQL besuchen, wenn Sie direkt in dieses Thema einsteigen möchten. In diesem Kurs erhalten Sie alle notwendigen Kenntnisse, um rekursive Abfragen in MySQL 8 zu beherrschen. Alles, was Sie brauchen, ist ein Webbrowser, eine Internetverbindung und Grundkenntnisse in SQL (einschließlich der Klauseln JOIN und GROUP BY ). Es gibt 114 interaktive Übungen mit Hinweisen und Online-Hilfe, so dass Sie Ihre Kenntnisse wirklich vertiefen können.

Doch konzentrieren wir uns zunächst auf die Grundlagen von MySQL CTEs und beginnen mit der einfachsten Syntax.

Grundlegende Syntax für eine MySQL-CTE

Die Syntax zum Schreiben einer MySQL-CTE beginnt mit dem Wort WITH, gefolgt vom Namen der CTE und einer Liste der Spalten, die die CTE zurückgibt. Diese Liste ist nicht obligatorisch, da sie durch das Ergebnis der Abfrage, die die CTE definiert, festgelegt werden kann. Diese Abfrage muss in Klammern nach dem Wort AS geschrieben werden.

Die grundlegende Syntax von Common Table Expressions in MySQL lässt sich wie folgt zusammenfassen:

WITH cte_name (column_list) AS (
    query
) 
SELECT * 
FROM cte_name;

Nach der Klammer, die die CTE-Abfrage einschließt, folgt eine herkömmliche SQL-Anweisung, die sich auf den CTE mit seinem Namen bezieht, so als wäre er ein View. Die Anweisung kann ein DML-Befehl (Data Manipulation Language) sein, z. B. SELECT, INSERT, DELETE oder UPDATE.

Wenn Sie eine Kurzanleitung für die MySQL-Syntax oder -Befehle benötigen, sollten Sie sich unser kostenloses MySQL Cheat Sheet vormerken. Es wird Ihnen helfen, Ihr Gedächtnis für gängige (und nicht so gängige) MySQL-Ausdrücke aufzufrischen.

Ein CTE in MySQL, der einen einzelnen Wert zurückgibt

Sehen wir uns ein Beispiel für einen CTE an, der in einer MySQL-Datenbank erstellt wurde. Wir verwenden Verkaufsinformationen und zeigen die Differenz zwischen dem Betrag jedes einzelnen Verkaufs und dem durchschnittlichen Verkaufsbetrag an.

Wir haben eine sales Tabelle mit detaillierten Verkaufsinformationen. In diesem Beispiel sind wir an drei Spalten interessiert: customer_id, sale_id und amount. Hier sind einige Beispieldaten:

Wir erstellen eine CTE namens sales_average, die einen einzigen Wert zurückgibt: den Durchschnitt aller Werte in der Spalte amount der Tabelle sales Tabelle (d. h. der Gesamtdurchschnitt aller Verkaufsbeträge). In dem externen SELECT, das die CTE aufruft, wird der von der CTE zurückgegebene Wert verwendet, um die Differenz zwischen dem Betrag der einzelnen Verkäufe und dem Gesamtdurchschnitt zu berechnen. Dadurch können wir die wichtigsten Verkäufe ermitteln:

WITH sales_average AS (
  SELECT AVG(amount) AS amount_avg
  FROM sales
)
SELECT 
  customer_id, 
  sale_id, 
  amount, 
  amount - amount_avg as difference
FROM sales, sales_average;

Wenn MySQL auf eine Abfrage wie die obige stößt, führt es zunächst die Abfrage in der WITH -Klausel aus und speichert ihre Ergebnisse vorübergehend unter dem Namen der CTE (sales_average). Dann führt er die externe SELECT unter Verwendung von sales_average wie eine gewöhnliche Tabelle aus; dies ergibt die endgültige Ergebnismenge.

Die Ausführung der gesamten Abfrage mit den oben beschriebenen Beispieldaten liefert die folgenden Ergebnisse:

CTE mit Gruppierungs- und Aggregatfunktionen

Schauen wir uns ein etwas komplexeres Beispiel an: Wir erstellen eine Abfrage, die eine CTE verwendet, um die ältesten Anrufe in einem Callcenter zu finden. Wir haben eine Tabelle namens calls die die caller_id und die call_time jedes Anrufs enthält (neben einigen anderen Daten wie der Anrufdauer, die wir hier nicht mit einbeziehen werden). Dies sind einige Zeilen aus der calls Tabelle:

Unsere CTE mit der Bezeichnung first_contact gibt das Datum und die Uhrzeit des ältesten Anrufs eines jeden Anrufers zurück. In der externen SELECT verknüpfen wir die CTE mit der Tabelle calls um alle Daten des jeweils ältesten Anrufs zurückzugeben. In diesem Beispiel (anders als im vorherigen) geben wir die Namen der Spalten, die von der CTE zurückgegeben werden, in der Deklaration an:

WITH first_contact (caller_id, first_call_time) AS (
	SELECT 
          caller_id, 
          MIN(call_time) AS first_call_time
	FROM calls
	GROUP BY caller_id
)
SELECT c.*
FROM calls AS c
INNER JOIN first_contact AS fc 
ON fc.caller_id = c.caller_id 
  AND fc.first_call_time = c.call_time;

Um die obige Abfrage auszuführen, erhält MySQL zunächst die Ergebnisse der Unterabfrage unter dem Namen first_contact. Dadurch werden alle unterschiedlichen Werte von caller_id zusammen mit dem jeweils ältesten Wert von call_time vorübergehend gespeichert. Dann verbindet die externe SELECT die temporären Ergebnisse der vorherigen Unterabfrage mit der calls Tabelle zusammen, so dass alle Daten ab dem ältesten Anruf jeder Anrufer-ID vorliegen.

Dies sind die Abfrageergebnisse:

Wenn Sie sich mit MySQL-CTEs vertraut machen wollen, um sich für ein Vorstellungsgespräch zu bewerben, lesen Sie diese Zusammenstellung von SQL-CTE-Vorstellungsfragen, um zu sehen, was auf Sie zukommen wird.

Mehrere und verschachtelte CTEs

In MySQL kann eine einzelne SQL-Anweisung mehrere CTEs enthalten. Unabhängig davon, wie viele CTEs Sie in einer Abfrage definieren, wird das Wort WITH nur einmal am Anfang der Anweisung verwendet; die CTE-Definitionen werden durch Kommata getrennt.

Verwendung mehrerer CTEs in einer einzigen Abfrage

Im folgenden Beispiel haben wir eine Tabelle namens customers in der das Gebiet gespeichert ist, in dem sich jeder Kunde befindet:

Angenommen, wir möchten die Informationen in dieser Tabelle in Verbindung mit der Tabelle sales Tabelle. Konkret wollen wir die Umsatzsummen der Kunden aus den Gebieten 'WEST' und 'OST' ermitteln.

Zu diesem Zweck definieren wir zwei CTEs, die es uns ermöglichen, nur die Kunden in den für uns interessanten Gebieten zu filtern. In der externen Anweisung SELECT kombinieren wir dann die Daten aus den beiden CTEs mit der Tabelle sales Tabelle, um die durchschnittlichen Verkaufszahlen für jedes dieser Gebiete zu erhalten:

WITH customers_west AS (
	SELECT *
	FROM customers
	WHERE area = 'WEST'),
customers_east AS (
	SELECT *
	FROM customers
	WHERE area = 'EAST')
SELECT 
   cw.area, 
   AVG(sw.amount) AS amount_avg
FROM customers_west AS cw
INNER JOIN sales sw 
ON sw.customer_id = cw.customer_id

UNION

SELECT 
  ce.area, 
  AVG(se.amount)
FROM customers_east AS ce
INNER JOIN sales se 
ON se.customer_id = ce.customer_id;

Und hier ist das Ergebnis:

Verschachtelte CTEs

MySQL erlaubt es uns auch, CTEs zu verschachteln, so daß ein CTE auf ein zuvor definiertes CTE verweisen kann - und das alles innerhalb einer einzigen SQL-Anweisung.

Die Technik der Verschachtelung von CTEs erlaubt es uns, große, komplexe Abfragen in kleinere, besser zu handhabende Unterabfragen aufzuteilen. Auf diese Weise können wir uns schrittweise an die Lösung eines Problems herantasten und den letzten Schritt schließlich auf eine einfache SELECT reduzieren.

Im folgenden Beispiel verwenden wir zwei verschachtelte CTEs, um die Gesamtverkäufe gruppiert nach Land und Region zu ermitteln. Die Tabelle sales Tabelle enthält eine Länderspalte, in der das Land angegeben ist, in dem die einzelnen Verkäufe getätigt wurden.

Die Tabelle countries Tabelle enthält eine Regionenspalte, die die geografische Region angibt, zu der jedes Land gehört:

Um die Umsatzsummen nach Region zu erhalten, verwenden wir zwei CTEs, die schrittweise zur endgültigen Lösung führen:

WITH sales_by_country AS (
SELECT 
  country, 
  SUM(amount) AS total_sales_by_country
FROM sales
group BY country
),
sales_by_region AS (
SELECT 
  c.region, 
  SUM(s.total_sales_by_country) AS total_sales_by_region
FROM sales_by_country s
INNER JOIN countries c 
ON c.country = s.country
GROUP BY c.region
)
SELECT * 
FROM sales_by_region;

In der obigen Abfrage definieren wir zunächst ein CTE mit der Bezeichnung sales_by_country, das die Daten nach Ländern gruppiert und die Gesamtumsätze jedes Landes zurückgibt. Dann definieren wir eine zweite CTE mit der Bezeichnung sales_by_region, die die CTE sales_by_country mit der Ländertabelle verknüpft und die Umsatzsummen nach Regionen gruppiert. Schließlich wird die gesamte Abfrage mit einer einfachen SELECT aus der sales_by_region CTE gelöst.

Verwendung von MySQL CTEs mit anderen DML-Befehlen

In den vorangegangenen Beispielen wurden CTEs zur Lösung komplexer SELECT Abfragen verwendet, wobei deren Komplexität reduziert wurde, um sie schließlich mit einem einfachen SELECT zu lösen. CTEs können aber auch mit anderen Befehlen verwendet werden. In diesem Abschnitt wird gezeigt, wie CTEs in den Befehlen INSERT und UPDATE verwendet werden können.

Verwendung eines CTE mit INSERT

Angenommen, wir haben eine employees Tabelle mit den Spalten empl_id (INT), empl_name (VARCHAR) und salary (DECIMAL). Dann haben wir eine applicants Tabelle mit denselben Spalten wie employees plus die Spalte aptitude (TINYINT), die angibt, ob ein Bewerber den Eignungstest bestanden hat (aptitude = 1) oder nicht (aptitude = 0). Nur Bewerber, die den Test bestehen, sind qualifiziert, Mitarbeiter zu werden.

Periodisch werden die Zeilen der Tabelle applicants Tabelle, die in der Spalte aptitude den Wert 1 haben, in die employees Tabelle eingefügt werden. Dazu verwenden wir eine CTE mit dem Namen qualified_applicants, um die Bewerber zu filtern, die den Eignungstest bestanden haben, aber noch nicht Angestellte geworden sind. Die von der CTE qualified_applicants zurückgegebenen Daten sind die Quelldaten für die INSERT, die sie in die employees Tabelle aufnimmt.

INSERT INTO employees (empl_id, empl_name, salary)
WITH qualified_applicants (empl_id, empl_name, salary) AS (
SELECT 
    empl_id, 
    empl_name, 
    salary
FROM applicants AS a
WHERE a.aptitude = 1
AND NOT EXISTS 
       (SELECT * 
        FROM employees AS e 
        WHERE e.empl_id = a.empl_id)
)
SELECT 
  empl_id, 
  empl_name, 
  salary
FROM qualified_applicants;

Wenn eine CTE in Kombination mit einer INSERT verwendet wird, wird der gesamte Code der CTE, von der WITH -Klausel bis zur äußeren SELECT, hinter die INSERT INTO table (column1, column2, ...) Zeile geschrieben. Um diese Einfügung durchzuführen, führt MySQL zunächst die gesamte CTE-Anweisung aus und fügt dann deren Ergebnisse in die angegebene Tabelle ein.

Das Schöne an der obigen Anweisung ist, dass sie wiederholt ausgeführt werden kann, ohne dass die Gefahr besteht, doppelte Daten zu erzeugen oder einen Primärschlüssel zu verletzen. Das liegt daran, dass die Bedingung WHERE in der CTE-Definition eine Klausel enthält, die verhindert, dass INSERT versucht, Daten wieder einzufügen, die sich bereits in der employees Tabelle befinden.

Verwendung einer CTE mit UPDATE

So wie wir eine MySQL-CTE zum Einfügen von Zeilen in eine Tabelle verwendet haben, können wir auch eine CTE verwenden, um die Tabelle mit neuen Informationen zu aktualisieren. Im folgenden Beispiel werden wir sehen, wie ein CTE in Verbindung mit dem Befehl UPDATE verwendet wird.

Wir verwenden die gleiche employees Tabelle und eine neue salaries Tabelle mit den Spalten empl_id (INT) und salary (DECIMAL). In dieser Tabelle werden die aktualisierten Gehälter der einzelnen Mitarbeiter gespeichert. Die CTE gibt die Zeilen von salaries zurück, in denen das Gehalt dieses Mitarbeiters höher ist als in der Tabelle employees Tabelle für denselben Angestellten.

Hier ist die vollständige Abfrage:

WITH raised_salaries (empl_id, salary) AS (
	SELECT s.empl_id, s.salary
	FROM salaries s
	INNER JOIN employees e 
      ON e.empl_id = s.empl_id
	WHERE s.salary > e.salary
)
UPDATE employees e
INNER JOIN raised_salaries rs 
ON rs.empl_id = e.empl_id
SET e.salary = rs.salary;

Die Syntax, die für die Aktualisierung von Daten aus einer CTE erforderlich ist, folgt der allgemeinen Form des MySQL-Befehls UPDATE JOIN. Wenn er zusammen mit einem CTE verwendet wird, muss der Befehl UPDATE JOIN als Ersatz für den externen SELECT des CTEs verwendet werden. Der Name des CTE wird in die JOIN -Klausel gesetzt, um ihn mit der zu aktualisierenden Tabelle zu verbinden.

Natürlich können Sie die Felder der CTE nicht aktualisieren, sondern nur die Felder der Tabelle(n), die mit der CTE verbunden sind.

Wie die Anweisung INSERT, die wir zuvor verwendet haben, kann diese Kombination aus MySQL CTE und UPDATE wiederholt ausgeführt werden, ohne einen einzigen Buchstaben zu ändern. Jedes Mal, wenn sie ausgeführt wird, aktualisiert sie nur die Zeilen von employees mit einem niedrigeren Gehalt als dem in der salaries Tabelle.

Rekursive CTEs in MySQL

CTEs vereinfachen und verdeutlichen nicht nur komplexe Abfragen, sondern ermöglichen es auch, Rekursionen in MySQL zu implementieren. Da die Rekursion nur mit SQL-Anweisungen aufgelöst werden kann, müssen wir keine anderen Programmiersprachen verwenden. Dadurch können wir effizientere Abfragen schreiben.

Rekursive CTEs bestehen aus den folgenden Elementen

• Einem Ankerelement, das den/die Startwert(e) für die rekursive Sequenz liefert.
• Ein rekursives Element, das die Ergebnisse der vorherigen Iterationen aufnimmt und ihnen neue Informationen hinzufügt. Das (die) Ankerelement(e) und das (die) rekursive(n) Element(e) werden durch UNION -Klauseln kombiniert.
• Eine Abbruchbedingung, d. h. eine WHERE Klausel (die an das rekursive Element angehängt ist), die die Bedingung definiert, die das Ende des rekursiven Zyklus bestimmt.
• Ein Aufruf oder die externe SELECT, die sich namentlich auf den rekursiven CTE bezieht (wie bei jedem anderen CTE).

Ausführlichere Informationen finden Sie in diesem Artikel über rekursive CTEs.

Eine typische Anwendung von rekursiven Funktionen in der Programmierung ist die Erzeugung von Fibonacci-Zahlen bis zu einem bestimmten Wert. Fibonacci-Zahlen werden in so unterschiedlichen Bereichen wie der Entwicklung von Suchalgorithmen und der Simulation des Bevölkerungswachstums verwendet. In der folgenden rekursiven MySQL CTE erhalten wir die ersten 10 Zahlen der Fibonacci-Folge:

WITH RECURSIVE cte_fib AS (
SELECT 
  1 counter, 
  CAST(0 AS decimal) fibo, 
  CAST(0 AS decimal) prev

UNION ALL

SELECT 2, 1, 0

UNION ALL

SELECT 
  counter + 1, 
  fibo + prev, 
  fibo
FROM cte_fib
WHERE counter < 10
AND fibo > 0
)
SELECT counter, fibo
FROM cte_fib
ORDER BY counter;

Im obigen Beispiel wird das Ankerelement durch die beiden ersten Mitglieder der UNION gebildet, da sie die Anfangselemente der Fibonacci-Folge liefern (die ersten beiden Zeilen in den resultierenden Daten):

SELECT 
  1 counter, 
  CAST(0 AS decimal) fibo, 
  CAST(0 AS decimal) prev

UNION ALL

SELECT 2, 1, 0

Das rekursive Element ist der dritte Teil von UNION, weil es den Daten, die von der vorhergehenden Iteration desselben CTE zurückgegeben wurden, eine Zeile hinzufügt:

SELECT 
  counter + 1, 
  fibo + prev, 
  fibo
FROM cte_fib
WHERE counter < 10
AND fibo > 0

Und die Abbruchbedingung ist die WHERE Klausel innerhalb der Klammer:

WHERE counter < 10
AND fibo > 0

Sie besagt, dass die Rekursion aufhören soll, wenn der Zähler einen Wert von 10 erreicht. Und der Aufruf ist die SELECT außerhalb der Klammern, die den Zähler und die Fibonacci-Zahl für jede von der CTE zurückgegebene Zeile zurückgibt.

Es gibt viele andere Verwendungsmöglichkeiten für rekursive CTEs in MySQL. Sie können zum Beispiel verwendet werden, um Tabellen mit hierarchisch geordneten Informationen (z.B. Organigramme) oder Baum- oder Graphdatenstrukturen zu durchlaufen. Sie können sogar für so unkonventionelle SQL-Aufgaben wie das Zeichnen eines Weihnachtsbaums mit den Ergebnissen einer Abfrage verwendet werden.

MySQL CTEs in einer Kurzfassung

In diesem Artikel haben wir einen Rundgang durch die CTEs in MySQL gemacht. Wir haben gelernt, dass Common Table Expressions:

• Komplexe Abfragen vereinfachen, indem sie sie in kleinere, leichter zu verstehende Teile zerlegen und organisieren. Dies verbessert auch die Lesbarkeit von Abfragen.
• Sie helfen uns, effizienteren Code zu schreiben. Eine als CTE definierte Subquery kann in verschiedenen Teilen derselben Abfrage - sogar in anderen Subqueries - wiederverwendet werden, ohne dass sie wiederholt werden muss.
• Kapseln Sie die Logik, ohne unnötige Objekte zu erstellen. CTEs kapseln die Logik von Unterabfragen unter einem Namen ein, genau wie Views. Aber im Gegensatz zu Views implizieren sie nicht die Erstellung von mehrjährigen Objekten in der Datenbank.
• Rekursion implementieren. Dies ist einer der Hauptgründe für die Beliebtheit von CTEs, denn ohne sie ist es ziemlich kompliziert, Rekursion in MySQL zu implementieren.

Inzwischen sollten Sie eine Vorstellung davon haben, was man mit rekursiven CTEs in MySQL machen kann. Nach der Lektüre dieses Artikels werden Sie bereit sein, Ihr Wissen durch einige praktische Übungen zu CTEs zu festigen.

Aber was Sie in diesem Artikel gesehen haben, ist nur die Spitze des Eisbergs. Um das volle Potential von CTEs in MySQL zu entdecken, sollten Sie den Kurs Rekursive Abfragen in MySQL von LearnSQL.de besuchen. Mit seinen 114 interaktiven Übungen vermittelt dieser Kurs das gesamte Wissen, das Sie benötigen, um rekursive Abfragen in MySQL 8 zu beherrschen. Sie brauchen nicht einmal Zugang zu einem MySQL-Server zu haben; die Kursumgebung stellt alle notwendigen Werkzeuge zur Verfügung. Alles, was Sie brauchen, ist ein Webbrowser, eine Internetverbindung und Grundkenntnisse in SQL. Sie können sogar das Einführungsquiz kostenlos absolvieren, um sicherzustellen, dass der Kurs Ihren Anforderungen entspricht!

Zusammenfassend lässt sich sagen, dass CTEs ein leistungsfähiges Werkzeug in MySQL sind, vor allem dann, wenn die Sauberkeit und Lesbarkeit des SQL-Codes im Vordergrund steht. In multidisziplinären Teams, in denen erfahrene SQL-Datenbankingenieure mit Datenanalysten, Datenbankdesignern und DBAs zusammenarbeiten, sind CTEs ein wertvolles Hilfsmittel, um die Produktivität des Teams zu steigern und eine besser wartbare und langlebige Software zu erhalten.