CTE: Mehrstufige Abfragen mit WITH

Echte Abfragen nehmen tendenziell zu: drei JOINs, ein GROUP BY, eine Unterabfrage, eine weitere Unterabfrage ... und sie werden unlesbar. CTEs ("Common Table Mit „Ausdrücken“, der WITH-Klausel) können Sie einem einen Namen geben Zwischenschritt und verwenden Sie ihn so, als wäre er eine Tabelle in der Hauptabfrage.

Grundlegende Syntax

WITH nome_cte AS (
  SELECT …    -- query interna
)
SELECT …      -- query principale, usa nome_cte come fosse una tabella
FROM   nome_cte;

Ein CTE ist nur für die darauf folgende Abfrage gültig: Er bleibt nicht bestehen die Ausführung.

Beispiel

Finden Sie die 3 Kunden, die am meisten ausgegeben haben (Summe der gelieferten Bestellungen):

WITH total_spent AS (
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price) AS total
  FROM   orders      o
  JOIN   order_items oi ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY o.customer_id
)
SELECT c.email,
       s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
ORDER BY s.total DESC
LIMIT  3;

Lesen Sie auf Englisch: _ „Berechnen Sie zuerst die Ausgaben pro Kunde und schließen Sie sich dann an.“ Kunden und holen Sie sich die Top 3"_. Ohne einen CTE bräuchten Sie einen Inline Unterabfrage im FROM (legal, aber lauter).

Mehrere CTEs in Kaskade

Sie können mehrere durch Kommas getrennte CTEs definieren, auf die jeweils verwiesen werden kann zu den vorherigen:

WITH total_spent AS (
  SELECT customer_id, SUM(quantity * unit_price) AS total
  FROM   order_items oi
  JOIN   orders      o ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY customer_id
),
average AS (
  SELECT AVG(total) AS total_average
  FROM   total_spent
)
SELECT c.email, s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
CROSS JOIN average
WHERE  s.total > average.total_average
ORDER BY s.total DESC;

CROSS JOIN average, weil average immer eine einzelne Zeile hat: die Das kartesische Produkt hängt einfach total_average an jede Zeile des Ergebnisses an.

CTE vs. Unterabfrage: Wann welche verwenden

CTEs glänzen, wenn:

• Dieselbe Unterabfrage wird in der Hauptabfrage zwei- oder mehrmals verwendet.
• Sie möchten die Struktur in benannten Schritten explizit machen (selbstdokumentierend);
• Sie sind die Voraussetzung für rekursive CTEs (WITH RECURSIVE, nächste Lektion in diesem Modul).

Eine Inline-Unterabfrage ist prägnanter für einen Schritt, der nur einmal verwendet wird.

Probieren Sie es aus

Wiederholungsübung