在MSSQL 2005中计算百分位排名(例如第90个百分位或者中位数)的最佳方法是什么？

我希望能够为单列分数选择第25，中位数和第75个百分位数(最好是在单个记录中，这样我就可以与平均值，最大值和最小值相结合)。因此，例如，结果的表输出可能是：

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

解决方案

回答

我可能会使用SQL Server 2005

row_number() over (order by score ) / (select count(*) from scores)

或者类似的规定。

回答

我会做类似的事情：

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

这是正确的吗？

回答

我认为这将是最简单的解决方案：

SELECT TOP N PERCENT FROM TheTable ORDER BY TheScore DESC

其中N =(100个所需百分位数)。因此，如果我们希望所有行都位于第90个百分位，则可以选择前10％。

我不确定"最好在一条记录中"是什么意思。意思是计算单个记录的给定分数将落入哪个百分位吗？例如我们是否希望能够发表这样的陈述："分数是83，这使我们处于第91个百分位"。 ？

编辑：好的，我想过更多关于问题，并提出了这种解释。我们是否在问如何计算特定百分位数的截止分数？例如这样的事情：要进入第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

回答

我已经在做更多的工作了，到目前为止，这是我要提出的：

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表中进行选择，并为每个不同的测试取回相同的统计信息(就像我在问题示例表中一样)。