在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
row_number() over (order by score ) / (select count(*) from scores)
或类似的内容。
