Antwort

Fensterfunktionen ermöglichen Berechnungen über ein "Fenster" von Zeilen, ohne diese in separate Zeilen zu gruppieren, was für Berichte und Analysen praktisch ist.

ROW_NUMBER() — weist innerhalb jeder Partition (Teilung) des Fensters eine eindeutige fortlaufende Nummer zu, wobei sie nach einem bestimmten Kriterium sortiert werden. Es kann Nummernsprünge geben, wenn die Werte in ORDER BY gleich sind.
RANK() — weist den Zeilen mit dem gleichen Wert in ORDER BY den gleichen Rang zu, überspringt jedoch die Nummern der folgenden Elemente (es gibt eine Lücke).
DENSE_RANK() — weist ebenfalls den Zeilen mit dem gleichen Wert den gleichen Rang zu, jedoch folgen die Nummern ohne Lücken aufeinander.

Beispiel:

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS num,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Tabelle:

name	salary
Vasja	10000
Petja	10000
Mascha	9000

Ergebnis:

name	salary	num	rank	dense_rank
Vasja	10000	1	1	1
Petja	10000	2	1	1
Mascha	9000	3	3	2

Stolpersteine:

Eine falsch gewählte ORDER BY-Klausel kann zu einer falschen Sortierung führen.
(row_number) Wenn kein eindeutiges Feld in ORDER BY gewählt wird, gibt es keine Garantie für eine stabile Reihenfolge.
Die Verwendung ohne PARTITION BY bei Bedarf einer Aufteilung führt zu einer falschen Nummerierung über die gesamte Menge von Zeilen.

Fangfrage

Wenn kein PARTITION BY im Fenster der Funktion angegeben wird, wie erfolgt dann die Nummerierung der Zeilen in ROW_NUMBER()?

Antwort: Die gesamte Datenabfrage wird als eine Partition betrachtet. Das heißt, die Nummerierung erfolgt fortlaufend über alle Zeilen, ohne Berücksichtigung irgendwelcher Gruppierungen.

Beispiel:

SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) FROM employees;
-- Alle Mitarbeiter erhalten eine eindeutige Nummer, beginnend mit 1, ohne Aufteilung nach Abteilungen

Geschichte Nr.1

Im BI-Bericht wurde vergessen, PARTITION BY nach Abteilung anzugeben. Alle Mitarbeiter des Unternehmens erhielten eine fortlaufende Nummerierung, während die Aufgabe darin bestand, die Besten innerhalb jeder Abteilung zu identifizieren. Infolgedessen entstand ein fehlerhaftes TOP-N der Mitarbeiter nach Abteilungen.

Geschichte Nr.2

Der Entwickler wählte RANK() anstatt ROW_NUMBER(), um den "Besten" aus der Gruppe zu bestimmen — aber bei gleichen Werten wurden die gleichen Nummern zugewiesen, was zu impliziten Duplikaten der Führenden in der Analyse führte.

Geschichte Nr.3

Bei der Verwendung von DENSE_RANK() wurde nicht berücksichtigt, dass es Lücken in den Rängen verhindert, was den Bericht über die Anzahl der "einzigartigen" signifikanten Positionen beim Verkauf analysiert hat. Eine Überprüfung der Geschäftslogik zeigte einen Fehler in der Platzverteilung auf.

Wie funktionieren und unterscheiden sich die Fensterfunktionen ROW_NUMBER(), RANK(), DENSE_RANK() bei der Programmierung von Berichten in SQL? Welche Stolpersteine gibt es bei ihrer Verwendung?

Antwort

Fangfrage