PERCENTILE_CONT

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_CONTzwischen 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_CONTsicher die richtige Wahl. Gut dabei ist, dass ihr die Wahl habt.

Referenzen

• Dokumentation von Microsoft