关于sql server：在MS SQL中计算百分位排名

Calculating percentile rankings in MS SQL

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

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

1
2
3
4

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

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

1	SELECT TOP N PERCENT FROM TheTable ORDER BY TheScore DESC

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

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

编辑：好的，我想过更多关于您的问题，并提出了这种解释。您是否在问如何计算特定百分位数的截止分数？例如诸如此类：要进入第90个百分点，您的得分必须大于78。

如果是，则此查询有效。不过，我不喜欢子查询，因此，根据子查询的目的，我可能会尝试找到更优雅的解决方案。但是，它确实返回具有单个分数的单个记录。

1
2
3
4

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

签出NTILE命令-它将很容易为您提供百分位数！

1
2
3
4
5
6
7
8

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)

如何？

1
2
3
4
5
6

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

第50个百分位数与中位数相同。在计算其他百分位数时，例如说80，将80％的数据按升序排序，并将其余20％的数据按降序排序，然后取两个中间值的平均值。

NB：中位数查询已经存在很长时间了，但是不记得我确切地从哪里得到的，我只是修改了它以计算其他百分位数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

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

我正在为此做更多的工作，到目前为止，这是我想出的内容：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

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

然后在另一个存储过程中，我这样做：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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

百分位数由

计算

(Rank -1) /(total_rows -1)当您按升序对值进行排序时。

下面的查询将为您提供0到1之间的百分数值。标记最低的人的百分位数为0。

1
2
3
4
5
6
7
8

SELECT Name, marks, (rank_1-1)/((SELECT COUNT(*) AS total_1 FROM TABLE)-1)AS percentile_rank
FROM
(
SELECT Name,
Marks,
RANK() OVER (ORDER BY Marks) AS rank_1
FROM TABLE
) AS A

我会做类似的事情：

1
2
3
4
5
6

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

这是对的吗？

我可能会使用sql server 2005