Berechnung von Perzentil-Ranglisten in MS SQL

Question

Was ist der beste Weg, um Perzentil-Rankings (z.B. das 90. Perzentil oder der Medianwert) in MSSQL 2005 zu berechnen?

Ich möchte in der Lage sein, die 25., Median und 75. Perzentile für eine einzelne Spalte von Noten (vorzugsweise in einem einzigen Datensatz, so dass ich mit Durchschnitt, Maximum und Minimum kombinieren kann) zu wählen. Die Tabellenausgabe der Ergebnisse könnte zum Beispiel so aussehen:

Group  MinScore  MaxScore  AvgScore  pct25  median  pct75
-----  --------  --------  --------  -----  ------  -----
T1     52        96        74        68     76      84
T2     48        98        74        68     75      85

Answer 1

Ich würde denken, dass dies die einfachste Lösung wäre:

SELECT TOP N PERCENT FROM TheTable ORDER BY TheScore DESC

Dabei ist N = (100 - gewünschtes Perzentil). Wenn Sie also alle Zeilen im 90. Perzentil haben möchten, wählen Sie die obersten 10 % aus.

Ich bin mir nicht sicher, was Sie mit "vorzugsweise in einem einzigen Datensatz" meinen. Meinen Sie damit die Berechnung, in welches Perzentil eine bestimmte Punktzahl für einen einzelnen Datensatz fallen würde? Wollen Sie z. B. Aussagen treffen können wie "Ihre Punktzahl ist 83, womit Sie im 91. Perzentil liegen."? ?

EDIT: OK, ich habe etwas mehr über Ihre Frage nachgedacht und bin zu dieser Interpretation gekommen. Fragen Sie, wie man den Cutoff-Wert für ein bestimmtes Perzentil berechnet? z. B. so: Um im 90. Perzentil zu sein, müssen Sie eine Punktzahl von mehr als 78 haben.

Wenn ja, funktioniert diese Abfrage. Ich mag allerdings keine Unterabfragen. Je nachdem, wofür sie gedacht ist, würde ich wahrscheinlich versuchen, eine elegantere Lösung zu finden. Sie gibt jedoch einen einzigen Datensatz mit einer einzigen Punktzahl zurück.

-- Find the minimum score for all scores in the 90th percentile
SELECT Min(subq.TheScore) FROM
(SELECT TOP 10 PERCENT TheScore FROM TheTable
ORDER BY TheScore DESC) AS subq

Answer 2

Probieren Sie den NTILE-Befehl aus - er liefert Ihnen ganz einfach Perzentile!

SELECT  SalesOrderID, 
    OrderQty,
    RowNum = Row_Number() OVER(Order By OrderQty),
    Rnk = RANK() OVER(ORDER BY OrderQty),
    DenseRnk = DENSE_RANK() OVER(ORDER BY OrderQty),
    NTile4  = NTILE(4) OVER(ORDER BY OrderQty)
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN (43689, 63181)

Answer 3

Wie wäre es damit:

SELECT
  Group,
  75_percentile =  MAX(case when NTILE(4) OVER(ORDER BY score ASC) = 3 then score  else 0 end),
  90_percentile =  MAX(case when NTILE(10) OVER(ORDER BY score  ASC) = 9 then score  else 0 end)     
FROM TheScore
GROUP BY Group

Answer 4

Ich habe mich ein bisschen mehr damit beschäftigt, und hier ist, was ich bis jetzt herausgefunden habe:

CREATE PROCEDURE [dbo].[TestGetPercentile]

@percentile as float,
@resultval as float output

AS

BEGIN

WITH scores(score, prev_rank, curr_rank, next_rank) AS (
    SELECT dblScore,
        (ROW_NUMBER() OVER ( ORDER BY dblScore ) - 1.0) / ((SELECT COUNT(*) FROM TestScores) + 1)  [prev_rank],
        (ROW_NUMBER() OVER ( ORDER BY dblScore ) + 0.0) / ((SELECT COUNT(*) FROM TestScores) + 1)  [curr_rank],
        (ROW_NUMBER() OVER ( ORDER BY dblScore ) + 1.0) / ((SELECT COUNT(*) FROM TestScores) + 1)  [next_rank]
    FROM TestScores
)

SELECT @resultval = (
    SELECT TOP 1 
    CASE WHEN t1.score = t2.score
        THEN t1.score
    ELSE
        t1.score + (t2.score - t1.score) * ((@percentile - t1.curr_rank) / (t2.curr_rank - t1.curr_rank))
    END
    FROM scores t1, scores t2
    WHERE (t1.curr_rank = @percentile OR (t1.curr_rank < @percentile AND t1.next_rank > @percentile))
        AND (t2.curr_rank = @percentile OR (t2.curr_rank > @percentile AND t2.prev_rank < @percentile))
)

END

Dann in einer anderen gespeicherten Prozedur tue ich dies:

DECLARE @pct25 float;
DECLARE @pct50 float;
DECLARE @pct75 float;

exec SurveyGetPercentile .25, @pct25 output
exec SurveyGetPercentile .50, @pct50 output
exec SurveyGetPercentile .75, @pct75 output

Select
    min(dblScore) as minScore,
    max(dblScore) as maxScore,
    avg(dblScore) as avgScore,
    @pct25 as percentile25,
    @pct50 as percentile50,
    @pct75 as percentile75
From TestScores

Es ist immer noch nicht ganz das, wonach ich suche. Ich möchte jedoch aus einer TestScores-Tabelle auswählen können, die mehrere verschiedene Tests enthält, und die gleichen Statistiken für jeden einzelnen Test zurückerhalten (wie in meiner Beispieltabelle in meiner Frage).

Answer 5

Das 50. Perzentil entspricht dem Median. Bei der Berechnung eines anderen Perzentils, z. B. des 80. Perzentils, werden die Daten für die 80 Prozent der Daten in aufsteigender Reihenfolge und die anderen 20 Prozent in absteigender Reihenfolge sortiert, und es wird der Durchschnittswert der beiden mittleren Werte ermittelt.

NB: Die Median-Abfrage gibt es schon lange, aber ich weiß nicht mehr genau, woher ich sie habe, ich habe sie nur geändert, um andere Perzentile zu berechnen.

DECLARE @Temp TABLE(Id INT IDENTITY(1,1), DATA DECIMAL(10,5))

INSERT INTO @Temp VALUES(0)
INSERT INTO @Temp VALUES(2)
INSERT INTO @Temp VALUES(8)
INSERT INTO @Temp VALUES(4)
INSERT INTO @Temp VALUES(3)
INSERT INTO @Temp VALUES(6)
INSERT INTO @Temp VALUES(6)
INSERT INTO @Temp VALUES(6) 
INSERT INTO @Temp VALUES(7)
INSERT INTO @Temp VALUES(0)
INSERT INTO @Temp VALUES(1)
INSERT INTO @Temp VALUES(NULL)

--50th percentile or median
SELECT ((
        SELECT TOP 1 DATA
        FROM   (
                SELECT  TOP 50 PERCENT DATA
                FROM    @Temp
                WHERE   DATA IS NOT NULL
                ORDER BY DATA
                ) AS A
        ORDER BY DATA DESC) + 
        (
        SELECT TOP 1 DATA
        FROM   (
                SELECT  TOP 50 PERCENT DATA
                FROM    @Temp
                WHERE   DATA IS NOT NULL
                ORDER BY DATA DESC
                ) AS A
        ORDER BY DATA ASC)) / 2.0

--90th percentile 
SELECT ((
        SELECT TOP 1 DATA
        FROM   (
                SELECT  TOP 90 PERCENT DATA
                FROM    @Temp
                WHERE   DATA IS NOT NULL
                ORDER BY DATA
                ) AS A
        ORDER BY DATA DESC) + 
        (
        SELECT TOP 1 DATA
        FROM   (
                SELECT  TOP 10 PERCENT DATA
                FROM    @Temp
                WHERE   DATA IS NOT NULL
                ORDER BY DATA DESC
                ) AS A
        ORDER BY DATA ASC)) / 2.0

--75th percentile
SELECT ((
        SELECT TOP 1 DATA
        FROM   (
                SELECT  TOP 75 PERCENT DATA
                FROM    @Temp
                WHERE   DATA IS NOT NULL
                ORDER BY DATA
                ) AS A
        ORDER BY DATA DESC) + 
        (
        SELECT TOP 1 DATA
        FROM   (
                SELECT  TOP 25 PERCENT DATA
                FROM    @Temp
                WHERE   DATA IS NOT NULL
                ORDER BY DATA DESC
                ) AS A
        ORDER BY DATA ASC)) / 2.0