Eine Auswertung in Schritten: WITH

Kurz gesagt: WITH (Common Table Expression, CTE) gibt jedem Schritt einer Abfrage einen Namen, sodass sie von oben nach unten liest und ein Schritt mehrfach benutzt werden kann.

Problem

Eine Auswertung von innen nach außen

Der Inhaber will eine Liste der Kunden, die mehr als 100 € ausgegeben haben (ohne die stornierten Bestellungen), mit ihrer Anzahl an Bestellungen. Mit Unterabfragen in FROM kannst du das schon:

SQLDatenbank: Shop

SELECT c.name, t.orders, t.spent
FROM (
  SELECT customer_id, COUNT(*) AS orders, ROUND(SUM(total), 2) AS spent
  FROM (
    SELECT o.id, 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 <> 'cancelled'
    GROUP BY o.id, o.customer_id
  ) AS ot
  GROUP BY customer_id
) AS t
JOIN customers c ON c.id = t.customer_id
WHERE t.spent > 100
ORDER BY t.spent DESC;

SELECT c.name, t.orders, t.spent FROM ( SELECT customer_id, COUNT(*) AS orders, ROUND(SUM(total), 2) AS spent FROM ( SELECT o.id, 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 <> 'cancelled' GROUP BY o.id, o.customer_id ) AS ot GROUP BY customer_id ) AS t JOIN customers c ON c.id = t.customer_id WHERE t.spent > 100 ORDER BY t.spent DESC;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Das Ergebnis stimmt. Aber versuch zu sagen, was die Abfrage tut: Du musst bei den innersten Klammern anfangen und dich nach außen arbeiten. In einem Monat erkennst du sie selbst nicht wieder.

Die Idee

WITH

WITH gibt einem Schritt vor der Hauptabfrage einen Namen:

SQLDatenbank: Shop

WITH order_totals AS (
  SELECT o.id, 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 <> 'cancelled'
  GROUP BY o.id, o.customer_id
)
SELECT * FROM order_totals;

WITH order_totals AS ( SELECT o.id, 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 <> 'cancelled' GROUP BY o.id, o.customer_id ) SELECT * FROM order_totals;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

order_totals ist der Wert jeder Bestellung, die nicht storniert ist: 53 Zeilen. Nach WITH wird der Name wie eine Tabelle benutzt. Ein solcher benannter Schritt heißt CTE (Common Table Expression).

Es können mehrere Schritte sein, durch Kommas getrennt. Jeder sieht die Schritte vor sich:

SQLDatenbank: Shop

WITH order_totals AS (
  SELECT o.id, 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 <> 'cancelled'
  GROUP BY o.id, o.customer_id
),
customer_totals AS (
  SELECT customer_id, COUNT(*) AS orders, ROUND(SUM(total), 2) AS spent
  FROM order_totals
  GROUP BY customer_id
)
SELECT c.name, ct.orders, ct.spent
FROM customer_totals ct
JOIN customers c ON c.id = ct.customer_id
WHERE ct.spent > 100
ORDER BY ct.spent DESC;

WITH order_totals AS ( SELECT o.id, 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 <> 'cancelled' GROUP BY o.id, o.customer_id ), customer_totals AS ( SELECT customer_id, COUNT(*) AS orders, ROUND(SUM(total), 2) AS spent FROM order_totals GROUP BY customer_id ) SELECT c.name, ct.orders, ct.spent FROM customer_totals ct JOIN customers c ON c.id = ct.customer_id WHERE ct.spent > 100 ORDER BY ct.spent DESC;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Dieselbe Auswertung wie am Anfang, aber sie liest sich von oben nach unten: der Wert jeder Bestellung, dann eine Summe für jeden Kunden, dann nur die über 100 €. Es kommen sieben Kunden heraus, angeführt von Maria (375,70 € in 12 Bestellungen).

Probier es aus

Bestellungen über dem Durchschnitt

Zeige Nummer und Wert der Bestellungen (ohne die stornierten), deren Wert über dem durchschnittlichen Bestellwert liegt. Das Feld hat den Schritt order_totals schon.

SQLDatenbank: Shop

WITH order_totals AS (
  SELECT o.id, 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 <> 'cancelled'
  GROUP BY o.id, o.customer_id
)
SELECT id, ROUND(total, 2) AS total
FROM order_totals;

WITH order_totals AS ( SELECT o.id, 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 <> 'cancelled' GROUP BY o.id, o.customer_id ) SELECT id, ROUND(total, 2) AS total FROM order_totals;

Prüfen▶ Ausführen

Sieh es dir an

Wie lange der Name lebt

Ein Name aus WITH gilt nur für die Abfrage, zu der er gehört. Hier sind es zwei Abfragen: Die erste zählt die Bestellungen, die zweite fragt noch einmal nach order_totals:

SQLDatenbank: Shop

WITH order_totals AS (
  SELECT o.id, 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 <> 'cancelled'
  GROUP BY o.id, o.customer_id
)
SELECT COUNT(*) FROM order_totals;

SELECT * FROM order_totals;

WITH order_totals AS ( SELECT o.id, 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 <> 'cancelled' GROUP BY o.id, o.customer_id ) SELECT COUNT(*) FROM order_totals; SELECT * FROM order_totals;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

1. Warum findet die zweite Abfrage order_totals nicht?

Weil ein Name aus WITH nur in der Abfrage lebt, zu der er gehörtWeil ein Komma fehltWeil die erste Abfrage order_totals gelöscht hat

Prüfen

WITH legt keine Tabelle in der Datenbank an. Wenn du einen Schritt in vielen Auswertungen brauchst, gibt es dafür Views (VIEW), die wir im Modul über das Schema kennenlernen.

Die Idee

Wann WITH, wann eine Unterabfrage

Das Ergebnis ist dasselbe. Der Unterschied ist für den, der liest:

• eine kurze Bedingung wie price > (SELECT AVG(price) FROM products) ist am klarsten an Ort und Stelle, als Unterabfrage;
• eine Auswertung aus mehreren Schritten ist mit WITH klarer: Jeder Schritt hat einen Namen, der sagt, was er ist;
• ein Schritt, den du zweimal brauchst, wird einmal geschrieben.

Es hilft auch bei der Fehlersuche: Ersetze das letzte SELECT durch SELECT * FROM order_totals, und du siehst, was jeder Schritt liefert.

Genauer, für Neugierige

Ob die Datenbank jeden Schritt einzeln ausführt oder sie zu einer Abfrage verschmilzt, entscheidet die Datenbank. In PostgreSQL vor Version 12 wurde jedes WITH einzeln ausgeführt, ab Version 12 kann die Datenbank es in die Hauptabfrage einbauen. Wenn eine WITH-Abfrage einmal langsamer ist als dieselbe mit Unterabfragen, liegt der Grund oft dort.

Übung

Drei Aufgaben

1. Das Vertriebsteam will den Umsatz nach Stadt, um zu entscheiden, wo es werben soll.

Zeige die Stadt und den Umsatz aus den Bestellungen ihrer Kunden (ohne die stornierten), gerundet auf zwei Nachkommastellen. Beginne beim Schritt order_totals.

SQLDatenbank: Shop

WITH order_totals AS (
  SELECT o.id, 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 <> 'cancelled'
  GROUP BY o.id, o.customer_id
)
SELECT * FROM order_totals;

WITH order_totals AS ( SELECT o.id, 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 <> 'cancelled' GROUP BY o.id, o.customer_id ) SELECT * FROM order_totals;

Prüfen▶ Ausführen

2. Welche Kategorien haben mehr als 200 € eingebracht (ohne die stornierten Bestellungen)?

Zeige den Kategorienamen und seinen Umsatz (die Summe aus Menge mal Stückpreis), nur für die Kategorien mit mehr als 200 € Umsatz. Mach einen Schritt mit dem Umsatz nach category_id und verbinde ihn dann mit categories.

SQLDatenbank: Shop

Prüfen▶ Ausführen

3. Im Modul über das Verbinden hast du mit CROSS JOIN das Raster aus jeder Stadt und jeder Unterkategorie gebaut. Jetzt füll es aus: den Umsatz für jedes Paar, mit 0, wo es keine Verkäufe gibt.

Zeige die Stadt, den Namen der Unterkategorie (Spalte category) und den Umsatz (Spalte revenue) der Kunden dieser Stadt in dieser Unterkategorie (ohne die stornierten Bestellungen), gerundet auf zwei Nachkommastellen. Für Paare ohne Verkäufe ist der Umsatz 0. Das Feld hat das Raster schon.

SQLDatenbank: Shop

WITH grid AS (
  SELECT DISTINCT cu.city, cat.id AS category_id, cat.name AS category
  FROM customers cu
  CROSS JOIN categories cat
  WHERE cu.city IS NOT NULL AND cat.parent_id IS NOT NULL
)
SELECT * FROM grid;

WITH grid AS ( SELECT DISTINCT cu.city, cat.id AS category_id, cat.name AS category FROM customers cu CROSS JOIN categories cat WHERE cu.city IS NOT NULL AND cat.parent_id IS NOT NULL ) SELECT * FROM grid;

Prüfen▶ Ausführen

Test

Teste dich

1. Wie schreibst du zwei Schritte in ein WITH?

WITH a AS (…), b AS (…) SELECT …WITH a AS (…); b AS (…); SELECT …WITH a AS (…) WITH b AS (…) SELECT …

2. Was macht WITH order_totals AS (…) mit der Datenbank?

Es behält das Ergebnis bis zum Ende des TagesNichts in der Datenbank: Es gibt einem Ergebnis nur für diese Abfrage einen NamenEs legt eine Tabelle order_totals an, die bleibt

3. Was bringt dir WITH, was eine Unterabfrage in FROM nicht bringt?

Richtigere ErgebnisseDie Schritte lesen sich von oben nach unten, und ein Schritt kann mehrmals benutzt werdenEs funktioniert ohne GROUP BY

Prüfen

Lektion von vorn beginnen

Verwandte Lektionen

• Baut auf: Viele Tabellen und eine Tabelle mit sich selbst verbunden · UNION, INTERSECT, EXCEPT und CROSS JOIN · Unterabfragen · Korrelierte Unterabfragen
• Geht weiter in: Bäume und Folgen: WITH RECURSIVE · Fensterfunktionen · Suchen nach Bedeutung · Die Bäckerei, Etappe 3: die Fragen
• Aufgaben zum Thema: Unterabfragen und Fensterfunktionen