Level 17: Fensterfunktionen: Ränge bilden

Lektion

GROUP BY fasst zusammen: Es verschmilzt die Zeilen einer Gruppe zu einer einzigen. Manchmal will man im Gegenteil alle Zeilen behalten und jeder eine Information hinzufügen, die von den anderen abhängt: ihren Rang, ihre Nummer, ihre Position in der Gruppe. Das ist die Aufgabe der Fensterfunktionen, die man am Wort OVER erkennt.

Das „Fenster“ ist die Menge der Zeilen, die die Funktion betrachtet, um den Wert der aktuellen Zeile zu berechnen. OVER (ORDER BY rating DESC) bedeutet: Betrachte alle Zeilen, absteigend nach Bewertung geordnet.

Drei Funktionen ordnen die Zeilen in eine Rangfolge. ROW_NUMBER() nummeriert 1, 2, 3…, ohne je eine Nummer zu wiederholen. RANK() gibt Gleichplatzierten denselben Rang und überspringt dann Plätze: 1, 2, 2, 4. DENSE_RANK() gibt Gleichplatzierten ebenfalls denselben Rang, aber ohne Lücke: 1, 2, 2, 3. In staff verdienen Farid und Iris beide 47000: RANK und DENSE_RANK setzen sie auf denselben Rang, ROW_NUMBER trennt sie.

Bei Gleichstand ordnet ROW_NUMBER die Gleichplatzierten in einer beliebigen Reihenfolge, die sich ändern kann. Füge eine Spalte zum Auflösen des Gleichstands hinzu (ORDER BY salary DESC, name), um ein stabiles Ergebnis zu erhalten.

PARTITION BY teilt die Zeilen in Gruppen auf, und die Berechnung beginnt in jeder Gruppe neu: RANK() OVER (PARTITION BY department ORDER BY salary DESC) ordnet die Angestellten innerhalb ihrer Abteilung. Jede Abteilung hat ihre eigene Nummer 1.

Das in OVER geschriebene ORDER BY dient der Berechnung, nicht der Anzeige: Um das Ergebnis zu sortieren, füge ein abschließendes ORDER BY hinzu.

Fensterfunktionen werden nach WHERE, GROUP BY und HAVING berechnet, direkt vor dem abschließenden ORDER BY. Ein WHERE kann also nicht nach einem Rang filtern: Er existiert noch nicht. Um „den Ersten jeder Abteilung“ zu behalten, berechnet man den Rang in einer CTE und filtert dann danach: Das ist die Methode „Top N pro Gruppe“.

Syntax

SELECT col, RANK() OVER (PARTITION BY gruppe ORDER BY wert DESC) AS rang
FROM meine_tabelle;

Kommentiertes Beispiel

SELECT title, rating, RANK() OVER (ORDER BY rating DESC) AS rk
FROM movies;

Jeder Film erhält seinen Rang nach Bewertung, und alle 10 Filme bleiben erhalten.

Ergebnis des Beispiels

Das Wichtigste

• OVER (…) kennzeichnet eine Fensterfunktion.
• ROW_NUMBER: eindeutige Nummern; RANK: Gleichstand, dann Lücke; DENSE_RANK: Gleichstand ohne Lücke.
• PARTITION BY = „innerhalb jeder Gruppe“.

Häufige Fallen

• WHERE RANK() OVER (…) = 1 ist nicht erlaubt: Gehe über eine CTE.
• Das ORDER BY in OVER ordnet die Zeilen für die Berechnung; es sortiert nicht das Endergebnis.
• ROW_NUMBER ohne Entscheidungsspalte kann bei Gleichstand von einer Ausführung zur nächsten einen anderen Gewinner liefern.

Weiterführend

NTILE(4) OVER (ORDER BY salary) verteilt die Zeilen auf 4 gleich große Gruppen (Quartile); PERCENT_RANK liefert die relative Position jeder Zeile, zwischen 0 und 1.

Die 5 Übungen des Levels

1. Geführt · Zeige den Titel und das Erscheinungsdatum der Songs, mit einer Spalte n, die sie vom ältesten (1) zum neuesten nummeriert. (Tabelle: songs)
2. Training · Zeige den Namen und das Gehalt der Mitarbeitenden mit ihrem RANK und ihrem DENSE_RANK, vom höchsten zum niedrigsten Gehalt. (Tabelle: staff)
3. Training · Zeige den Namen, die Abteilung und das Gehalt jeder Person mit ihrem Gehaltsrang innerhalb ihrer Abteilung (1 = bestbezahlt in der Abteilung). (Tabelle: staff)
4. Training · Zeige die bestbezahlte Person jeder Abteilung (Name, Abteilung, Gehalt). (Tabelle: staff)
5. Herausforderung · Zeige für jedes Rennen (race) die beiden schnellsten Läufer: Rennen, Name des Läufers und Zeit, sortiert nach Rennen und dann nach Platzierung. (Tabellen: results, runners)

In SpeedQL wird jede Abfrage sofort geprüft: Ihr Ergebnis wird mit dem erwarteten verglichen, dann läuft die Abfrage noch einmal auf einer versteckten Kontrolldatenbank. Zu jeder Übung gibt es schriftliche Hinweise, die du nur bei Bedarf aufrufst.