PERCENT_RANK und CUME_DIST: Relative Rangordnung statt fixer Positionen

1. Warum absolute Ränge nicht immer die richtige Antwort sind

RANK und DENSE_RANK liefern eine absolute Position innerhalb einer sortierten Menge, also die Information, dass ein Wert der dritthöchste oder der siebte in der jeweiligen Partition ist. Diese Information ist wertvoll, solange die Größe der Gesamtmenge bekannt und stabil ist, etwa bei einer Top-Zehn-Liste mit fester Länge. Sobald sich aber die Anzahl der Zeilen von Abfrage zu Abfrage ändert, etwa weil ein Zeitraum, ein Filter oder eine Kategorie variiert, wird eine absolute Position schnell bedeutungslos, weil Rang zwölf bei hundert Zeilen etwas völlig anderes bedeutet als Rang zwölf bei zwölftausend Zeilen.

Genau hier setzen PERCENT_RANK und CUME_DIST an. Beide liefern statt einer Ganzzahl einen Wert zwischen null und eins, der die Position relativ zur Gesamtgröße der Partition ausdrückt. Ein Ergebnis von 0.95 bedeutet unabhängig von der absoluten Zeilenzahl immer dasselbe, nämlich dass sich der Wert im obersten Bereich der Verteilung befindet. Diese Normierung macht beide Funktionen besonders nützlich für Vergleiche über unterschiedlich große Gruppen hinweg, etwa beim Vergleich von Verkaufszahlen zwischen Filialen mit stark unterschiedlicher Kundenzahl.

2. Die PERCENT_RANK-Formel im Detail

PERCENT_RANK berechnet sich nach der Formel (rank minus eins) geteilt durch (Gesamtzahl der Zeilen in der Partition minus eins), wobei rank der Wert ist, den RANK für dieselbe Zeile liefern würde. Die erste Zeile einer Partition erhält damit immer den Wert null, unabhängig davon, wie viele Zeilen insgesamt vorhanden sind, weil rank für die erste Zeile stets eins ist und eins minus eins durch irgendetwas immer null ergibt. Die letzte Zeile erhält entsprechend immer den Wert eins, sofern keine Ties vorliegen, die diese Position vorziehen.

Wichtig ist, dass PERCENT_RANK wie RANK mit Ties umgeht, also gleiche Werte erhalten denselben Rang, und nachfolgende Ränge werden entsprechend übersprungen. Das bedeutet, dass bei vielen identischen Werten am oberen Ende der Verteilung mehrere Zeilen denselben PERCENT_RANK-Wert erhalten, während die nächste unterschiedliche Gruppe einen sichtbaren Sprung im Ergebnis zeigt. Bei einer Partition mit nur einer einzigen Zeile ist der Nenner null, in diesem Fall definiert der SQL-Standard den Rückgabewert als null Prozent, also 0.0.

SELECT
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary) AS salary_rank,
    PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary) AS salary_percent_rank
FROM employees
ORDER BY department, salary;

3. CUME_DIST: kumulative Verteilung statt Rangbasis

CUME_DIST unterscheidet sich von PERCENT_RANK in einem entscheidenden Detail: Statt auf dem Rang aufzubauen, zählt CUME_DIST direkt, wie viele Zeilen einen Wert kleiner oder gleich dem aktuellen Wert haben, und teilt diese Zahl durch die Gesamtzahl der Zeilen in der Partition. Die Formel lautet also Anzahl der Zeilen mit einem Sortierwert kleiner gleich dem aktuellen Wert, geteilt durch die Gesamtzahl der Zeilen. Damit liegt der Ergebnisbereich immer strikt zwischen einem Wert größer null und genau eins, niemals bei null.

Dieser Unterschied wirkt sich vor allem bei Ties spürbar aus. Während bei PERCENT_RANK alle Zeilen mit demselben Wert denselben, potenziell sehr niedrigen Rang teilen, erhalten bei CUME_DIST alle Zeilen mit demselben Wert automatisch den höchsten gemeinsamen Anteilswert dieser Gruppe, weil die Zählung alle gleich- oder kleinwertigen Zeilen einschließt. Praktisch bedeutet das, dass CUME_DIST die Frage beantwortet, welcher Anteil der Gesamtmenge höchstens so groß ist wie der aktuelle Wert, während PERCENT_RANK eher die relative Position in der Sortierreihenfolge ausdrückt.

SELECT
    employee_id,
    department,
    salary,
    CUME_DIST() OVER (PARTITION BY department ORDER BY salary) AS salary_cume_dist
FROM employees
ORDER BY department, salary;

4. PERCENT_RANK und CUME_DIST im direkten Vergleich bei Ties

Der praktische Unterschied zwischen beiden Funktionen wird erst bei doppelten Werten wirklich sichtbar. Angenommen, in einer Partition mit zehn Zeilen haben drei Zeilen denselben, niedrigsten Wert. Bei PERCENT_RANK erhalten alle drei Zeilen den Wert null, weil ihr gemeinsamer Rang eins ist und die Formel (eins minus eins) durch neun ergibt. Bei CUME_DIST erhalten dieselben drei Zeilen dagegen den Wert 0.3, weil drei von zehn Zeilen einen Wert kleiner oder gleich dem aktuellen Wert haben.

Diese Divergenz zeigt, dass die beiden Funktionen unterschiedliche Fragen beantworten, auch wenn sie oberflächlich ähnlich aussehen. PERCENT_RANK beantwortet, wie weit eine Zeile im Vergleich zur ersten und letzten Position der Sortierung entfernt liegt, während CUME_DIST beantwortet, welcher Anteil der Gesamtmenge höchstens diesen Wert erreicht. Für Perzentil-Berichte, in denen Nutzer typischerweise fragen, in welchem Prozentbereich ein Wert liegt, ist CUME_DIST meist die intuitivere Wahl, weil sie sich direkt als Anteil interpretieren lässt.

5. Abgrenzung zu NTILE: stufenlos statt in feste Buckets

NTILE teilt eine Partition in eine feste Anzahl gleich großer Gruppen ein, etwa in vier Quartile oder zehn Dezile, und jede Zeile erhält eine Ganzzahl als Gruppen-Zugehörigkeit. Das ist praktisch, wenn eine feste Anzahl an Kategorien tatsächlich gebraucht wird, etwa für eine Kundensegmentierung in vier gleich große Gruppen. Der Nachteil ist, dass NTILE innerhalb einer Gruppe keine weitere Differenzierung mehr liefert, zwei Werte in derselben Gruppe können also sehr unterschiedlich weit von der Gruppengrenze entfernt liegen, ohne dass sich das im Ergebnis widerspiegelt.

PERCENT_RANK und CUME_DIST liefern dagegen eine stufenlose, kontinuierliche Einordnung ohne vorab festgelegte Bucket-Anzahl. Statt zu sagen, ein Kunde gehört zum obersten Quartil, sagt CUME_DIST direkt, dass ein Kunde im oberen 1.5 Prozent der Verteilung liegt, was für feingranulare Auswertungen wie Bonusberechnungen oder individuelle Perzentil-Angaben in einem Dashboard oft aussagekräftiger ist als eine grobe Bucket-Zuordnung. In der Praxis ergänzen sich beide Ansätze häufig: NTILE für die grobe Kategorisierung, PERCENT_RANK oder CUME_DIST für die exakte Prozentangabe innerhalb eines Berichts.

6. Praxisbeispiel: Gehaltsbenchmark ohne feste Bucket-Anzahl

Ein realistisches Anwendungsszenario ist ein interner Gehaltsbenchmark, bei dem die Personalabteilung wissen möchte, in welchem Perzentil ein einzelnes Gehalt innerhalb der jeweiligen Berufsgruppe liegt, ohne dass vorab eine feste Anzahl von Gehaltsklassen definiert werden soll. Eine Anfrage wie liegt dieses Gehalt im obersten Zehntel oder eher im Mittelfeld lässt sich mit CUME_DIST direkt und ohne zusätzliche Bucket-Logik beantworten.

In der folgenden Abfrage wird für jede Position innerhalb einer Berufsgruppe sowohl der relative Rang als auch die kumulative Verteilung berechnet, sodass beide Sichtweisen im selben Ergebnis verfügbar sind. Die Rundung auf zwei Nachkommastellen macht das Ergebnis für eine Präsentation lesbar, ohne die zugrunde liegende Präzision der Berechnung zu verändern.

SELECT
    employee_id,
    job_family,
    salary,
    ROUND(CUME_DIST() OVER (PARTITION BY job_family ORDER BY salary)::numeric, 2)
        AS salary_percentile,
    CASE
        WHEN CUME_DIST() OVER (PARTITION BY job_family ORDER BY salary) >= 0.9
            THEN 'Top 10 Prozent'
        WHEN CUME_DIST() OVER (PARTITION BY job_family ORDER BY salary) <= 0.1
            THEN 'Unteres Zehntel'
        ELSE 'Mittelfeld'
    END AS bucket_label
FROM employees
ORDER BY job_family, salary;

7. Gruppenweise Perzentile mit PARTITION BY korrekt bilden

Sowohl PERCENT_RANK als auch CUME_DIST akzeptieren wie jede andere Window Function eine PARTITION-BY-Klausel, die die Berechnung auf einzelne Gruppen beschränkt. Ohne PARTITION BY bezieht sich die Berechnung auf die gesamte Ergebnismenge, was bei heterogenen Gruppen, etwa unterschiedlichen Abteilungen mit stark abweichendem Gehaltsniveau, zu irreführenden Ergebnissen führt, weil ein hohes Gehalt in einer niedrig bezahlten Abteilung fälschlich als durchschnittlich erscheinen kann, wenn es mit einer hoch bezahlten Abteilung vermischt wird.

Ein häufiger Fehler in der Praxis ist, PARTITION BY zu vergessen oder falsch zu wählen, wenn mehrere Dimensionen gleichzeitig relevant sind, etwa Abteilung und Standort. In solchen Fällen sollte die PARTITION-BY-Klausel alle Dimensionen enthalten, innerhalb derer ein sinnvoller Vergleich stattfinden soll, während übergreifende Vergleiche über alle Partitionen hinweg eine separate Abfrage ohne Partitionierung oder mit einer bewusst gröberen Partitionierung erfordern.

8. Randfälle bei kleinen Partitionen und Nullwerten

Bei sehr kleinen Partitionen liefern beide Funktionen mathematisch korrekte, aber praktisch oft wenig aussagekräftige Ergebnisse. Eine Partition mit genau zwei Zeilen liefert für PERCENT_RANK entweder null oder eins, es gibt keine Zwischenwerte, weil der Nenner in der Formel nur den Wert eins annehmen kann. Bei einer Partition mit nur einer Zeile liefert PERCENT_RANK definitionsgemäß null, während CUME_DIST für dieselbe Zeile immer eins liefert, weil hundert Prozent der Zeilen einen Wert kleiner oder gleich dem einzigen vorhandenen Wert haben.

Auch NULL-Werte in der Sortierspalte verdienen besondere Aufmerksamkeit. Die meisten Datenbanken sortieren NULL-Werte je nach Konfiguration entweder an den Anfang oder an das Ende der Reihenfolge, was sich direkt auf den berechneten PERCENT_RANK- oder CUME_DIST-Wert auswirkt. Ist dieses Verhalten für die Fachlichkeit relevant, empfiehlt sich eine explizite NULLS-FIRST- oder NULLS-LAST-Angabe in der ORDER-BY-Klausel der Window Function, statt sich auf das jeweilige Standardverhalten der eingesetzten Datenbank zu verlassen, das zwischen Systemen durchaus unterschiedlich ausfällt.

SELECT
    employee_id,
    bonus_eligible,
    PERCENT_RANK() OVER (
        ORDER BY bonus_eligible NULLS LAST
    ) AS bonus_percent_rank
FROM employees;

9. Verfügbarkeit über verschiedene Datenbanksysteme hinweg

PERCENT_RANK und CUME_DIST sind Teil des SQL-Standards und in PostgreSQL, Oracle und SQL Server seit langem vollständig implementiert, jeweils mit identischer Syntax und identischem Verhalten bei Ties. MySQL unterstützt beide Funktionen erst seit Version 8.0 im Rahmen der allgemeinen Einführung von Window Functions, in älteren MySQL-Versionen müssen sie über Hilfskonstruktionen mit Subqueries und COUNT-Aggregationen manuell nachgebildet werden, was deutlich mehr Code und schlechtere Lesbarkeit bedeutet.

SQLite unterstützt beide Funktionen ebenfalls seit einer vergleichsweise frühen Version im Rahmen der Window-Function-Erweiterung, was sie auch für eingebettete Anwendungen und lokale Analysen nutzbar macht. Bei portablem Code, der auf mehreren Datenbanksystemen laufen soll, lohnt sich dennoch ein Blick auf die konkrete Mindestversion, insbesondere wenn ältere MySQL-Installationen im Zielspektrum liegen, da dort eine funktionale Nachbildung über Fensteraggregation mit COUNT und einer geeigneten ORDER-BY-Logik notwendig wird.

10. Zusammenfassung

11. FAQ: PERCENT_RANK und CUME_DIST: Das Wichtigste auf einen Blick