Was ist PERCENTILE_CONT?
Beim letzten Mal hattet ihr im Dojo mit der PERCENTILE_DISC
-Funktion gearbeitet. Diese Funktion hat euch immer genau den Wert in eurer Tabelle zurückgegeben, bei der ein bestimmtes Quantil erreicht wurde. Das heißt, sie hat auf der diskreten Menge der Daten operiert und einen bestimmten Wert aus dieser Menge herausgesucht, doch wie sieht das aus, wenn ihr stattdessen ein interpoliertes Quantil haben möchtet, also eines das auf einer stetigen (auf Englisch sagt man “Continuous”) Verteilungsfunktion arbeitet, also nicht mit diskreten Werten wie Anzahl oder Tag sondern mit kontinuierlichen Werten arbeitet? Das macht die PERCENTILE_CONT
-Funktion, die euer heutiger Randori-Partner sein wird.
Wie könnt ihr mit PERCENTILE_CONT arbeiten?
Wie schon in der letzten Woche wird auch das heutige Dojo eher kurz ausfallen, da die Syntax wenig überraschend dieselbe ist, wie bei PERCENTILE_DISC
. Nehmen wir uns also einen Datensatz heraus, bei dem wir beide Funktionen miteinander vergleichen können. Dafür gehen wir in die WideWorldImporters Datenbank und sehen uns die Preise der Stock Items an:
SELECT UnitPrice ,StockItemID FROM [Warehouse].[StockItems]
Auf dieser Datenmenge möchten wir nun herausfinden, bei welchen Preisen die 0.05 und 0.95-Quantile liegen. Das bedeutet, wir bestimmen den Preisbereich in dem 90% der Produkte liegen, das kann uns helfen, Ausreißer zu finden. Dafür nutzen wir sowohl die PERCENTILE_DISC
-Funktion:
SELECT UnitPrice ,StockItemID ,PERCENTILE_DISC(0.05) WITHIN GROUP (ORDER BY UnitPrice) OVER (PARTITION BY 1) AS Lower5Percent ,PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY UnitPrice) OVER (PARTITION BY 1) AS Upper5Percent FROM [Warehouse].[StockItems]
Diese Werte möchten wir nun mit der kontinuierlichen Version der Quantils-Funktion vergleichen:
SELECT UnitPrice ,StockItemID ,PERCENTILE_DISC(0.05) WITHIN GROUP (ORDER BY UnitPrice) OVER (PARTITION BY 1) AS Lower5PercentDisc ,PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY UnitPrice) OVER (PARTITION BY 1) AS Upper5PercentDisc ,PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY UnitPrice) OVER (PARTITION BY 1) AS Lower5PercentCont ,PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY UnitPrice) OVER (PARTITION BY 1) AS Upper5Percent FROM [Warehouse].[StockItems]
Ihr seht, dass die diskrete Variante für das untere 0,5 Quantil den Wert 2,55 ausgibt, während die kontinuierliche Variante einen Wert von 2,595 liefert. Diese Werte überprüfen wir nun, indem wir zählen, wie viele Zeilen Preise haben, die kleiner oder gleich diesen Werten sind:
SELECT COUNT(*), 'Count DISC' FROM [Warehouse].[StockItems] WHERE UnitPrice <= 2.55 UNION ALL SELECT COUNT(*), 'Count CONT' FROM [Warehouse].[StockItems] WHERE UnitPrice <= 2.595
Wir sehen in beiden Fällen 12 Zeilen, die das Kriterium erfüllen. Der Unterschied beider Funktionen liegt darin, dass PERCENTILE_DISC
eben den diskreten (also den konkreten) Wert einer Zeile zurück liefert, während PERCENTILE_CONT
zwischen den Werten der Zeilen interpoliert und den echten Wert für das Quantil berechnet und zurückliefert, denn während es Zeilen mit einem UnitPrice von 2,55 gibt, gibt es keine mit einem UnitPrice von 2,595:
SELECT StockItemID, 'Price DISC' FROM [Warehouse].[StockItems] WHERE UnitPrice = 2.55 UNION ALL SELECT StockItemID, 'Price CONT' FROM [Warehouse].[StockItems] WHERE UnitPrice = 2.595
Dieses kurze Randori hat euch hoffentlich erklärt, was der Unterschied zwischen den beiden Varianten der PERCENTILE
-Funktion ist. Welche der Varianten ihr anwenden solltet, hängt dabei von eurer Anwendung ab. Wenn ihr am Ende konkrete Datensätze referenzieren möchtet, die genau auf dem Quantil liegen, dann ist natürlich die PERCENTILE_DISC
-Funktion vorzuziehen, wenn ihr aber einen Schwellwert möglichst genau angeben möchtet, dann ist PERCENTILE_CONT
sicher die richtige Wahl. Gut dabei ist, dass ihr die Wahl habt.