Wie rechnet man getrennt je Gruppe?
Mit PARTITION BY teilen Sie die Zeilen einer Tabelle in Gruppen und rechnen in jeder Gruppe für sich, zum Beispiel einen Rang je Kurs.
Ein Frame legt dazu fest, welche Nachbarzeilen mitzählen. So bilden Sie einen gleitenden Durchschnitt über die letzten drei Wochen.
Am Ende dieser Lektion
Notiz
Ich kann in jeder Gruppe einen Rang vergeben und einen Durchschnitt über ein wanderndes Fenster bilden.
Schlüsselwörter
| Begriff | Was er bedeutet |
|---|---|
| Partition | eine Gruppe von Zeilen, in der eine Fensterfunktion für sich rechnet |
| Frame | die Zeilen um die aktuelle Zeile, die mitzählen |
| Gleitender Durchschnitt | der Schnitt der letzten paar Werte, der mitwandert |
| ROWS BETWEEN | legt den Frame nach der Zahl der Zeilen fest |
| QUALIFY | behält Zeilen nach dem Ergebnis einer Fensterfunktion |
Die Zellen, die Lernende ausfüllen, bleiben in dieser Vorschau leer.
Partition by gibt jeder Gruppe ihre eigene Rechnung
Ein Rang über alle Studierenden mischt die Kurse. Besser ist ein Rang je Kurs.
partition by kurs in der Klammer von over teilt die Zeilen nach Kurs. Dann zählt jede Partition von vorn: In jedem Kurs gibt es einen Platz 1.
Die Zeilen bleiben erhalten, nur die Rechnung läuft getrennt.
Ein Platz je Kurs
| kurs | studierende | punkte | platz |
|---|---|---|---|
| Datenbanken | Clara | 91 | 1 |
| Datenbanken | Anna | 88 | 2 |
| Statistik | Finn | 95 | 1 |
| Statistik | Greta | 83 | 2 |
Die Zellen, die Lernende ausfüllen, bleiben in dieser Vorschau leer.
Probieren Sie es: ein Platz je Kurs
-- Der Platz zählt in jedem Kurs von vorn.
select kurs, studierende, punkte,
rank() over (partition by kurs order by punkte desc) as platz
from pruefungen
order by kurs, platz;
-- Ersetzen Sie rank() durch row_number() und vergleichen Sie.
Qualify behält die ersten Plätze jeder Gruppe
Sie wollen nur die zwei Besten je Kurs. Die Bedingung platz <= 2 gehört aber nicht in where, denn where läuft vor der Fensterfunktion.
In DuckDB gibt es dafür qualify. Diese Klausel läuft nach der Fensterfunktion und filtert Zeilen nach dem Platz.
Sie können auch eine Unterabfrage um die Fensterfunktion setzen und dort mit where filtern.
Probieren Sie es: die zwei Besten je Kurs
-- qualify filtert nach der Fensterfunktion.
select kurs, studierende, punkte,
row_number() over (partition by kurs order by punkte desc) as platz
from pruefungen
qualify platz <= 2
order by kurs, platz;
-- Ändern Sie 2 in 1: Wer ist jeweils die Beste oder der Beste?
Ein häufiger Fehler
Warnung
Viele schreiben where platz <= 2 und wundern sich über den Fehler. Der Platz entsteht erst nach where. Nutzen Sie qualify oder eine Unterabfrage, wenn Sie nach einer Fensterfunktion filtern.
Ein Frame bestimmt, welche Zeilen mitzählen
Eine laufende Summe zählt alle Zeilen bis zur aktuellen. Oft wollen Sie weniger: nur die letzten drei Wochen.
Der Frame sagt das: rows between 2 preceding and current row heißt die aktuelle Zeile und die zwei davor. Mit avg entsteht ein gleitender Durchschnitt.
Am Anfang gibt es weniger als zwei Vorgänger. Dann zählt der Frame nur, was da ist.
Der Frame wandert mit
| woche | fahrten | Zeilen im Frame | gleitend |
|---|---|---|---|
| 1 | 300 | Woche 1 | 300 |
| 2 | 330 | Woche 1 bis 2 | 315 |
| 3 | 360 | Woche 1 bis 3 | 330 |
| 4 | 270 | Woche 2 bis 4 | 320 |
Die Zellen, die Lernende ausfüllen, bleiben in dieser Vorschau leer.
Probieren Sie es: der gleitende Durchschnitt
-- Der Schnitt der aktuellen Woche und der zwei davor.
select station, woche, fahrten,
avg(fahrten) over (
partition by station
order by woche
rows between 2 preceding and current row
) as gleitend
from wochen
order by station, woche;
-- Ändern Sie 2 in 1 und sehen Sie, wie die Kurve unruhiger wird.