SQL: Fensterfunktionen

Kurz: GROUP BY fasst Zeilen zusammen. Fensterfunktionen berechnen Werte über eine Gruppe verwandter Zeilen, behalten aber jede Zeile. Syntax: funktion() OVER (PARTITION BY … ORDER BY …).

Teil des Kurses SQL

Kapitel 10 von 15 im Kurs SQL. Mit Fortschritt, Quiz und Zertifikat auf der Lernseite.

Aggregieren ohne Zeilen zu verlieren

GROUP BY fasst Zeilen zusammen. Fensterfunktionen berechnen Werte über eine Gruppe verwandter Zeilen, behalten aber jede Zeile. Syntax: funktion() OVER (PARTITION BY ... ORDER BY ...).

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT name, ort, punkte,
       sum(punkte) OVER (PARTITION BY ort) AS summe_ort,
       round(avg(punkte) OVER (), 1) AS schnitt_gesamt
FROM kunden
WHERE punkte IS NOT NULL
ORDER BY ort, name;

Ausgabe:

name  ort      punkte  summe_ort  schnitt_gesamt
----  -------  ------  ---------  --------------
Mia   Berlin   120     320        137.5
Zoe   Berlin   200     320        137.5
Tom   Hamburg  80      80         137.5
Eva   Köln     150     150        137.5

Ranglisten

Funktion	Verhalten bei Gleichstand
ROW_NUMBER()	fortlaufend, keine gleichen Nummern
RANK()	gleiche Platzierung, danach Lücke (1,2,2,4)
DENSE_RANK()	gleiche Platzierung, keine Lücke (1,2,2,3)

CREATE TABLE ergebnisse (name TEXT, punkte INTEGER);
INSERT INTO ergebnisse VALUES ('Mia',90),('Tom',85),('Zoe',90),('Ben',70),('Eva',85);
SELECT name, punkte,
       ROW_NUMBER() OVER (ORDER BY punkte DESC, name) AS zeile,
       RANK()       OVER (ORDER BY punkte DESC) AS rang,
       DENSE_RANK() OVER (ORDER BY punkte DESC) AS dichter_rang
FROM ergebnisse
ORDER BY punkte DESC, name;

Ausgabe:

name  punkte  zeile  rang  dichter_rang
----  ------  -----  ----  ------------
Mia   90      1      1     1
Zoe   90      2      1     1
Eva   85      3      3     2
Tom   85      4      3     2
Ben   70      5      5     3

Top-n pro Gruppe

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
WITH nummeriert AS (
    SELECT kunde_id, produkt, preis,
           ROW_NUMBER() OVER (PARTITION BY kunde_id ORDER BY preis DESC) AS nr
    FROM bestellungen
    WHERE kunde_id IS NOT NULL
)
SELECT kunde_id, produkt, preis FROM nummeriert WHERE nr = 1 ORDER BY kunde_id;

Ausgabe:

kunde_id  produkt  preis
--------  -------  -----
1         Laptop   999.0
2         Monitor  189.9
3         Laptop   999.0

Laufende Summen und Vergleich mit dem Vorgänger

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT datum, preis,
       sum(preis) OVER (ORDER BY datum, id) AS laufende_summe,
       LAG(preis) OVER (ORDER BY datum, id) AS vorheriger,
       preis - LAG(preis, 1, preis) OVER (ORDER BY datum, id) AS differenz
FROM bestellungen
ORDER BY datum, id;

Ausgabe:

datum       preis  laufende_summe  vorheriger  differenz
----------  -----  --------------  ----------  ---------
2025-01-10  999.0  999.0                       0.0
2025-01-12  25.5   1024.5          999.0       -973.5
2025-02-01  189.9  1214.4          25.5        164.4
2025-02-15  49.0   1263.4          189.9       -140.9
2025-03-03  25.5   1288.9          49.0        -23.5
2025-03-20  999.0  2287.9          25.5        973.5
2025-03-21  5.0    2292.9          999.0       -994.0

• LAG(x) / LEAD(x): Wert der vorherigen / nächsten Zeile
• FIRST_VALUE, LAST_VALUE, NTILE(n) (in n Gruppen teilen)
• Rahmen: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW für gleitende Durchschnitte

CREATE TABLE messwerte (tag INTEGER, wert REAL);
INSERT INTO messwerte VALUES (1,10),(2,20),(3,30),(4,40),(5,50);
SELECT tag, wert,
       avg(wert) OVER (ORDER BY tag ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS gleitend_3
FROM messwerte;

Ausgabe:

tag  wert  gleitend_3
---  ----  ----------
1    10.0  10.0
2    20.0  15.0
3    30.0  20.0
4    40.0  30.0
5    50.0  40.0

Merke

• Fensterfunktionen berechnen über Gruppen, ohne Zeilen zusammenzufassen
• PARTITION BY teilt in Gruppen, ORDER BY bestimmt die Reihenfolge im Fenster
• ROW_NUMBER, RANK, DENSE_RANK für Ranglisten
• LAG/LEAD für Vergleiche mit Nachbarzeilen, sum() OVER (ORDER BY ...) für laufende Summen
• Ergebnisse von Fensterfunktionen filterst du über eine CTE oder Unterabfrage

Übungsaufgabe

Zeige zu jeder Bestellung den Rang des Preises innerhalb des jeweiligen Produkts.

Quiz zur Selbstkontrolle

Was ist der Unterschied zu GROUP BY?

• Fensterfunktionen behalten jede Zeile (richtig)
• Fensterfunktionen sind langsamer
• Es gibt keinen
• Sie fassen Zeilen zusammen

Was liefert RANK bei zwei Erstplatzierten als nächsten Rang?

• 3 (richtig)
• 2
• 1
• 4

Wie bekommst du den Wert der vorherigen Zeile?

• LAG() (richtig)
• LEAD()
• PREV()
• BEFORE()

Weiter im Kurs

Zurück: Unterabfragen, CTEs und Mengenoperationen

Weiter: Indizes und Performance

Alle Kapitel: SQL im Überblick