Sobald du eine Rangliste brauchst — „Top 10 Kunden", „Pro Kategorie das beste Produkt", „Position in der Sortierung" — sind Ranking-Window-Functions die richtige Wahl. Die drei klassischen ROW_NUMBER, RANK, DENSE_RANK sehen ähnlich aus, behandeln aber Gleichstände unterschiedlich. Hier der Vergleich.
Beispiel-Daten
CREATE TABLE orders (
id bigserial PRIMARY KEY,
customer text NOT NULL,
total numeric(10, 2) NOT NULL
);
INSERT INTO orders (customer, total) VALUES
('Alice', 299.95),
('Bob', 199.95),
('Carol', 199.95), -- gleichstand mit Bob
('David', 149.95),
('Eva', 99.95),
('Frank', 99.95); -- gleichstand mit EvaDrei Ranking-Funktionen im Vergleich
SELECT
customer,
total,
ROW_NUMBER() OVER (ORDER BY total DESC) AS row_num,
RANK() OVER (ORDER BY total DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY total DESC) AS dense_rnk
FROM orders;Output:
customer | total | row_num | rnk | dense_rnk
----------+--------+---------+-----+-----------
Alice | 299.95 | 1 | 1 | 1
Bob | 199.95 | 2 | 2 | 2
Carol | 199.95 | 3 | 2 | 2
David | 149.95 | 4 | 4 | 3
Eva | 99.95 | 5 | 5 | 4
Frank | 99.95 | 6 | 5 | 4Drei Verhalten bei Gleichstand:
| Funktion | Bei Gleichstand | Lücken? |
|---|---|---|
ROW_NUMBER() | unterschiedliche aufeinanderfolgende Nummern (zufällig zugeordnet) | nein |
RANK() | gleicher Rang | ja — nächste Position überspringt |
DENSE_RANK() | gleicher Rang | nein — fortlaufende Ränge |
Bei RANK() springt nach Bob/Carol (beide auf Platz 2) der nächste auf Platz 4 (zwei Plätze überspringen). Bei DENSE_RANK() ist der nächste auf Platz 3 (keine Lücke). ROW_NUMBER() zählt einfach durch, der Tiebreaker ist undeterministisch.
Wann welche?
| Use-Case | Empfehlung |
|---|---|
| Pagination / Top-N pro Gruppe — eindeutige Nummern nötig | ROW_NUMBER() |
| Klassische Sport-Rangliste (1., 1., 3.) | RANK() |
| „Welche Stufe" (1, 1, 2 — Stufenmodell) | DENSE_RANK() |
| Erste Zeile pro Gruppe extrahieren | ROW_NUMBER() = 1 (siehe unten) |
Top-N pro Gruppe — der Klassiker
Aufgabe: pro Kategorie die zwei höchst-bewerteten Produkte. Window mit PARTITION BY plus Filter:
SELECT *
FROM (
SELECT
category,
name,
rating,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY rating DESC) AS rn
FROM products
) ranked
WHERE rn <= 2
ORDER BY category, rn;Pro Kategorie wird neu nummeriert (PARTITION BY category). Innerhalb jeder Kategorie absteigend nach Rating sortiert. Outer Query filtert dann auf rn <= 2.
Wichtig: das WHERE rn <= 2 muss in einer äußeren Query stehen — Window Functions sind in WHERE der gleichen Query nicht erlaubt.
Bei Gleichständen: wenn du alle Produkte mit Top-2-Rating willst (auch wenn drei den gleichen Top-Wert haben), nutze RANK() <= 2 statt ROW_NUMBER().
NTILE(n) — gleichmäßige Buckets
NTILE(n) teilt die sortierte Liste in n Buckets gleicher Größe:
SELECT
customer,
total,
NTILE(4) OVER (ORDER BY total) AS quartile
FROM orders;Output:
customer | total | quartile
---------+--------+----------
Eva | 99.95 | 1 <- unterstes Quartil
Frank | 99.95 | 1
David | 149.95 | 2
Bob | 199.95 | 3
Carol | 199.95 | 3
Alice | 299.95 | 4 <- oberstes QuartilNTILE(4) = 4 Buckets (Quartile). NTILE(10) = Dezile, etc. Praktisch für „aufteilen in N gleich große Gruppen" — z. B. „Kunden nach Umsatz in 5 Tiers".
PERCENT_RANK und CUME_DIST
Für Percentile-Berechnungen:
SELECT
customer,
total,
ROUND(PERCENT_RANK() OVER (ORDER BY total)::numeric, 3) AS percent_rank,
ROUND(CUME_DIST() OVER (ORDER BY total)::numeric, 3) AS cume_dist
FROM orders;Output:
customer | total | percent_rank | cume_dist
----------+--------+--------------+-----------
Eva | 99.95 | 0.000 | 0.333
Frank | 99.95 | 0.000 | 0.333
David | 149.95 | 0.400 | 0.500
Bob | 199.95 | 0.600 | 0.833
Carol | 199.95 | 0.600 | 0.833
Alice | 299.95 | 1.000 | 1.000PERCENT_RANK()=(rank - 1) / (total_rows - 1)— wo in der VerteilungCUME_DIST()= Anteil der Zeilen mit Wert ≤ aktueller — kumulative Verteilung
Beide nehmen Werte zwischen 0 und 1 an. Typische Anwendung: „dieser Kunde gehört zu den Top 10 %".
FAQ
ROW_NUMBER() bei Gleichstand: wer kommt zuerst?
Wenn die ORDER BY-Spalte im Window Duplikate hat, ist die Reihenfolge zwischen den gleichen Werten nicht-deterministisch. Wer das eindeutig will: einen Tiebreaker im ORDER BY ergänzen, z. B. ORDER BY total DESC, id ASC. Sonst kann sich die Position bei jedem Run ändern.
WHERE row_number() ... geht nicht.
Window Functions werden nach WHERE ausgewertet, daher kann man sie dort nicht referenzieren. Lösung: in eine Subquery oder CTE packen, dann äußere Query mit WHERE. Eigentlich ein konzeptioneller Bug-Schutz von SQL — würde sonst zirkuläre Logik bedeuten.
RANK() bei Sport-Statistiken — der Klassiker.
Bei einer Skirennen-Wertung: zwei Fahrer auf Platz 1 (Tie), nächster auf Platz 3. Das ist RANK(). Mit DENSE_RANK() wäre der nächste auf Platz 2 — was bei Sport unüblich ist. „Wenn Lücken sein können" → RANK, „wenn nicht" → DENSE_RANK.
NTILE kann ungleiche Bucket-Größen liefern.
Wenn Zeilen-Anzahl nicht durch n teilbar ist, sind die ersten Buckets eine Zeile größer. Bei 7 Zeilen und NTILE(3): Bucket 1 hat 3 Zeilen, Buckets 2 und 3 haben je 2. Postgres-Doku ist dazu klar; wer denkt, es seien immer 3+3+1, irrt.
DISTINCT ON als Postgres-Alternative für „Top-1 pro Gruppe“.
Statt ROW_NUMBER() = 1 lässt sich „erste Zeile pro Gruppe" auch mit DISTINCT ON (group_col) plus ORDER BY group_col, sort_col DESC machen. Lesbarer für genau diesen Fall — funktioniert aber nur Postgres-spezifisch und nur für Top-1, nicht Top-N.
Performance: Ranking braucht Sort.
Ranking-Functions brauchen die Daten sortiert nach ORDER BY der Window-Klausel. Mit passendem Index (etwa auf (category, rating DESC)) kann der Sort entfallen. Bei großen Tabellen ist das oft der Unterschied zwischen Sub-Sekunde und mehreren Sekunden.
Weiterführende Ressourcen
Externe Quellen
- Window Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE
- Window Functions Tutorial
- SELECT – DISTINCT ON