Level 16: Mit WITH strukturieren (CTE)

Lektion

Wenn eine Abfrage mehrere Berechnungen aneinanderreiht, verschachteln sich die Unterabfragen, und das Lesen wird schwierig: Man muss von innen nach außen lesen. WITH erlaubt, dieselben Schritte von oben nach unten zu schreiben, wie ein Rezept.

WITH name AS (SELECT …) erzeugt eine benannte temporäre Tabelle, die die nachfolgende Abfrage wie eine echte Tabelle verwenden kann. Man nennt das eine CTE (Common Table Expression, „allgemeiner Tabellenausdruck“). Sie existiert nur für die Dauer der Abfrage: In der Datenbank wird nichts gespeichert.

Beispiel: WITH totals AS (SELECT seller, SUM(amount) AS total FROM sales GROUP BY seller) SELECT seller, total FROM totals WHERE total > 1200. Der erste Schritt berechnet den Umsatz jedes Verkäufers; der zweite filtert dieses Ergebnis mit einem einfachen WHERE, da total jetzt eine gewöhnliche Spalte von totals ist.

Man kann mehrere CTEs aneinanderreihen, durch Kommas getrennt, ohne WITH zu wiederholen: WITH a AS (…), b AS (… FROM a …) SELECT … FROM b. Jede CTE kann die vorhergehenden verwenden. Die abschließende Abfrage folgt nach der letzten, ohne Komma.

Eine CTE kann in derselben Abfrage mehrmals gelesen werden. Eine CTE monthly, die den Umsatz jedes Monats berechnet, kann sowohl im FROM als auch in einer Unterabfrage dienen, die den Durchschnitt dieser Umsätze berechnet, ohne die Berechnung neu zu schreiben.

Methodentipp: Baue eine CTE nach der anderen. Schreibe den ersten Schritt, führe ihn allein aus und prüfe sein Ergebnis, dann füge den nächsten Schritt hinzu. Ist das Endergebnis falsch, weißt du, in welchem Schritt du suchen musst.

Achte auf die Zeichensetzung: kein Semikolon zwischen einer CTE und dem Rest der Abfrage, es würde sie in zwei Teile schneiden.

Syntax

WITH schritt1 AS (
  SELECT …
),
schritt2 AS (
  SELECT …
  FROM schritt1
)
SELECT …
FROM schritt2;

Kommentiertes Beispiel

WITH totals AS (
  SELECT seller, SUM(amount) AS total
  FROM sales
  GROUP BY seller
)
SELECT seller, total
FROM totals
WHERE total > 1200;

Schritt 1: die Summe pro Verkäufer. Schritt 2: Wir filtern dieses Ergebnis wie eine gewöhnliche Tabelle.

Ergebnis des Beispiels

Das Wichtigste

• WITH name AS (abfrage) benennt einen Schritt.
• Mehrere CTE werden durch Kommas getrennt, ohne WITH zu wiederholen.
• Die Hauptabfrage folgt nach der letzten CTE.

Häufige Fallen

• Ein Semikolon nach der Klammer der CTE schneidet die Abfrage in zwei.
• Eine CTE existiert nur für die Dauer der Abfrage.

Die 5 Übungen des Levels

1. Geführt · Zeige mit einer CTE it, die die Mitarbeitenden der Abteilung 'IT' enthält, den Namen und das Einstellungsdatum derer, die nach 2018 eingestellt wurden (ab dem 1. Januar 2019). (Tabelle: staff)
2. Training · Berechne mit einer CTE den Durchschnitt jedes Schülers (student_id) und zeige dann die Schüler, deren Durchschnitt mindestens 13 beträgt, auf 2 Nachkommastellen gerundet (avg_grade). (Tabelle: grades)
3. Training · Zeige mit einer CTE done, die die Stunden der erledigten Aufgaben (status = 'done') pro Projekt berechnet, den Namen jedes Projekts und diese Stunden. (Tabellen: tasks, projects)
4. Training · Zeige mit einer CTE monthly, die die Verkaufssumme jedes Monats berechnet, die Monate, deren Summe über dem Durchschnitt der Monatssummen liegt. (Tabelle: sales)
5. Herausforderung · Berechne den Saldo jedes Kontos (Summe seiner Transaktionen) und dann das Gesamtvermögen jedes Inhabers (owner), vom reichsten zum ärmsten. Konten ohne Transaktion werden ignoriert. (Tabellen: transactions, accounts)

In SpeedQL wird jede Abfrage sofort geprüft: Ihr Ergebnis wird mit dem erwarteten verglichen, dann läuft die Abfrage noch einmal auf einer versteckten Kontrolldatenbank. Zu jeder Übung gibt es schriftliche Hinweise, die du nur bei Bedarf aufrufst.