Fensterfunktionen

Kurz gesagt: Fensterfunktionen mit OVER und PARTITION BY rechnen über eine Gruppe von Zeilen, ohne sie zusammenzufassen: Ranglisten, laufende Summen oder die vorige Zeile mit LAG.

Problem

Der Durchschnitt, aber auch die Zeilen

Den Durchschnittspreis jeder Kategorie bekommst du schon mit GROUP BY:

SQLDatenbank: Shop

SELECT category_id, ROUND(AVG(price), 2) AS category_avg
FROM products
GROUP BY category_id;

SELECT category_id, ROUND(AVG(price), 2) AS category_avg FROM products GROUP BY category_id;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Aber die Shopleitung will eine Tabelle mit allen Produkten und neben jedem den Durchschnitt seiner Kategorie, um zu sehen, wie weit jedes davon abweicht. GROUP BY hat die Produkte zusammengedrückt: Von 25 Zeilen bleiben 7. In der Lektion über korrelierte Unterabfragen hast du es mit einer Unterabfrage für jede Zeile gelöst. Es gibt einen kürzeren Weg.

Die Idee

OVER und PARTITION BY

SQLDatenbank: Shop

SELECT name, category_id, price,
  ROUND(AVG(price) OVER (PARTITION BY category_id), 2) AS category_avg
FROM products;

SELECT name, category_id, price, ROUND(AVG(price) OVER (PARTITION BY category_id), 2) AS category_avg FROM products;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

AVG(price) OVER (PARTITION BY category_id) berechnet den Durchschnitt über die Gruppe der Zeile, drückt die Zeilen aber nicht zusammen: Alle 25 Produkte sind da, jedes mit dem Durchschnitt seiner eigenen Kategorie.

OVER (…) sagt, über welche Zeilen gerechnet wird. Diese Gruppe von Zeilen heißt Fenster, und eine Funktion mit OVER heißt Fensterfunktion. PARTITION BY teilt die Zeilen wie GROUP BY in Gruppen, fasst sie aber nicht zusammen.

Ein Unterschied zur korrelierten Unterabfrage: Für die Geschenkkarte ist der Durchschnitt 20 €, nicht NULL. PARTITION BY steckt alle NULL in eine Gruppe, wie GROUP BY, während p2.category_id = p.category_id für NULL nicht wahr war.

Die Idee

Ein Platz in einer Rangliste

ROW_NUMBER() gibt die Nummer der Zeile im Fenster. Mit ORDER BY in OVER legst du fest, wie die Zeilen geordnet sind:

SQLDatenbank: Shop

SELECT name, category_id, price,
  ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place
FROM products;

SELECT name, category_id, price, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place FROM products;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

In jeder Kategorie beginnt die Nummerierung neu, beim teuersten Produkt: Das teuerste Produkt in „Küche“ (Kategorie 8) ist das Messerset mit Nummer 1.

Der natürliche nächste Schritt ist WHERE place = 1, um nur die ersten zu behalten. Aber:

SQLDatenbank: Shop

SELECT name, category_id, price,
  ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place
FROM products
WHERE place = 1;

SELECT name, category_id, price, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place FROM products WHERE place = 1;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Fensterfunktionen werden nach WHERE berechnet: Erst werden die Zeilen ausgewählt, dann werden sie in Fenster geordnet. Die Rangliste gehört also in einen Schritt, und der Filter steht außen.

Probier es aus

Das teuerste Produkt jeder Kategorie

Zeige für jede Kategorie ihren Namen (Spalte category), ihr teuerstes Produkt und den Preis (Spalten name und price). Das Feld nummeriert die Produkte schon. Pack es in WITH und nimm nur die ersten.

SQLDatenbank: Shop

SELECT name, category_id, price,
  ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place
FROM products;

SELECT name, category_id, price, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place FROM products;

Prüfen▶ Ausführen

Sieh es dir an

Geteilte Plätze

Jetzt eine Rangliste nach verkauften Stück in „Brot und Gebäck“ (Kategorie 3), mit den drei Funktionen nebeneinander:

SQLDatenbank: Shop

WITH sold AS (
  SELECT p.name, p.category_id, SUM(oi.quantity) AS sold
  FROM products p
  JOIN order_items oi ON oi.product_id = p.id
  GROUP BY p.id, p.name, p.category_id
)
SELECT name, sold,
  ROW_NUMBER() OVER (ORDER BY sold DESC, name) AS row_number,
  RANK() OVER (ORDER BY sold DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY sold DESC) AS dense_rank
FROM sold
WHERE category_id = 3;

WITH sold AS ( SELECT p.name, p.category_id, SUM(oi.quantity) AS sold FROM products p JOIN order_items oi ON oi.product_id = p.id GROUP BY p.id, p.name, p.category_id ) SELECT name, sold, ROW_NUMBER() OVER (ORDER BY sold DESC, name) AS row_number, RANK() OVER (ORDER BY sold DESC) AS rank, DENSE_RANK() OVER (ORDER BY sold DESC) AS dense_rank FROM sold WHERE category_id = 3;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

1. Die Banitsa mit Käse und das Weißbrot haben je 16 verkaufte Stück. Welchen Platz gibt RANK ihnen, und welchen gibt DENSE_RANK dem Butterbrötchen danach?

RANK: 1 und 1, das Brötchen ist 3.; DENSE_RANK: das Brötchen ist 2.RANK: 1 und 2; DENSE_RANK: 1 und 1Beide geben dem Brötchen Platz 3

Prüfen

• ROW_NUMBER gibt auch bei Gleichstand verschiedene Nummern. Wer von beiden zuerst kommt, entscheidet die zweite Sortierung (hier der Name). Ohne sie wird nichts versprochen.
• RANK gibt gleichen Zeilen denselben Platz und überspringt dann: 1, 1, 3.
• DENSE_RANK gibt denselben Platz und überspringt nichts: 1, 1, 2.

Für „die Top 3 Produkte“ wähl mit Absicht: Mit ROW_NUMBER kommen genau drei heraus, mit RANK alle, die sich einen Platz teilen.

Die Idee

Laufende Summe

Mit ORDER BY in OVER ist das Fenster jeder Zeile „vom Anfang bis hierher“. Wie viel wir seit dem 1. September bis zu jedem Tag verkauft haben (ohne die stornierten Bestellungen):

SQLDatenbank: Shop

WITH daily AS (
  SELECT o.ordered_at AS day, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.ordered_at >= '2026-09-01' AND o.status <> 'cancelled'
  GROUP BY o.ordered_at
)
SELECT day, ROUND(revenue, 2) AS revenue,
  ROUND(SUM(revenue) OVER (ORDER BY day), 2) AS running_total
FROM daily
ORDER BY day;

WITH daily AS ( SELECT o.ordered_at AS day, SUM(oi.quantity * oi.unit_price) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.ordered_at >= '2026-09-01' AND o.status <> 'cancelled' GROUP BY o.ordered_at ) SELECT day, ROUND(revenue, 2) AS revenue, ROUND(SUM(revenue) OVER (ORDER BY day), 2) AS running_total FROM daily ORDER BY day;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

running_total wächst Zeile für Zeile: Am 3. Oktober erreicht es 415,30 €, und am 19. Oktober sind es 480,30 €. Das heißt laufende Summe (running total).

Genauer, für Neugierige

Haben zwei Zeilen denselben Wert in ORDER BY, bekommen beide standardmäßig die Summe einschließlich der anderen. Hier ist jeder Tag eine Zeile, das kann also nicht passieren. Wenn du Bestellungen nach Datum ordnest und ein Tag zwei Bestellungen hat, ergänze eine zweite Sortierung wie ORDER BY ordered_at, id oder schreib ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ausdrücklich hin.

Probier es aus

Wie viele Kunden wir hatten

Zeige für jeden Kunden den Namen, das Anmeldedatum und wie viele Kunden der Shop an diesem Datum hatte, ihn eingeschlossen (Spalte customers_so_far).

SQLDatenbank: Shop

SELECT name, joined_at FROM customers
ORDER BY joined_at;

SELECT name, joined_at FROM customers ORDER BY joined_at;

Prüfen▶ Ausführen

Die Idee

LAG: die vorige Zeile

LAG(column) gibt den Wert aus der vorigen Zeile im Fenster. Wie viele Tage liegen zwischen Marias Bestellungen?

SQLDatenbank: Shop

SELECT id, ordered_at,
  LAG(ordered_at) OVER (ORDER BY ordered_at) AS previous,
  julianday(ordered_at) - julianday(LAG(ordered_at) OVER (ORDER BY ordered_at)) AS days
FROM orders
WHERE customer_id = 1;

SELECT id, ordered_at, LAG(ordered_at) OVER (ORDER BY ordered_at) AS previous, julianday(ordered_at) - julianday(LAG(ordered_at) OVER (ORDER BY ordered_at)) AS days FROM orders WHERE customer_id = 1;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

previous ist das Datum ihrer vorigen Bestellung. julianday macht aus einem Datum eine Tageszahl, die Differenz ist also die Zahl der Tage zwischen den beiden Bestellungen. Die erste Bestellung hat keine vorige und bekommt NULL. Die Gegenfunktion ist LEAD: die nächste Zeile.

Übung

Zwei Aufgaben

1. Das Schaufenster der Website zeigt aus jeder Kategorie zwei Produkte, die teuersten.

Zeige für jede Kategorie ihren Namen (Spalte category) und ihre zwei teuersten Produkte mit den Preisen (Spalten name und price).

SQLDatenbank: Shop

Prüfen▶ Ausführen

2. Das Marketing will wissen, wie lange jeder Kunde höchstens zwischen zwei seiner Bestellungen gewartet hat.

Zeige für jeden Kunden mit mindestens zwei Bestellungen den Namen und die längste Pause in Tagen zwischen zwei seiner aufeinanderfolgenden Bestellungen (Spalte longest_pause).

SQLDatenbank: Shop

Prüfen▶ Ausführen

Test

Teste dich

1. Was ist der Unterschied zwischen GROUP BY und OVER (PARTITION BY …)?

OVER funktioniert nur mit ORDER BYGROUP BY drückt jede Gruppe zu einer Zeile zusammen, OVER behält alle Zeilen und fügt jeder das Ergebnis hinzuEs gibt keinen

2. Warum gibt WHERE place = 1 einen Fehler, wenn place ROW_NUMBER() OVER (…) ist?

Weil ROW_NUMBER bei 0 anfängtWeil Fensterfunktionen nach WHERE berechnet werdenWeil place ein reserviertes Wort ist

3. Was ergibt SUM(revenue) OVER (ORDER BY day)?

Die Summe vom ersten Tag bis zum aktuellen: eine laufende SummeNur die Summe des aktuellen TagesDie Gesamtsumme in jeder Zeile

4. Was liefert LAG(ordered_at) in der ersten Zeile?

NULL, weil es keine vorige Zeile gibtDas Datum derselben ZeileEinen Fehler

Prüfen

Lektion von vorn beginnen

Verwandte Lektionen

• Baut auf: JOIN: zwei Tabellen in einer Abfrage · Unterabfragen · Korrelierte Unterabfragen · Eine Auswertung in Schritten: WITH
• Geht weiter in: Ereignisströme · Spaltenorientierte Datenbanken und Zeitreihen · Die Bäckerei, Etappe 6: der Bericht, die Kopie und DROP
• Aufgaben zum Thema: Unterabfragen und Fensterfunktionen