11 SQL-Übungen zu allgemeinen Tabellenausdrücken

In diesem Artikel bieten wir Ihnen 11 Übungsaufgaben an, mit denen Sie Ihre Kenntnisse über Common Table Expressions (CTEs) in die Praxis umsetzen können. Zu jeder CTE-Übung gibt es eine Lösung und eine ausführliche Erklärung.

Ein Sprichwort besagt: "Jeden Tag ein kleiner Fortschritt bringt große Ergebnisse". Und das trifft zweifellos auf die SQL-Kenntnisse zu. So wie Sie regelmäßig ins Fitnessstudio gehen sollten, um Ihre Muskeln zu trainieren, sollten Sie häufig Übungen zu Common Table Expressions machen, um Ihre Abfragefähigkeiten für umfangreiche Datenanalysen zu trainieren.

CTEs (common table expressions) sind leistungsstarke SQL-Anweisungen. Sie ermöglichen es Ihnen, eine Unterabfrage innerhalb einer SQL-Abfrage vorübergehend zu definieren und ihr einen Namen zuzuweisen. Das bedeutet, dass innerhalb der Abfrage auf die CTE mit ihrem Namen verwiesen werden kann - so als wäre sie eine Tabelle.

Der Wert von CTEs

Neben den Namen gibt es noch weitere wichtige Unterschiede zwischen CTEs und Unterabfragen. CTEs sind praktisch für die Strukturierung komplexer Abfragen, wodurch sie leichter zu lesen, zu verstehen und zu debuggen sind. CTEs können auch wiederholt in derselben Abfrage verwendet werden, wodurch diese übersichtlicher wird. Diese Eigenschaften von CTEs machen sie ideal für die Datenanalyse, da in Abfragen zur Datenanalyse häufig einfache Daten-SELECTs mit gruppierten SELECTs mit Summen, Durchschnittswerten und anderen Aggregatfunktionen gemischt werden. Ohne CTEs könnten komplexe Abfragen praktisch unmöglich zu lesen sein oder die Erstellung von temporären Tabellen oder Ansichten erfordern, die die Datenbank unnötig mit vielen Objekten füllen.

CTEs sind auch sehr nützlich bei der Durchführung hochkomplexer Datenanalysen, ohne auf andere Programmiersprachen zurückgreifen zu müssen. Die Verwendung von Rekursionen zum Auffinden kritischer Pfade oder zum Durchlaufen abstrakter Datentypen (wie Bäume und Graphen) sind Beispiele für die Nützlichkeit von CTEs. Das Gleiche gilt für die Möglichkeit, verschachtelte Abfragen zu erstellen, die die Komplexität eines Problems schrittweise reduzieren, bis es zu einem einfachen SELECT wird.

Die Fähigkeit von CTEs, Abfragen lesbarer und prägnanter zu machen, werden Sie zu schätzen wissen, wenn Sie eine lange Abfrage noch Jahre nach dem Schreiben überprüfen müssen. Glücklicherweise erlauben die meisten modernen relationalen Datenbankmanagementsysteme (RDBMS) - einschließlich PostgreSQL, MySQL, SQL Server und Oracle - die Verwendung von CTEs.

Alle in diesem Artikel zusammengestellten Übungen stammen aus unserem Rekursive Abfragen Kurs entnommen. Es handelt sich dabei um ein ausführliches Tutorial zu Common Table Expressions in der Datenanalyse. Die 114 interaktiven Übungen behandeln einfache CTEs, verschachtelte CTEs und rekursive CTEs in einer geschätzten Gesamtzeit von 18 Stunden. Sie können auch mehr über CTEs erfahren, indem Sie CTEs Explained with Examples lesen.

Ein weiteres wichtiges Thema, das jeder Datenanalyst beherrschen sollte, sind SQL-Fensterfunktionen. Sehen Sie sich diese Reihe von SQL-Fensterfunktionen-Übungen an, um Ihre Fähigkeiten auf die Probe zu stellen.

Lassen Sie uns nun mit den Übungen zu den gemeinsamen Tabellenausdrücken beginnen. Wir beginnen mit einfachen CTEs und gehen dann zu verschachtelten und rekursiven CTEs über.

Einfache CTE-Übungen

Für diese CTE-Übungen verwenden wir eine Datenbank, die für die Verwaltung von Crowdfunding-Projekten entwickelt wurde. Dieses Schema besteht aus drei Tabellen:

1. supporter enthält Informationen über die Unterstützer, d. h. diejenigen, die Geld für die Projekte spenden.
2. project enthält Informationen über die Projekte, die Spenden von Unterstützern erhalten.
3. donation erfasst die Spenden der Unterstützer an die Projekte.

Die Tabelle supporter Tabelle speichert die id, first_name und last_name jedes Unterstützers im System. Schauen wir uns einige der Zeilen an:

Die Tabelle project speichert id, category, author_id und minimal_amount, die für den Start jedes Projekts benötigt werden. Dies sind einige der Zeilen:

Die Daten in der Spalte author_id verknüpfen jedes Projekt in der Tabelle project Tabelle mit einer Zeile der supporter Tabelle. Jeder Unterstützer, der über die Spalte author_id mit einem Projekt verbunden ist, ist der Autor dieses Projekts.

Schließlich enthält die donation die Tabelle id, supporter_id, den Spendenbetrag und die Spalte donated, die das Datum jeder Spende angibt.

Übung 1: Verwenden einer CTE, um summierte Daten zu erhalten

Übung: Ermitteln Sie die Projekt-ID, den Mindestbetrag und die Gesamtsumme der Spenden für Projekte, die Spenden über dem Mindestbetrag erhalten haben.

Lösung:

WITH project_revenue AS (
  SELECT
    project_id,
    SUM(amount) AS sum_amount
  FROM donation
  GROUP BY project_id
)
SELECT project.id, minimal_amount, sum_amount
FROM project_revenue
INNER JOIN project ON
project.id = project_revenue.project_id
WHERE sum_amount >= minimal_amount;

Erläuterung: Um diese Aufgabe zu lösen, verwenden wir ein CTE namens project_revenue, das die Spenden der einzelnen Projekte summiert. Dieses CTE hat zwei Spalten: id und sum_amount, wobei letztere die berechnete Summe der Spenden für jedes project_id ist. Nach der CTE-Definition verwenden wir eine SELECT -Anweisung, die die Tabelle project Tabelle mit der CTE verbindet. Für jedes Projekt, das Spenden erhalten hat, gibt die CTE seine id, minimal_amount und die Gesamtsumme der erhaltenen Spenden (sum_amount) zurück.

Die CTE project_revenue enthält nur Zeilen für Projekte, die Spenden erhalten haben, da sie Daten aus der Spendentabelle erhält. Die SELECT unterhalb der CTE-Definition zeigt ebenfalls nur Projekte an, die Spenden erhalten haben, da die INNER JOIN zwischen dem CTE und der project Tabelle. Und die Bedingung WHERE stellt sicher, dass nur Projekte angezeigt werden, bei denen der gespendete Betrag den Mindestbetrag übersteigt.

Wenn Sie das Gruppieren von Daten in SQL üben möchten, sehen Sie sich diese 10 GROUP BY-Übungen an. Probieren Sie diese fortgeschrittenen SQL-Übungen aus, um schneller zu SQL-Kenntnissen zu gelangen.

Übung 2: Mehrere CTEs in der gleichen Anweisung verwenden

Übung: Wählen Sie Unterstützer aus, die insgesamt mehr als 200 $ gespendet haben oder die mindestens zweimal gespendet haben.

Lösung:

WITH rich AS (
  SELECT
	s.id,
	first_name,
	last_name
  FROM supporter s
  JOIN donation d
	ON d.supporter_id = s.id
  GROUP BY s.id, first_name, last_name
  HAVING SUM(amount) > 200
),
frequent AS (
  SELECT
	s.id,
	first_name,
	last_name
  FROM supporter s
  JOIN donation d
	ON d.supporter_id = s.id
  GROUP BY s.id, first_name, last_name
  HAVING COUNT(d.id) > 1
)
SELECT
  id,
  first_name,
  last_name
FROM rich
UNION ALL
SELECT
  id,
  first_name,
  last_name
FROM frequent;

Erläuterung: In dieser Übung müssen wir zwei verschiedene Ergebnisse kombinieren, die wir durch Abrufen von Informationen aus den CTEs donation und supporter abrufen müssen: Unterstützer, die insgesamt mehr als 200 $ gespendet haben, und Unterstützer, die mehr als einmal gespendet haben. Diese Situation lässt sich ideal durch das Schreiben von zwei CTEs lösen, von denen eine den ersten Datensatz (rich) und die andere den zweiten Satz (frequent) abruft.

Die SQL-Syntax unterstützt das Schreiben mehrerer CTEs innerhalb desselben Befehls, was wir uns zur Lösung dieser Aufgabe zunutze gemacht haben. Indem jede Unterabfrage in einer anderen CTE platziert wird, ist die endgültige SELECT einfach die Vereinigung von zwei einfachen SELECTs - von denen jede direkt Daten von einer CTE abruft.

Verschachtelte CTEs

Obwohl kein RDBMS die Erstellung eines CTEs innerhalb eines anderen CTEs zulässt, sind verschachtelte CTEs erlaubt; dies ist der Fall, wenn ein CTE auf einen zuvor definierten CTE verweist, als wäre er eine Tabelle. Auf diese Weise schaffen CTEs verschiedene Abstraktionsebenen. Dies macht die endgültige Abfrage einfach und übersichtlich SELECT.

Für unsere verschachtelten CTE-Übungen verwenden wir das Tabellenschema eines Haustürverkaufsunternehmens. Dieses Schema hat drei Tabellen: salesman, daily_sales, und city. Die Tabelle salesman Tabelle enthält die Daten id, first_name, last_name und city_id für jeden Verkäufer. Dies sind einige der Zeilen der Tabelle:

Die Tabelle daily_sales Tabelle stellt die Gesamtverkäufe pro Tag und Verkäufer dar. Sie enthält die Spalten day, salesman_id, items_sold, amount_earned, distance, und customers. Die beiden letztgenannten Spalten zeigen die zurückgelegte Strecke und die Anzahl der Kunden, die von jedem Verkäufer pro Tag bedient wurden. Dies sind nur einige der Zeilen:

Schließlich haben wir die city Tabelle, die die id, name, country und region jeder Stadt speichert:

Übung 3: Verwenden von verschachtelten CTEs, um sich schrittweise auf ein Ergebnis zuzubewegen

Übung: Ermitteln Sie das Datum, die Stadt-ID, den Namen der Stadt und den Gesamtbetrag aller täglichen Verkäufe - gruppiert nach Datum und Stadt -, die den durchschnittlichen Tagesumsatz für alle Städte und alle Tage übersteigen.

Lösung:

WITH earnings_per_day_city AS (
  SELECT
	ds.day,
	c.id,
	c.name,
	SUM(amount_earned) AS total_earnings
  FROM salesman s
  JOIN daily_sales ds
	ON s.id = ds.salesman_id
  JOIN city c
	ON s.city_id = c.id
  GROUP BY ds.day, c.id, c.name
),
overall_day_city_avg AS (
  SELECT
	AVG(total_earnings) AS avg_earnings
  FROM earnings_per_day_city
)
SELECT
  day,
  id,
  name,
  total_earnings
FROM earnings_per_day_city, overall_day_city_avg
WHERE total_earnings > avg_earnings;

Erläuterung: Mit verschachtelten CTEs können wir ein Problem in Teile zerlegen und uns schrittweise der Lösung nähern. In dieser Übung müssen wir zunächst die Umsätze pro Tag und pro Stadt summieren. Dies geschieht mit dem ersten CTE, earnings_per_day_city.

Dann müssen wir den Durchschnitt aller summierten Verkäufe pro Tag und pro Stadt ermitteln. Dies geschieht mit der CTE overall_day_city_avg, die wiederum die Ergebnisse verwendet, die zuvor mit der CTE earnings_per_day_city summiert wurden. Diese zweite CTE gibt eine einzige Zeile mit den durchschnittlichen Verkäufen für alle Tage und alle Städte zurück.

In der abschließenden SELECT nehmen wir einfach die Daten aus den beiden CTEs (es ist nicht nötig, sie mit einer JOIN zu kombinieren, da earnings_per_day_city eine einzige Zeile zurückgibt) und fügen die Bedingung WHERE hinzu, dass der Gesamtumsatz des Tages und der Stadt größer sein muss als der Gesamtdurchschnitt.

Übung 4: Kombinieren von verschachtelten Abfragen und Unterabfragen

Übung: Ermitteln Sie das Datum, an dem die durchschnittliche Anzahl der bedienten Kunden pro Region am niedrigsten war, und zeigen Sie diesen Durchschnitt zusammen mit dem Datum an.

Lösung:

WITH sum_region AS (
  SELECT
	day,
	region,
	SUM(customers) AS sum_customers
  FROM salesman s
  JOIN daily_sales ds
	ON s.id = ds.salesman_id
  JOIN city c
	ON s.city_id = c.id
  GROUP BY day, region
),
avg_region AS (
  SELECT
	day,
	AVG(sum_customers) AS avg_region_customers
  FROM sum_region
  GROUP BY day
)
SELECT
  day,
  avg_region_customers
FROM avg_region
WHERE avg_region_customers = (SELECT
  MIN(avg_region_customers)
  FROM avg_region);

Erläuterung: Um diese Abfrage zu lösen, verwenden wir denselben sukzessiven Lösungsansatz wie in der vorherigen Übung, indem wir zunächst eine CTE erstellen, um die Gesamtzahl der bedienten Kunden pro Tag und pro Region zu erhalten, und dann eine weitere CTE, die auf der vorherigen basiert, um die täglichen Durchschnittswerte der bedienten Kunden pro Tag zu erhalten. In der abschließenden SELECT verwenden wir dann eine Unterabfrage, um das Minimum der durchschnittlichen Kundenzahl pro Tag zu ermitteln, und verwenden es in der WHERE Klausel als Vergleichswert, so dass die Abfrage den Tag zurückgibt, der diesem Minimum entspricht.

Wenn Sie die endgültige SELECT weiter aufschlüsseln möchten, können Sie anstelle einer Unterabfrage eine dritte CTE hinzufügen. Auf diese Weise wird die endgültige SELECT noch einfacher. Hier sehen Sie die neue (dritte) CTE und die äußere SELECT:

min_avg_region as (
  SELECT
    MIN(avg_region_customers) as min_avg_region_customers
  FROM avg_region
  )
SELECT
  day,
  avg_region_customers
FROM avg_region, min_avg_region
WHERE avg_region_customers = min_avg_region_customers;

Übung 5: Verwendung verschachtelter CTEs zur Berechnung komplexer Statistiken

Übung: Berechnen Sie für jede Stadt die durchschnittliche Gesamtentfernung, die von jedem Verkäufer zurückgelegt wurde. Berechnen Sie auch den Gesamtdurchschnitt aller Durchschnittswerte der Städte.

Lösung:

WITH distance_salesman_city AS (
  SELECT
	city_id,
	salesman_id,
	SUM(distance) AS sum_distance
  FROM daily_sales d
  JOIN salesman s
	ON d.salesman_id = s.id
  GROUP BY city_id, salesman_id
),
city_average AS (
  SELECT
	city_id,
	AVG(sum_distance) AS city_avg
  FROM distance_salesman_city
  GROUP BY city_id
)
SELECT AVG(city_avg)
FROM city_average;

Erläuterung: Die Vorteile von verschachtelten CTEs machen sich bemerkbar, wenn Sie statistische Berechnungen durchführen müssen, die aus mehreren aufeinanderfolgenden Schritten bestehen.

In diesem Fall ist das Endergebnis ein Gesamtdurchschnitt der Durchschnittswerte pro Stadt der Summen der Entfernungen für jede Stadt und jeden Verkäufer. Es handelt sich also um einen Durchschnitt von Durchschnittswerten von Summen. Mit CTEs können wir eine schrittweise Annäherung an das Ergebnis vornehmen, analog dazu, wie ein Datenwissenschaftler dies mit statistischen Formeln tun würde.

Übung 6: Verwendung verschachtelter CTEs zum Vergleich von Elementgruppen

Übung: Vergleichen Sie die durchschnittlichen Umsätze aller Verkäufer in den USA mit den durchschnittlichen Umsätzen aller Verkäufer im Rest der Welt.

Lösung:

WITH cities_categorized AS (
  SELECT
    id AS city_id,
    CASE WHEN country = 'USA' THEN country ELSE 'Rest of the World' END AS category
  FROM city
),
sales_category AS (
  SELECT
    category,
    salesman_id,
    SUM(items_sold) total_sales
  FROM daily_sales ds
  JOIN salesman s
    ON s.id = ds.salesman_id
  JOIN cities_categorized
    ON cities_categorized.city_id = s.city_id
  GROUP BY category, salesman_id
)

SELECT
  category,
  AVG(total_sales)
FROM sales_category
GROUP BY category;

Erläuterung: Im ersten CTE (cities_categorized) haben wir die Städte in zwei Gruppen unterteilt: Städte in den USA und Städte im Rest der Welt.

Im zweiten CTE werden die Informationen aus dem CTE cities_categorized mit daily_sales und den Verkäufern kombiniert, um nach den beiden Kategorien von Städten und Verkäufern gruppierte Umsatzsummen zu erhalten.

Im abschließenden SELECT wird einfach nach Städtekategorie gruppiert und der durchschnittliche Umsatz für jede der beiden Kategorien (US-Städte und Städte im Rest der Welt) ermittelt.

Rekursive CTEs

In der SQL-Programmierung sind rekursive CTEs allgemeine Tabellenausdrücke, die auf sich selbst verweisen. Wie rekursive Funktionen, die in anderen Programmiersprachen verwendet werden, basieren rekursive CTEs auf dem Prinzip, die Daten aus einem vorherigen Durchlauf zu nehmen, sie zu ergänzen oder zu ändern und die Ergebnisse an den nächsten Durchlauf weiterzugeben. Dies wird so lange fortgesetzt, bis eine Stop-Bedingung erfüllt ist, d. h. bis das Endergebnis vorliegt.

Rekursive CTEs müssen das Wort RECURSIVE nach dem Wort WITH enthalten. Die Funktionsweise rekursiver CTEs lässt sich am besten anhand eines einfachen Beispiels verstehen, wie in der folgenden Übung.

Übung 7: Rekursive CTEs zum Erzeugen von Sequenzen verwenden

Übung: Verwenden Sie die Rekursion, um alle ganzen Zahlen von 1 bis 10 aufzulisten.

Lösung:

WITH RECURSIVE ten_numbers(prev_number) AS (
  SELECT 1
  UNION ALL
  SELECT ten_numbers.prev_number + 1
  FROM ten_numbers
  WHERE prev_number < 10
)

SELECT *
FROM ten_numbers;

Erläuterung: Diese Abfrage verwendet die PostgreSQL-Notation für rekursive CTEs, die aus vier Teilen besteht:

1. Anchor member: Hier wird der Startpunkt der Rekursion definiert. Dieser Teil der Abfrage muss autonom gelöst werden können, ohne dass die Ergebnisse früherer Iterationen der gleichen CTE verwendet werden müssen.
2. Rekursives Mitglied: Dieser Teil wird so oft wie nötig wiederholt, wobei die Ergebnisse der vorherigen Iteration als Grundlage dienen.
3. Abbruchbedingung: Diese Bedingung wird nach jeder Wiederholung des rekursiven Teils ausgewertet; wenn sie erfüllt ist, wird die rekursive Schleife beendet. Wäre diese Bedingung nicht vorhanden oder würde sie immer ein wahres Ergebnis liefern, würde die Rekursion unbegrenzt fortgesetzt.
4. Aufrufen: Der Hauptunterschied zwischen dieser SELECT Abfrage und anderen CTE-Hauptabfragen besteht darin, dass diese SELECT als Auslöser für den Zyklus der rekursiven Ausführungen dient.

In dieser Übung gibt das Ankerelement einfach eine Zeile mit der Nummer 1 zurück. Das rekursive Mitglied nimmt die Zeile(n) der vorherigen Ausführung und fügt (über die UNION-Klausel) eine neue Zeile mit dem vorherigen Wert, erhöht um 1, an. Die Abbruchbedingung besagt, dass die Abfrage so lange iteriert, bis der erhaltene Wert gleich 10 ist.

Übung 8: Verwenden einer rekursiven CTE zum Durchlaufen einer hierarchischen Datenstruktur (Baum)

Für diese Übung verwenden wir die Tabelle employee mit den Spalten id, first_name, last_name und superior_id. Ihre Zeilen enthalten die folgenden Daten:

Übung: Zeigen Sie alle Daten für jeden Mitarbeiter sowie einen Text an, der den Pfad in der Unternehmenshierarchie angibt, der jeden Mitarbeiter vom obersten Chef (identifiziert durch den literalen Wert 'Boss)' trennt.

Lösung:

WITH RECURSIVE hierarchy AS (
  SELECT
    id,
    first_name,
    last_name,
    superior_id,
    'Boss' AS path
  FROM employee
  WHERE superior_id IS NULL
  UNION ALL
  SELECT
    employee.id,
    employee.first_name,
    employee.last_name,
    employee.superior_id,
    hierarchy.path || '->' || employee.last_name
  FROM employee JOIN hierarchy
  ON employee.superior_id = hierarchy.id
)

SELECT *
FROM hierarchy;

Erläuterung: Die Daten in der Tabelle employee Tabelle stellen eine hierarchische oder Baumstruktur dar, in der jede Zeile eine Spalte hat, die sie mit ihrem Vorgesetzten (einer anderen Zeile in derselben Tabelle) in Beziehung setzt. Die Zeile, die dem Firmenchef entspricht (der Wurzelknoten des Baums), ist diejenige, die in der Spalte superior_id einen Nullwert hat. Daher ist dies unser Ankerelement, um dieses rekursive CTE zu erstellen. Der Pfad dieses Ankerelements enthält einfach den literalen Wert "Boss".

Dann verbindet das rekursive Abfrageelement die vorherige Iteration der Hierarchie mit employeeund setzt die Bedingung, dass die Chefs der Mitarbeiter der aktuellen Iteration (superior_id) bereits in der Hierarchie enthalten sind. Das bedeutet, dass wir bei jeder Iteration eine weitere Ebene zur Hierarchie hinzufügen. Diese Ebene wird durch die Untergebenen der Mitarbeiter gebildet, die in der vorherigen Iteration hinzugefügt wurden. Die Verknüpfungsbedingung lautet also employee.superior_id = hierarchy.id.

Der Pfad eines jeden Mitarbeiters wird durch die Verkettung des Pfades seines Chefs (hierarchy.path, der bis zu 'Boss' führt) mit dem Nachnamen des Mitarbeiters der aktuellen Iteration zusammengesetzt, verbunden durch eine Zeichenkette, die einen Pfeil darstellt (hierarchy.path || '->' || employee.last_name).

Übung 9: Zeigen Sie den Pfad eines Baumes ausgehend vom Wurzelknoten

Übung: Zeigen Sie eine Liste an, die den Vor- und Nachnamen jedes Mitarbeiters (einschließlich des obersten Chefs) enthält, zusammen mit einem Text (dem Pfadfeld), der den Pfad des Baumes zwischen jedem Mitarbeiter und dem obersten Chef zeigt. Im Falle des obersten Chefs soll die Pfadspalte die last_name des Chefs anzeigen.

Lösung:

WITH RECURSIVE hierarchy AS (
  SELECT
	first_name,
	last_name,
	CAST(last_name AS text) AS path
  FROM employee
  WHERE superior_id IS NULL
  UNION ALL
  SELECT
	employee.first_name,
	employee.last_name,
	hierarchy.path || '->' || employee.last_name AS path
  FROM employee, hierarchy
  WHERE employee.superior_id = hierarchy.id
)

SELECT *
FROM hierarchy;

Erläuterung: Die Lösung dieser Übung ist der vorherigen Übung sehr ähnlich, mit der einzigen Ausnahme, dass der Pfadwert für den Wurzelknoten kein literaler Wert vom Typ TEXT ist; es handelt sich um einen last_name Wert in der employee Tabelle. Dies zwingt uns dazu, eine Datenkonvertierung durchzuführen, um einen Fehler zu vermeiden, wenn wir diese Abfrage ausführen.

Da die CTE eine UNION zwischen den von der Ankerkomponente zurückgegebenen Daten und den von der rekursiven Komponente zurückgegebenen Daten vornimmt, ist es zwingend erforderlich, dass beide Ergebnissätze die gleiche Anzahl von Spalten haben und dass die Datentypen der Spalten übereinstimmen.

Die Spalte last_name der employee Tabelle (im CTE-Ankermitglied als path bezeichnet) ist ein VARCHAR-Typ, während die Verkettung hierarchy.path || '->' || employee.last_name (im rekursiven Mitglied als path bezeichnet) automatisch eine TEXT-Spalte ergibt. Damit UNION nicht zu einem Fehler bei der Typübereinstimmung führt, ist es erforderlich, CAST(last_name AS text) in das Ankerelement aufzunehmen. Auf diese Weise werden die path Spalten beider Teile des CTE TEXT sein.

Übung 10: Mehrere rekursive Spalten verwenden

Übung: Führen Sie alle Daten für jeden Mitarbeiter sowie den Weg in der Hierarchie bis zum obersten Chef auf. Fügen Sie eine Spalte namens Abstand ein, die die Anzahl der Personen in der Hierarchie vom obersten Chef bis zum Mitarbeiter angibt. Für den obersten Chef ist der Abstand 0; für seine Untergebenen ist er 1; für die Untergebenen seiner Untergebenen ist er 2, usw.

Lösung:

WITH RECURSIVE hierarchy AS (
  SELECT
	id,
	first_name,
	last_name,
	superior_id,
	'Boss' AS path,
	0 AS distance
  FROM employee
  WHERE superior_id IS NULL
  UNION ALL
  SELECT
	employee.id,
	employee.first_name,
	employee.last_name,
	employee.superior_id,
	hierarchy.path || '->' || employee.last_name,
	hierarchy.distance + 1
 FROM employee, hierarchy
 WHERE employee.superior_id = hierarchy.id
)
SELECT *
FROM hierarchy;

Erläuterung: Da das CTE zwei rekursive Spalten enthält, muss für jede dieser Spalten ein Anfangswert im Ankerelement angegeben werden. In diesem Fall hat die Pfadspalte den Anfangswert 'Boss' (wie in Aufgabe 8) und die Abstandsspalte den Wert 0. Im rekursiven Element ergibt sich dann der Abstandswert aus der Addition von 1 zum Abstand der vorherigen Iteration.

Übung 11: Rekursive CTEs verwenden, um netzwerkartige Datenstrukturen zu durchlaufen

Für diese Übung verwenden wir zwei Tabellen: eine destination Tabelle (bestehend aus den Spalten id und name) und eine ticket Tabelle (bestehend aus den Spalten city_from, city_to und cost ). Die Tabelle destination Tabelle enthält die IDs und Namen einer Gruppe von Städten, während die ticket Tabelle die Kosten für Tickets zwischen den Städtepaaren in der Tabelle destination Tabelle (sofern solche Verbindungen bestehen).

Dies sind Beispieldaten aus beiden Tabellen (zuerst destinationund dann ticket):

Übung: Finden Sie die billigste Verbindung zwischen allen Städten in der destination Tabelle, ausgehend von Warschau. Die Abfrage muss die folgenden Spalten enthalten:

• path - Die Namen der Städte auf der Strecke, getrennt durch '->'.
• last_id - Die Kennung der letzten Stadt auf dieser Strecke.
• total_cost - Die Summe der Kosten für die Fahrkarten.
• count_places - Die Anzahl der besuchten Städte. Diese muss gleich der Gesamtzahl der Städte in destinationd.h. 4.

Lösung:

WITH RECURSIVE travel(path, last_id,
	total_cost, count_places) AS (
  SELECT
	CAST(name as text),
	Id,
	0,
	1
  FROM destination
  WHERE name = 'Warsaw'
  UNION ALL
  SELECT
	travel.path || '->' || c2.name,
	c2.id,
	travel.total_cost + t.cost,
	travel.count_places + 1
  FROM travel
  JOIN ticket t
	ON travel.last_id = t.city_from
  JOIN destination c1
	ON c1.id = t.city_from
  JOIN destination c2
	ON c2.id = t.city_to
  WHERE position(c2.name IN travel.path) = 0
)
SELECT *
FROM travel
WHERE count_places = 4
ORDER BY total_cost ASC;

Erläuterung: Die Methode zur Lösung dieser Aufgabe ist ähnlich wie bei der vorherigen Aufgabe. In diesem Fall gibt es jedoch keine direkte Reihenfolge für die Beziehung zwischen den Elementen in derselben Tabelle. Stattdessen werden die Beziehungen zwischen den Elementen der destination Tabelle in der Tabelle ticket Tabelle ausgedrückt, wobei jedes Paar verbundener Städte miteinander verbunden wird.

Die erste Zeile der Ergebnisse der obigen Abfrage zeigt die kostengünstigste Route an. Dies ist möglich, weil die äußere SELECT der Abfrage die Ergebnisse in aufsteigender Reihenfolge nach total_cost sortiert. Das äußere SELECT wiederum stellt sicher, dass die Anzahl der durchfahrenen Städte 4 beträgt, indem es die Bedingung setzt, dass count_places gleich 4 ist.

Das rekursive Glied sorgt dafür, dass bei jeder Iteration eine neue Stadt zur Route hinzugefügt wird, indem die Bedingung gesetzt wird, dass der Name der Stadt nicht bereits im Pfad enthalten ist (position(c2.name IN travel.path) = 0).

Da der Ausgangspunkt der Reise die Stadt Warschau ist, ist das Ankerelement des rekursiven CTE die Zeile der Tabelle destination Tabelle, in der der Name gleich "Warschau" ist. Beachten Sie, dass wir die Spalte name in den Datentyp TEXT konvertieren (wie in Übung 9), damit der Datentyp mit der entsprechenden Spalte im rekursiven CTE-Mitglied übereinstimmt.

Benötigen Sie weitere Übungen zu allgemeinen Tabellenausdrücken?

Wenn Sie die SQL-Übungen in diesem Artikel durchgearbeitet haben, wissen Sie jetzt, wie man allgemeine Tabellenausdrücke verwendet. Und Sie wissen, wie nützlich CTEs sein können. Diese Übungen stammen aus unserem Rekursive Abfragen Kurs, und es gibt noch mehr Übungen wie diese im vollständigen Kurs.

Wenn Sie wirklich gut in der Datenanalyse mit SQL werden wollen, sollten Sie sich mit LearnSQL.de beschäftigen. Wir empfehlen Ihnen, sich das Alle für immer SQL-Paket anzusehen. Damit haben Sie lebenslangen Zugang zu allen SQL-Kursen, die wir jetzt anbieten, sowie zu allen neuen Kursen, die wir später hinzufügen. Auf diese Weise können Sie immer wieder neue Fähigkeiten erlernen. Werden Sie noch heute Mitglied bei LearnSQL.de und bauen Sie sich eine große Zukunft in der Datenanalyse auf!