percentiles - sql server percentile rank

Cálculo de rankings de percentiles en MS SQL

Question

¿Cuál es la mejor manera de calcular las clasificaciones percentiles (por ejemplo, el percentil 90 o la puntuación mediana) en MSSQL 2005?

Me gustaría poder seleccionar los percentiles 25, mediana y 75 para una sola columna de puntajes (preferiblemente en un solo registro para poder combinar con promedio, máximo y mínimo). Entonces, por ejemplo, el resultado de la tabla de los resultados podría ser:

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

Answer 1

Echa un vistazo al comando NTILE: ¡te dará los percentiles con bastante facilidad!

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 2

El percentil 50 es igual a la mediana. Al calcular otro percentil, digamos el 80º, ordene los datos para el 80% de los datos en orden ascendente y el otro 20% en orden descendente, y tome la media de los dos valores medios.

NB: La consulta mediana ha estado presente durante mucho tiempo, pero no puedo recordar de dónde exactamente la obtuve, solo la he modificado para calcular otros percentiles.

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

Answer 3

Haría algo así como:

select @n = count(*) from tbl1
select @median = @n / 2
select @p75 = @n * 3 / 4
select @p90 = @n * 9 / 10

select top 1 score from (select top @median score from tbl1 order by score asc) order by score desc

¿es esto correcto?

Answer 4

He estado trabajando en esto un poco más, y esto es lo que se me ocurrió hasta ahora:

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

Luego en otro procedimiento almacenado hago esto:

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

Todavía no hace exactamente lo que estoy buscando. Esto obtendrá las estadísticas para todas las pruebas; mientras que me gustaría ser capaz de seleccionar de una tabla TestScores que tiene múltiples pruebas diferentes y obtener las mismas estadísticas para cada prueba diferente (como tengo en mi tabla de ejemplo en mi pregunta).

Answer 5

Probablemente usaría un servidor sql 2005

row_number () over (orden por puntuación) / (seleccionar count (*) de puntajes)

O algo por el estilo.

Answer 6

Qué tal esto:

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 7

Yo pensaría que esta sería la solución más simple:

SELECT TOP N PERCENT FROM TheTable ORDER BY TheScore DESC

Donde N = (100 - percentil deseado). Por lo tanto, si desea todas las filas en el percentil 90, debe seleccionar el 10% superior.

No estoy seguro de lo que quiere decir con "preferiblemente en un solo registro". ¿Quiere decir calcular qué percentil correspondería a un puntaje dado para un solo registro? por ejemplo, ¿desea poder hacer declaraciones como "su puntaje es 83, lo que lo ubica en el percentil 91"? ?

EDITAR: OK, pensé un poco más acerca de su pregunta y se me ocurrió esta interpretación. ¿Estás preguntando cómo calcular la puntuación de corte para un percentil particular? por ejemplo, algo como esto: para estar en el percentil 90 debe tener un puntaje mayor a 78.

Si es así, esta consulta funciona. Sin embargo, me desagradan las subconsultas, por lo que, dependiendo de para qué, probablemente intente encontrar una solución más elegante. Sin embargo, devuelve un solo registro con una sola puntuación.

-- 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