Article: Computing Percentiles in SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Computing Percentiles in SQL Server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-20 : 10:47:15

Say you score 570 on the verbal section of the SAT. How well did you do? According to the College Board, 570 falls into the 71st percentile. This means you did better than 71% of the other students who took the test. Percentiles are a useful way to present rankings among a large population. You may want to add percentiles to reports you produce with SQL. This article by Bob Newstadt shows how to calculate a percentile for a given group of scores. It also covers finding a score for a given percentile.

Article Link.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-05-21 : 04:38:08

Interesting article.....and an even more enterprising and innovative approach to advertising your services....but at least you are offering something in return.

Bill Wilkinson
Starting Member

7 Posts

Posted - 2004-12-21 : 17:19:56

Ummm...aren't there some much easier ways to do some of these things, using TSQL?

Example: "What is the score that represnts the 71st percentile?"

SELECT MAX(score) FROM
( SELECT TOP 71 PERCENT score FROM scores ORDER BY score )

Yes, I see that doesn't cover, by any means, what the article covers. But for some questions...

Credit to Xander Sherry for rubbing my nose in this one.

dalepres
Starting Member

1 Post

Posted - 2005-10-12 : 09:28:45

Mr. Wilkerson's reply returns the maximum value from the data that is within the percentile but does not give the actual value that would, if it existed, match the specified percentile. In other words, if you used TOP 15 PERCENT, his method would return the maximum data value less than or equal to the 15th percentile but not the actual value of the 15th percentile. If you are trying to match Excel's PERCENTILE function, you have to use the interpolation to get the actual value.

sqlghost
Starting Member

23 Posts

Posted - 2006-10-31 : 07:51:50

This percentile function takes a looooong time.
Using 10,000 rows of data it took 2 minutes and 10 seconds on my computer.
This is how to get the 10,000 rows into the table:

Declare @TestScores table (StudentID int identity(1,1), Score int)
insert @TestScores (Score) Values (20)
insert @TestScores (Score) Values (03)
insert @TestScores (Score) Values (40)
insert @TestScores (Score) Values (45)
insert @TestScores (Score) Values (50)
insert @TestScores (Score) Values (20)
insert @TestScores (Score) Values (90)
insert @TestScores (Score) Values (20)
insert @TestScores (Score) Values (11)
insert @TestScores (Score) Values (30)

while @@ROWCOUNT > 0
insert @TestScores select t.Score from @TestScores t
cross join
(select max (StudentID) MaxRowNum from @TestScores) x
where
t.StudentID <= 10000 - x.MaxRowNum

That code puts the same scores in 1000 times, so you should get the same results as in the original article.

Here is a modification that is a bit faster (takes less than 1 second):

declare @pp float
set @pp = .5

declare @k int, @d float, @ax float, @bx float
declare @values table (i int identity(1,1), x float)

insert @values select Score from @TestScores order by Score

select @k=floor(kf), @d=kf-floor(kf)
from (select 1+@pp*(count(*)-1) as kf from @values) as x1

select @ax=x from @values where i=@k
select @bx=x from @values where i=@k+1 

select @pp as factor, 
   dbo.LERP(@d, 0.0, 1.0, @ax, @bx) as percentile

Personally I think LINTERP would be a nicer name than LERP.

ChrisBui2008

3 Posts

Posted - 2008-12-05 : 16:41:14

Great Job SQLGhost! The modification to the query made it extremly fast!

Chris Bui

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources