THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

T-SQL thoughts about the 95th percentile

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at: http://blogs.lobsterpot.com.au/2011/08/23/t-sql-thoughts-about-the-95th-percentile/

Published Tuesday, August 23, 2011 5:37 PM by Rob Farley

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

RichB said:

Being lazy I just take the top 5 percent ordered desc, and take the top 1 off that ordered asc.

ie

select top 1 * from (select top 5 percent * from table ordered by val desc) ordered by val asc

Sure, it's ugly, but it's easy :)

August 23, 2011 6:42 AM
 

Rob Farley said:

Yeah, there are definitely a bunch of ways you can work out what you count as your 95th. This method was as much an exercise in picturing a solution as anything, and trying to be sure of how things work when you don't have a nice round number.

August 23, 2011 7:16 AM
 

Alex Whittles said:

Hi Rob

Very interesting post and a great technique, thanks for sharing it.

The difference between your approach and any technique using stddev is essentially whether you want the top % of a sample or of the entire population. Stddev takes the limited sample data available and projects the distribution characteristics of the much larger unknown population.

Both techniques are equally valid, and as you pointed out, depend on what definition of percentile is required. I.e. Do you have all available data or just a sample of it.

Given this, hopefully you should avoid a bashing from the stats folk!

August 24, 2011 3:19 AM
 

Rob Farley said:

Ah, true. And hopefully statisticians won't mind my method either. Thanks Alex.

August 24, 2011 3:47 AM
 

Geoff said:

What I did at one point in time in algorithm/psuedo-code form

-get the total number of the set

-get the ideal index (0.95 * N)

-get the entry right below that index and right above

-take the value of the one below

-take the difference of the one below and above

-add the fractional difference based on the index between the two numbers to the lower value

I hope that makes sense. If I want greater speed I just round and use the top function or row_number functions.

August 25, 2011 10:14 AM
 

Rob Farley said:

Yes, that's the algorithm I applied here. But you have to make sure you subtract one and add it back again afterwards, because if you have just two numbers, you have one range between those two. Your point should be 1.95 not 1.9.

Rob

August 25, 2011 11:20 AM
 

Lars Hammarberg said:

"95% of statisticians may be offended at this post"

Hilarious! Thank you!

/Lars H

August 29, 2011 3:20 PM
 

Rob Farley said:

SQL 2012 brings us a bunch of new analytic functions , together with enhancements to the OVER clause

April 9, 2012 9:53 PM
 

Ara said:

This is very useful.

Many thanks.

August 8, 2013 4:42 PM
 

Perastikos said:

Hello guys, that looks really interesting

my question is , if i want to use the final percentile function which part of code should i use?

thanks in advanced

February 3, 2016 7:39 AM
 

Rob Farley said:

Then change the .975 to .99

February 3, 2016 7:53 AM
 

Perastikos said:

Mr. Rod are u answering to me?

February 3, 2016 8:03 AM
 

Rob Farley said:

Yes, Perastikos, I am.

February 3, 2016 8:08 AM
 

Perastikos said:

Sorry i gou confused,

i have table with these values

2.56280

0.10534

and i want to take percentile of 90 of these values, which picture of your code should i execute?

i want to manage same think like excel, that uses this formula :

PERCENTILE(A2:A4;J2)

February 3, 2016 8:10 AM
 

Rob Farley said:

Use the second-last one, and change the 0.975 value to whatever you want.

February 3, 2016 5:54 PM
 

Perastikos said:

Hello again, i run the code from second last picture, i have those two values in my table,

5.04799 and 6.11519

i want to take 90% and 10% percentile of these values, when i run the code twice for 90% and 10% i take exactly the same values in rangetop :

5.17092

in excel i take for 90% : 6% and 10% : 5%

any help?

February 4, 2016 2:28 AM
 

sccsc said:

xcsc

December 27, 2016 7:31 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

News

News? Haven't you read my blog?

My Company


Can't find something?

Contact Me

IM: rob_farley@hotmail.com
Twitter: @rob_farley
Skype: rob_farley
E: rob_farley@hotmail.com

MVP (SQL Server)




Certifications








Adelaide SQL UG

Privacy Statement