EMZETT.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;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;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;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;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 ZeileFIRST_VALUE,LAST_VALUE,NTILE(n)(in n Gruppen teilen)- Rahmen:
ROWS BETWEEN 2 PRECEDING AND CURRENT ROWfü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;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 BYteilt in Gruppen,ORDER BYbestimmt die Reihenfolge im FensterROW_NUMBER,RANK,DENSE_RANKfür RanglistenLAG/LEADfür Vergleiche mit Nachbarzeilen,sum() OVER (ORDER BY ...)für laufende Summen- Ergebnisse von Fensterfunktionen filterst du über eine CTE oder Unterabfrage
Aufgabe
Zeige zu jeder Bestellung den Rang des Preises innerhalb des jeweiligen Produkts.