在 MS SQL 中计算百分位排名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/79688/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculating percentile rankings in MS SQL
提问by Soldarnal
What's the best way to calculate percentile rankings (e.g. the 90th percentile or the median score) in MSSQL 2005?
在 MSSQL 2005 中计算百分位排名（例如第 90 个百分位或中位数）的最佳方法是什么？
I'd like to be able to select the 25th, median, and 75th percentiles for a single column of scores (preferably in a single record so I can combine with average, max, and min). So for example, table output of the results might be:
我希望能够为单列分数选择第 25 个、中位数和第 75 个百分位数（最好在单个记录中，以便我可以将平均值、最大值和最小值结合起来）。例如，结果的表输出可能是：
Group MinScore MaxScore AvgScore pct25 median pct75
----- -------- -------- -------- ----- ------ -----
T1 52 96 74 68 76 84
T2 48 98 74 68 75 85
回答by Matt
I would think that this would be the simplest solution:
我认为这将是最简单的解决方案：
SELECT TOP N PERCENT FROM TheTable ORDER BY TheScore DESC
Where N = (100 - desired percentile). So if you wanted all rows in the 90th percentile, you'd select the top 10%.
其中 N = (100 - 所需的百分位数)。因此，如果您想要第 90 个百分位数中的所有行，则应选择前 10%。
I'm not sure what you mean by "preferably in a single record". Do you mean calculate which percentile a given score for a single record would fall into? e.g. do you want to be able to make statements like "your score is 83, which puts you in the 91st percentile." ?
我不确定您所说的“最好在单个记录中”是什么意思。您的意思是计算单个记录的给定分数会落入哪个百分位？例如，您是否希望能够做出诸如“您的分数是 83，这使您处于第 91 个百分位”之类的陈述。?
EDIT: OK, I thought some more about your question and came up with this interpretation. Are you asking how to calculate the cutoff score for a particular percentile? e.g. something like this: to be in the 90th percentile you must have a score greater than 78.
编辑：好的，我对您的问题进行了更多思考并提出了这种解释。您是在问如何计算特定百分位数的截止分数吗？例如这样的事情：要进入第 90 个百分位数，您的分数必须大于 78。
If so, this query works. I dislike sub-queries though, so depending on what it was for, I'd probably try to find a more elegant solution. It does, however, return a single record with a single score.
如果是这样，则此查询有效。不过我不喜欢子查询，所以根据它的用途，我可能会尝试找到更优雅的解决方案。但是，它确实返回具有单个分数的单个记录。
-- 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
回答by Elizabeth
Check out the NTILE command -- it will give you percentiles pretty easily!
看看 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)
回答by Paul
How about this:
这个怎么样：
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
回答by Soldarnal
I've been working on this a little more, and here's what I've come up with so far:
我一直在为此做更多的工作，到目前为止，这是我想出的：
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
Then in another stored procedure I do this:
然后在另一个存储过程中我这样做：
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
It still doesn't do quite what I'm looking for. This will get the stats for all tests; whereas I would like to be able to select from a TestScores table that has multiple different tests in it and get back the same stats for each different test (like I have in my example table in my question).
它仍然不能完全满足我的要求。这将获得所有测试的统计数据；而我希望能够从包含多个不同测试的 TestScores 表中进行选择，并为每个不同的测试返回相同的统计数据（就像我在我的问题中的示例表中所做的那样）。
回答by Kay Aliu
The 50th percentile is same as the median. When computing other percentile, say the 80th, sort the data for the 80 percent of data in ascending order and the other 20 percent in descending order, and take the avg of the two middle value.
第 50 个百分位数与中位数相同。在计算其他百分位数时，比如第 80 个，将 80% 的数据按升序排序，其余 20% 的数据按降序排序，并取两个中间值的平均值。
NB: The median query has been around for a long time, but cannot remember where exactly I got it from, I have only amended it to compute other 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
回答by Kay Aliu
i'd probably use a the sql server 2005
我可能会使用 sql server 2005
row_number() over (order by score ) / (select count(*) from scores)
row_number() over（按分数排序）/（从分数中选择计数（*））
or something along those lines.
或类似的规定。
回答by Kay Aliu
i'd do something like:
我会做这样的事情：
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
is this right?
这是正确的吗？
回答by Debasmita
Percentile is calculated by
百分位数的计算方式为
(Rank -1) /(total_rows -1)when you sort values in ascending order.
(Rank -1) /(total_rows -1)当您按升序对值进行排序时。
The below query will give you percentile value between 0 and 1. Person with lowest marks will have 0 percentile.
下面的查询将为您提供 0 到 1 之间的百分位值。得分最低的人将有 0 个百分位。
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
