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.