T-SQL thoughts about the 95th percentile

re: T-SQL thoughts about the 95th percentile

RichB — Tue, 23 Aug 2011 10:42:19 GMT

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

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 :)

re: T-SQL thoughts about the 95th percentile

Rob Farley — Tue, 23 Aug 2011 11:16:59 GMT

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.

re: T-SQL thoughts about the 95th percentile

Alex Whittles — Wed, 24 Aug 2011 07:19:05 GMT

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!

re: T-SQL thoughts about the 95th percentile

Rob Farley — Wed, 24 Aug 2011 07:47:17 GMT

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

re: T-SQL thoughts about the 95th percentile

Geoff — Thu, 25 Aug 2011 14:14:07 GMT

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.

re: T-SQL thoughts about the 95th percentile

Rob Farley — Thu, 25 Aug 2011 15:20:19 GMT

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

re: T-SQL thoughts about the 95th percentile

Lars Hammarberg — Mon, 29 Aug 2011 19:20:47 GMT

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

Hilarious! Thank you!

/Lars H

Analytic functions – they’re not aggregates

Rob Farley — Tue, 10 Apr 2012 01:53:52 GMT

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