Abfragen in Schritten und Rechnen über Zeilen: CTEs und Fensterfunktionen

WITH: eine abgeleitete Tabelle mit Namen

Die letzte Lektion endete mit einer abgeleiteten Tabelle in FROM. Hier ist dieselbe Abfrage als Common Table Expression.

WITH category_stats AS (
  SELECT cat.name AS category,
         COUNT(*) AS products,
         ROUND(AVG(p.price), 2) AS average
  FROM products p
  JOIN categories cat ON cat.id = p.category_id
  GROUP BY cat.id
)
SELECT *
FROM category_stats
WHERE products >= 10
ORDER BY average DESC;

Identisches Ergebnis, und jetzt liest es sich vorwärts: erst category_stats definieren, dann verwenden. Der Name gilt nur für diese eine Anweisung – nichts wird angelegt, nichts muss aufgeräumt werden.

Mehrere Schritte nacheinander

CTEs lassen sich verketten, und jede darf die vorherigen benutzen, sodass aus einer verschachtelten Abfrage eine Folge benannter Schritte wird.

WITH paid_orders AS (
  SELECT id, customer_id, total
  FROM orders
  WHERE status IN ('paid', 'shipped', 'delivered')
),
per_customer AS (
  SELECT customer_id,
         COUNT(*) AS orders,
         ROUND(SUM(total), 2) AS revenue
  FROM paid_orders
  GROUP BY customer_id
)
SELECT c.first_name || ' ' || c.last_name AS customer,
       c.country,
       pc.orders,
       pc.revenue
FROM per_customer pc
JOIN customers c ON c.id = pc.customer_id
ORDER BY pc.revenue DESC
LIMIT 20;

Drei benannte Schritte: die Bestellungen eingrenzen, je Kunde zusammenfassen, den Kundennamen anhängen. Als verschachtelte Unterabfragen müsste man dieselbe Abfrage von innen nach außen lesen; so geschrieben ist sie eine Liste von Schritten in der Reihenfolge, in der sie passieren. Wird eine Abfrage schwer zu verfolgen, ist das Aufteilen in CTEs meist der erste Versuch.

Fensterfunktionen: aggregieren, ohne zusammenzufassen

GROUP BY macht aus vielen Zeilen eine. Eine Fensterfunktion rechnet über eine Menge von Zeilen und behält jede Zeile.

SELECT p.name,
       p.price,
       RANK()       OVER (ORDER BY p.price DESC) AS price_rank,
       DENSE_RANK() OVER (ORDER BY p.price DESC) AS dense_price_rank,
       ROW_NUMBER() OVER (ORDER BY p.price DESC) AS row_no
FROM products p
ORDER BY p.price DESC
LIMIT 20;

OVER (ORDER BY p.price DESC) ist das Fenster: die Zeilen, die diese Berechnung betrachtet, und in welcher Reihenfolge. Drei Funktionen, drei verschiedene Antworten bei Preisgleichstand:

• ROW_NUMBER() – 1, 2, 3, 4. Immer verschieden, Gleichstände willkürlich aufgelöst.
• RANK() – 1, 2, 2, 4. Gleichstände teilen sich einen Rang, der nächste Wert überspringt.
• DENSE_RANK() – 1, 2, 2, 3. Gleichstände teilen sich einen Rang, nichts wird übersprungen.

Welche Sie wollen, hängt von der Frage ab. "Gib mir genau zehn Zeilen" will ROW_NUMBER; "wer wurde Zweiter" will einen der Ränge.

Top N je Gruppe

Ohne Fensterfunktionen bräuchte das eine korrelierte Unterabfrage, die zählt, wie viele Produkte der Kategorie teurer sind. PARTITION BY beginnt die Nummerierung für jede Gruppe neu.

WITH ranked AS (
  SELECT cat.name AS category,
         p.name AS product,
         p.price,
         ROW_NUMBER() OVER (PARTITION BY cat.id ORDER BY p.price DESC) AS rank_in_category
  FROM products p
  JOIN categories cat ON cat.id = p.category_id
)
SELECT category, product, price
FROM ranked
WHERE rank_in_category <= 3
ORDER BY category, rank_in_category;

Laufende Summen

Fügen Sie dem Fenster ein ORDER BY hinzu, und jede Zeile sieht alles bis einschließlich sich selbst.

WITH monthly AS (
  SELECT substr(ordered_at, 1, 7) AS month,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE status IN ('paid', 'shipped', 'delivered')
    AND ordered_at >= '2025-01-01'
  GROUP BY month
)
SELECT month,
       revenue,
       ROUND(SUM(revenue) OVER (ORDER BY month), 2) AS running_total
FROM monthly
ORDER BY month;

Die CTE erzeugt eine Zeile je Monat; SUM(revenue) OVER (ORDER BY month) summiert diese Liste hinunter auf. Derselbe Kniff liefert laufende Zählungen, kumulierte Prozentsätze und Burn-down-Kurven – alles, wo die Antwort für eine Zeile von den Zeilen davor abhängt.

Mit der vorherigen Zeile vergleichen

WITH monthly AS (
  SELECT substr(ordered_at, 1, 7) AS month,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE status IN ('paid', 'shipped', 'delivered')
    AND ordered_at >= '2025-01-01'
  GROUP BY month
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS previous_month,
       ROUND(revenue - LAG(revenue) OVER (ORDER BY month), 2) AS change
FROM monthly
ORDER BY month;

LAG(revenue) greift im Fenster eine Zeile zurück, LEAD eine nach vorn. Die Veränderung zum Vormonat, die sonst einen Self-Join mit Versatz bräuchte, ist jetzt eine Zeile. Das previous_month der ersten Zeile ist NULL – davor gibt es nichts – und damit ist auch die Veränderung NULL, was die ehrliche Antwort ist statt einer irreführenden Null.

Unterstützung

Fensterfunktionen sind Standard und dort verfügbar, wo Sie ihnen wahrscheinlich begegnen: PostgreSQL, SQL Server und Oracle haben sie seit vielen Jahren, SQLite seit 3.25 (2018) und MySQL seit 8.0 (2018). MariaDB hat sie ab 10.2. MySQL 5.7 ist die eine Version, der Sie ohne sie noch begegnen dürften.

Was Sie jetzt können

Sie können Daten in praktisch jeder benötigten Form lesen. Die letzten beiden Lektionen behandeln die andere Hälfte der Arbeit: Daten sicher ändern und herausfinden, warum eine Abfrage langsam ist.