SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Computing Percentiles in SQL Server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 05/20/2004 :  10:47:15  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 05/21/2004 :  04:38:08  Show Profile  Reply with Quote
Interesting article.....and an even more enterprising and innovative approach to advertising your services....but at least you are offering something in return.
Go to Top of Page

Bill Wilkinson
Starting Member

USA
7 Posts

Posted - 12/21/2004 :  17:19:56  Show Profile  Reply with Quote
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.
Go to Top of Page

dalepres
Starting Member

1 Posts

Posted - 10/12/2005 :  09:28:45  Show Profile  Reply with Quote
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.
Go to Top of Page

sqlghost
Starting Member

23 Posts

Posted - 10/31/2006 :  07:51:50  Show Profile  Visit sqlghost's Homepage  Reply with Quote
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.
Go to Top of Page

ChrisBui2008
Starting Member

USA
3 Posts

Posted - 12/05/2008 :  16:41:14  Show Profile  Reply with Quote
Great Job SQLGhost! The modification to the query made it extremly fast!

Chris Bui
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000