在MS SQL中计算百分位排名 [英] Calculating percentile rankings in MS SQL

问题描述

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

What's the best way to calculate percentile rankings (e.g. the 90th percentile or the median score) in MSSQL 2005?

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

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:

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

推荐答案

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

I would think that this would be the simplest solution:

SELECT TOP N PERCENT FROM TheTable ORDER BY TheScore DESC

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

Where N = (100 - desired percentile). So if you wanted all rows in the 90th percentile, you'd select the top 10%.

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

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." ?

好的，我想了更多关于您的问题，并提出了这种解释.您是否在问如何计算特定百分位数的截止分数?例如这样的事情:要进入第90个百分点，您的得分必须大于78.

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.

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

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

这篇关于在MS SQL中计算百分位排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！