Was ist PERCENTILE_DISC?
Wie schon die in der letzten Woche vorgestellte CUME_DIST-Funktion, gehört auch PERCENTILE_DISC zu den statistischen Funktionen. Sie wurde im SQL Server 2012 eingeführt und berechnet Quantile. Wenn ihr das 0,5-Quantil einer Menge (oder auch “Stichprobe”, wenn wir bei der in der Statistik üblichen Begrifflichkeit bleiben wollen) versteht man denjenigen Wert, bei dem die Hälfte aller Werte kleiner und die andere Hälfte größer ist als dieser Wert. Entsprechend versteht man unter dem 0.1-Quantil den Wert, bei dem 10% der Werte kleiner und 90% der Werte größer sind als er selbst. Quantile könnt ihr mit PERCENTILE_DISC berechnen. Dabei wird wie schon bei CUME_DIST ein Fenster mit Sortierung angegeben und eben derjenige Wert der ORDER BY-Spalten zurückgegeben, bei dem die Verteilungsfunktion, also CUME_DIST im selben Fenster erstmalig größer als der übergebene Wert ist.
Wie könnt ihr mit PERCENTILE_DISC arbeiten?
Nehmen wir uns in der WideWorldImporters-Datenbank eine Abfrage vor, bei der wir uns die Verteilung von Bestellungen im Jahr ansehen:
SELECT
InvoiceDate
,CUME_DIST() OVER (PARTITION BY DATEPART(YEAR, InvoiceDate) ORDER BY InvoiceDate) as SalesDistribution
FROM Sales.Invoices
Angenommen, wir möchten nun erfahren, an welchem Tag im Jahr 90% der Bestellungen eingegangen waren. Dann berechnen wir das 0,9-Quantil der Bestellungen wie folgt:
SELECT
InvoiceDate
,CUME_DIST() OVER (PARTITION BY DATEPART(YEAR, InvoiceDate) ORDER BY InvoiceDate) as SalesDistribution
,PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY InvoiceDate) OVER (PARTITION BY DATEPART(YEAR, InvoiceDate)) as BreakevenPoint
FROM Sales.Invoices
ORDER BY InvoiceDate
Wichtig ist, zu beachten, dass PERCENTILE_DISC die ORDER BY-Bedingung nicht im normalen Fenster, also in der OVER-Clause hat, sondern in einer speziellen WITHIN GROUP-Clause. Das liegt daran, dass wir hier anders als bei der OVER-Clause nicht mehrere Sortierspalten angeben können sondern auf eine einzige Spalte zur Sortierung festgelegt sind, die auch gleichzeitig der Rückgabewert der Funktion ist.
Nun könnt ihr auf der Zeile jeder Bestellung sehen, wann im Jahr der Bestellung 90% der Bestellungen eingegangen waren. In dieser Granularität ist das zwar in einer Applikation möglicherweise hilfreich, allerdings können wir diese Berechnung natürlich auch in einer CTE ausführen, deren Resultate wir dann zusammenfassen:
WITH CTE_Percentile AS (
SELECT
InvoiceDate
,CUME_DIST() OVER (PARTITION BY DATEPART(YEAR, InvoiceDate) ORDER BY InvoiceDate) as SalesDistribution
,PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY InvoiceDate) OVER (PARTITION BY DATEPART(YEAR, InvoiceDate)) as BreakevenPoint
FROM Sales.Invoices
)
SELECT DISTINCT
DATEPART(YEAR, InvoiceDate) as InvoiceYear
,BreakevenPoint
FROM CTE_Percentile
Wichtig ist, zu beachten, dass PERCENTILE_DISC euch immer einen konkreten Wert zurückliefert, den es auch in eurer Tabelle gibt, das heißt hier wird nicht interpoliert oder so, sondern wirklich genau der Wert zurückgegeben, bei dem CUME_DIST erstmalig über dem gesuchten Quantil liegt. Deshalb auch der Name der Funktion: PERCENTILE_DISC berechnet auf der diskreten Menge der Zeilen genau den Wert, der die Bedingung erfüllt.
