Вычисление рейтингов процентилей в MS SQL

Ответ 1

Я бы подумал, что это будет самое простое решение:

SELECT TOP N PERCENT FROM TheTable ORDER BY TheScore DESC

Где N = (100 - желаемый процентиль). Поэтому, если вы хотите, чтобы все строки в 90-м процентиле, вы должны выбрать 10% лучших.

Я не уверен, что вы подразумеваете под "предпочтительно в одной записи". Вы имеете в виду подсчитать, какой процентный показатель для одной записи упадет? например вы хотите, чтобы у вас были такие заявления, как "ваш счет - 83, что ставит вас в 91-й процентиль".

EDIT: Хорошо, я подумал о вашем вопросе и придумал эту интерпретацию. Вы спрашиваете, как рассчитать показатель отсечки для определенного процентиля? например что-то вроде этого: чтобы быть в 90-м процентиле, вы должны иметь счет больше 78.

Если это так, этот запрос работает. Однако мне не нравятся подзапросы, поэтому, в зависимости от того, для чего это было, я, вероятно, попытаюсь найти более элегантное решение. Тем не менее, он возвращает одну запись с одним счетом.

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

Ответ 2

Проверьте команду NTILE - это даст вам процентиля довольно легко!

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)

Ответ 3

Как насчет этого:

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

Ответ 4

Я работал над этим немного больше, и вот что я до сих пор придумал:

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

Затем в другой хранимой процедуре я делаю это:

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

Это все еще не совсем то, что я ищу. Это позволит получить статистику для всех тестов; в то время как я хотел бы иметь возможность выбирать из таблицы TestScores, в которой есть несколько разных тестов, и возвращать одинаковые статистические данные для каждого другого теста (например, у меня в моей таблице примеров в моем вопросе).

Ответ 5

50-й процентиль такой же, как медиана. При вычислении другого процентиля, скажем, 80-го, сортируйте данные для 80 процентов данных в порядке возрастания, а остальные 20 процентов в порядке убывания, и возьмите среднее среднее значение.

NB: медианный запрос существует долгое время, но я не могу вспомнить, откуда именно я его получил, я только изменил его, чтобы вычислить другие процентили.

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

Ответ 6

я бы, вероятно, использовал SQL Server 2005

row_number() over (порядок по счету)/(выберите счетчик (*) из результатов)

или что-то в этом роде.

Ответ 7

я бы сделал что-то вроде:

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

это право?