NTILE fuer Perzentil-Berechnungen einsetzen

1. Warum Perzentil-Analysen direkt in SQL gehoeren

Eine Perzentil-Analyse beantwortet Fragen wie "In welchem Viertel der Kunden nach Umsatz liegt dieser Datensatz?" oder "Welche Antwortzeit markiert das 95. Perzentil aller Requests?". Solche Fragen tauchen in Reporting, Monitoring und Kundensegmentierung staendig auf, und ohne eine Datenbankfunktion dafuer landet die Berechnung frueher oder spaeter in einem Python-Skript mit pandas oder numpy, das Rohdaten aus der Datenbank exportiert, sortiert und in Buckets einteilt.

SQL bietet mit NTILE(n) eine Window Function, die genau dieses Problem loest, ohne Daten die Datenbank verlassen zu muessen. NTILE(n) teilt eine nach einer Sortierspalte geordnete Ergebnismenge in n moeglichst gleich grosse Gruppen, sogenannte Buckets, auf und weist jeder Zeile die Bucket-Nummer zu. Das ist die Grundlage fuer Quartile, Dezile und beliebige andere Perzentil-Einteilungen, alles in einer einzigen Abfrage.

Dieser Artikel zeigt die Mechanik von NTILE(n) im Detail, wie ungleiche Bucket-Groessen entstehen, wie sich NTILE von PERCENT_RANK und CUME_DIST unterscheidet und wie eine praktische Kundensegmentierung nach Umsatzquartilen aussieht.

2. NTILE(n): die Grundlagen der Bucket-Verteilung

NTILE(n) ist eine Window Function, die als Argument die Anzahl der gewuenschten Buckets erwartet, zum Beispiel NTILE(4) fuer Quartile oder NTILE(10) fuer Dezile. Wie jede Window Function benoetigt NTILE eine ORDER BY-Klausel in der OVER()-Definition, denn ohne definierte Reihenfolge waere die Zuordnung von Zeilen zu Buckets willkuerlich. Optional gruppiert PARTITION BY die Berechnung, sodass Perzentile getrennt pro Kategorie, Region oder Zeitraum entstehen koennen.

Der Algorithmus hinter NTILE ist einfach: Die Datenbank zaehlt zunaechst alle Zeilen der Partition, teilt diese Anzahl durch n und weist den ersten Zeilen der sortierten Reihenfolge Bucket 1 zu, den naechsten Bucket 2, und so weiter bis Bucket n. Bei 100 Zeilen und NTILE(4) landen exakt 25 Zeilen in jedem Bucket, Bucket 1 enthaelt die 25 Zeilen mit den niedrigsten Werten der Sortierspalte, Bucket 4 die 25 Zeilen mit den hoechsten.

-- Basic NTILE: split customers into 4 buckets by total spend
SELECT
    customer_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile
FROM customer_totals
ORDER BY total_spend;

-- Sample result (8 customers, 4 buckets, 2 rows each)
-- customer_id | total_spend | spend_quartile
-- 5           | 120.00      | 1
-- 2           | 340.00      | 1
-- 8           | 510.00      | 2
-- 1           | 680.00      | 2
-- 6           | 890.00      | 3
-- 3           | 1100.00     | 3
-- 4           | 1450.00     | 4
-- 7           | 2200.00     | 4

3. Ungleiche Bucket-Groessen: wie NTILE Restwerte verteilt

Ist die Zeilenzahl nicht ohne Rest durch n teilbar, entstehen zwangslaeufig ungleich grosse Buckets. Bei 10 Zeilen und NTILE(3) lassen sich keine drei exakt gleich grossen Gruppen bilden. NTILE loest dieses Problem, indem es den Rest auf die ersten Buckets verteilt: Bei 10 Zeilen und drei Buckets erhalten die ersten beiden Buckets je vier Zeilen, der dritte Bucket nur zwei. Diese Regel ist im SQL-Standard festgelegt und wird von allen gaengigen Datenbanken identisch umgesetzt.

Diese ungleiche Verteilung ist meist unproblematisch, sollte aber bekannt sein, bevor NTILE in einem Report auf kleine Datenmengen angewendet wird. Bei sehr wenigen Zeilen, etwa fuenf Zeilen und NTILE(10), erhalten die ersten fuenf Buckets je eine Zeile und die restlichen fuenf Buckets bleiben komplett leer, das heisst sie erscheinen im Ergebnis gar nicht als eigene Bucket-Nummer. Fuer statistisch belastbare Perzentile braucht NTILE deshalb eine ausreichend grosse Datenmenge relativ zur gewaehlten Bucket-Anzahl.

-- Uneven distribution: 10 rows into 3 buckets
SELECT
    request_id,
    response_time_ms,
    NTILE(3) OVER (ORDER BY response_time_ms) AS bucket
FROM api_requests
ORDER BY response_time_ms;

-- 10 rows, 3 buckets: first two buckets get 4 rows, last gets 2
-- bucket 1: rows 1-4
-- bucket 2: rows 5-8
-- bucket 3: rows 9-10

4. Quartile berechnen: NTILE(4) in der Praxis

Quartile teilen eine Verteilung in vier gleich grosse Gruppen, jeweils 25 Prozent der Zeilen. NTILE(4) ist deshalb die direkte SQL-Entsprechung eines Quartils: Bucket 1 enthaelt das unterste Quartil, Bucket 4 das oberste. Diese Einteilung ist die Grundlage fuer Boxplots, fuer die Identifikation von Ausreissern und fuer viele Business-Reports, die Kunden, Produkte oder Transaktionen in "unteres", "mittleres unteres", "mittleres oberes" und "oberes" Viertel einteilen wollen.

Ein haeufiger Folgeschritt nach der Quartilsberechnung ist die Aggregation pro Bucket: Durchschnitt, Minimum und Maximum jeder Quartilsgruppe zeigen, wie stark sich die Werte innerhalb der Verteilung unterscheiden. Kombiniert man NTILE(4) in einer Subquery oder CTE mit einer nachfolgenden GROUP BY spend_quartile-Aggregation, entsteht eine vollstaendige Quartilsuebersicht in einer einzigen zusammengesetzten Abfrage.

-- Quartile summary: aggregate after bucketing
WITH quartiles AS (
    SELECT
        customer_id,
        total_spend,
        NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile
    FROM customer_totals
)
SELECT
    spend_quartile,
    COUNT(*) AS customer_count,
    MIN(total_spend) AS min_spend,
    MAX(total_spend) AS max_spend,
    ROUND(AVG(total_spend), 2) AS avg_spend
FROM quartiles
GROUP BY spend_quartile
ORDER BY spend_quartile;

5. Dezile und Perzentile: NTILE(10) und NTILE(100)

Fuer feinere Einteilungen ersetzt man einfach das Argument von NTILE: NTILE(10) erzeugt Dezile mit jeweils zehn Prozent der Zeilen pro Bucket, NTILE(100) erzeugt echte Perzentile mit jeweils einem Prozent pro Bucket. Diese Flexibilitaet ist einer der groessten Vorteile von NTILE gegenueber fest kodierten Fallunterscheidungen mit CASE WHEN, bei denen jede Grenze manuell berechnet und gepflegt werden muesste.

NTILE(100) ist besonders in Performance-Monitoring gebraeuchlich, etwa um das 95. oder 99. Perzentil der Antwortzeiten zu identifizieren. Statt einer festen Formel mit PERCENTILE_CONT, die nicht in jeder Datenbank verfuegbar ist, liefert NTILE(100) eine Bucket-Nummer pro Zeile, aus der sich mit einem einfachen Filter WHERE percentile_bucket >= 95 die oberen fuenf Prozent aller Requests extrahieren lassen, unabhaengig vom SQL-Dialekt.

6. NTILE mit PARTITION BY: Perzentile pro Gruppe

In der Praxis sollen Perzentile selten global, sondern meist pro Kategorie berechnet werden, etwa Umsatzquartile getrennt pro Region oder Antwortzeit-Perzentile getrennt pro API-Endpunkt. PARTITION BY in der OVER()-Klausel loest genau das: NTILE berechnet die Bucket-Zuordnung dann unabhaengig innerhalb jeder Partition, jede Gruppe bekommt ihre eigenen vier, zehn oder hundert Buckets.

Wichtig dabei: Die Anzahl der Zeilen pro Partition beeinflusst direkt die Aussagekraft der Buckets. Eine Region mit nur zwoelf Kunden und NTILE(10) erzeugt Buckets mit nur ein bis zwei Kunden, was statistisch kaum belastbar ist. Vor dem Einsatz von PARTITION BY lohnt sich deshalb ein COUNT(*) pro geplanter Partition, um zu pruefen, ob die gewaehlte Bucket-Anzahl fuer die jeweilige Gruppengroesse sinnvoll ist.

-- Quartiles per region, independent buckets in each partition
SELECT
    region,
    customer_id,
    total_spend,
    NTILE(4) OVER (
        PARTITION BY region
        ORDER BY total_spend
    ) AS regional_spend_quartile
FROM customer_totals
ORDER BY region, total_spend;

7. Der Unterschied zu PERCENT_RANK und CUME_DIST

NTILE ist nicht die einzige SQL-Funktion fuer Verteilungsanalysen. PERCENT_RANK() liefert fuer jede Zeile einen relativen Rang zwischen 0 und 1, berechnet als (Rang - 1) / (Gesamtzahl - 1), waehrend CUME_DIST() die kumulative Verteilung liefert, also den Anteil der Zeilen, deren Wert kleiner oder gleich dem aktuellen ist. Beide Funktionen liefern eine kontinuierliche Zahl statt einer diskreten Bucket-Nummer und eignen sich deshalb besser, wenn der exakte relative Rang statt einer groben Gruppierung gefragt ist.

Der praktische Unterschied zeigt sich am deutlichsten bei Duplikaten in der Sortierspalte: NTILE ignoriert gleiche Werte und teilt strikt nach Zeilenposition auf, sodass zwei Zeilen mit identischem Wert in unterschiedlichen Buckets landen koennen. PERCENT_RANK und CUME_DIST hingegen beruecksichtigen Ties explizit und weisen gleichen Werten denselben relativen Rang zu. Wer also mit vielen wiederholten Werten arbeitet, etwa Bewertungen von eins bis fuenf, sollte PERCENT_RANK statt NTILE in Betracht ziehen.

-- Compare NTILE, PERCENT_RANK and CUME_DIST on the same data
SELECT
    product_id,
    rating,
    NTILE(4) OVER (ORDER BY rating) AS ntile_bucket,
    ROUND(PERCENT_RANK() OVER (ORDER BY rating)::numeric, 3) AS percent_rank,
    ROUND(CUME_DIST() OVER (ORDER BY rating)::numeric, 3) AS cume_dist
FROM product_ratings
ORDER BY rating;

-- Note: rows with identical rating get identical percent_rank
-- and cume_dist, but can still fall into different ntile_bucket values

8. Anwendungsfall: Kundensegmentierung nach Umsatz

Ein klassischer Anwendungsfall fuer NTILE ist die Einteilung von Kunden in Wertsegmente fuer Marketing- oder Vertriebszwecke. Mit NTILE(4) ueber den Gesamtumsatz pro Kunde entsteht eine Vier-Stufen-Segmentierung, die sich direkt in eine CASE WHEN-Klausel uebersetzen laesst, etwa "Top-Kunden" fuer Bucket 4 und "Gelegenheitskaeufer" fuer Bucket 1. Diese Segmentierung passt sich automatisch an, wenn sich die Kundenbasis veraendert, weil NTILE die Grenzen bei jeder Ausfuehrung neu aus den aktuellen Daten berechnet, statt feste Umsatzschwellen zu verwenden.

Der Vorteil gegenueber fest kodierten Schwellenwerten wie "Kunden mit Umsatz ueber 1000 Euro" liegt in der relativen Natur von NTILE: Waechst der durchschnittliche Kundenumsatz ueber die Zeit, bleiben die Quartilsgrenzen automatisch aktuell, ohne dass jemand manuell einen Schwellenwert im Code anpassen muss. Das macht NTILE-basierte Segmentierungen deutlich wartungsaermer als Regelwerke mit fest verdrahteten Zahlen.

9. NTILE, PERCENT_RANK und CUME_DIST im Vergleich

Die Wahl zwischen NTILE, PERCENT_RANK und CUME_DIST haengt vom gewuenschten Ergebnisformat ab. Braucht die Anwendung eine klar definierte Gruppenzugehoerigkeit, etwa fuer ein Dashboard mit vier Kundensegmenten, ist NTILE die richtige Wahl, weil es eine diskrete, direkt filterbare Bucket-Nummer liefert. Braucht die Anwendung dagegen den exakten relativen Rang eines einzelnen Werts, etwa "dieser Request liegt im 97. Perzentil aller Antwortzeiten", ist PERCENT_RANK oder CUME_DIST praeziser, weil beide Funktionen nicht durch die Bucket-Grenzen von NTILE eingeschraenkt sind.

Alle drei Funktionen teilen dieselbe Grundvoraussetzung: eine ORDER BY-Klausel in der OVER()-Definition, ohne die keine sinnvolle Verteilung berechnet werden kann. Und alle drei lassen sich mit PARTITION BY kombinieren, um Perzentile getrennt pro Gruppe zu berechnen, was sie zu einem konsistenten Werkzeugkasten fuer nahezu jede Verteilungsanalyse in SQL macht.

10. Zusammenfassung

NTILE(n) teilt eine sortierte Ergebnismenge in n moeglichst gleich grosse Buckets auf und ist damit das direkte SQL-Werkzeug fuer Quartile mit NTILE(4), Dezile mit NTILE(10) und Perzentile mit NTILE(100). Restzeilen bei nicht restlos teilbaren Zeilenzahlen verteilen sich auf die ersten Buckets, was bei kleinen Datenmengen zu leeren oder ungleich grossen Buckets fuehren kann. Kombiniert mit PARTITION BY berechnet NTILE Perzentile unabhaengig pro Gruppe, etwa pro Region oder Produktkategorie.

Im Vergleich zu PERCENT_RANK und CUME_DIST liefert NTILE eine diskrete Bucket-Nummer statt eines kontinuierlichen relativen Rangs und ignoriert dabei Ties, waehrend die beiden Alternativen gleichen Werten denselben Rang zuweisen. Fuer klassifikatorische Anwendungen wie Kundensegmentierung ist NTILE die richtige Wahl, fuer die praezise Bestimmung einzelner Perzentil-Schwellen sind PERCENT_RANK und CUME_DIST vorzuziehen.

11. FAQ: NTILE fuer Perzentil-Berechnungen