Verwenden Sie NTILE mit weniger als 100 Datensätzen?

Question

Ich verwende NTILE(100) um den 95. Perzentilwert in meiner Spalte zu berechnen. In einigen Fällen, in denen die Anzahl der Datensätze weniger als 100 beträgt, NTILE(100) gibt bei der Abfrage nach den Werten im 95. Quartil einen NULL-Wert zurück, da nicht genügend Werte vorhanden sind. Ich mache dies derzeit auf folgende Weise:

CREATE TABLE #Temp(val int)
GO

INSERT INTO #Temp(val) VALUES(1);
INSERT INTO #Temp(val) VALUES(2);
INSERT INTO #Temp(val) VALUES(3);
INSERT INTO #Temp(val) VALUES(4);
INSERT INTO #Temp(val) VALUES(5);
INSERT INTO #Temp(val) VALUES(6);

SELECT val, Quantile
FROM
(
    SELECT val, NTILE(100) OVER (ORDER BY val) AS Quantile
    FROM
    #Temp
) A
WHERE Quantile=95

GO

DROP TABLE #Temp

Ich frage mich, was der akzeptierte Standard für die Behandlung eines Falls mit weniger als 100 Werten in der Spalte ist. Irgendwelche Vorschläge?

Answer 1

Ich habe nach einer Lösung für ein Kundenprojekt gesucht, das noch auf SQL Server 2005 basierte. Da NTILE die verfügbaren Zeilen einfach in Gruppen unterteilt, werden Sie mit 6 Zeilen nie mehr als 6 erhalten. Wenn Sie die Zeilen so aufteilen möchten, dass sie wie Perzentile aussehen (z. B. 16, 33, 50, 66, 83, 100) können Sie wie folgt vorgehen. Beachten Sie, dass Sie nicht notwendigerweise genau 95 treffen werden, so dass Sie nach dem nächstliegenden Wert suchen müssen:

SELECT TOP 1 val, Quantile
FROM
(
    SELECT val, CAST (ROW_NUMBER() OVER (ORDER BY val) * 100.0 / COUNT(*) OVER() AS INT) AS Quantile
    FROM #Temp
) A
WHERE Quantile >= 95 
ORDER BY Quantile

Answer 2

Ich gehe davon aus, dass, wenn Sie sagen " NTILE(100) bei der Abfrage nach den Werten im 95. Quartil einen NULL-Wert zurückgibt, weil nicht genügend Werte vorhanden sind", meinen Sie, dass Sie bei weniger als 100 Werten einen Null-Wert erhalten, und nicht, weil NITLE(100) selbst.

Bei einem kleinen Datensatz ist NTILE() keine gute Option. NTILE(100) unterteilt die Daten in 100 Unterteilungen, aber in deinem Fall gibt es nur 6 Werte. Der größte Wert 6 hat also eine entsprechende Ausgabe von 6.

Was Ihre Frage betrifft, so würde ich vorschlagen, dass Sie mit PERCENT_RANK() die das relative Perzentil jeder Zeile angibt.

Im Fall der 6 Werte wird kein Wert das exakte 95. Perzentil sein. Ich werde also den Wert ausgeben, der dem 95. am nächsten kommt.

   WITH sub AS(SELECT 
       val,
       PERCENT_RANK() OVER (ORDER BY val) AS percentile
   FROM temp)

    SELECT  *
   FROM sub
   ORDER BY ABS(percentile - 0.95)  
   LIMIT 1