Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to find n>1 percentiles of an unsorted column in PostgreSQL. For example the 20th, 40th, 60th, 80th and 100th percentile.

An obvious solution is to count and sort the column and then do a look but I'm hoping for a better solution. Any ideas?

P.S. I have found a good solution for MySQL but can't translate it to psql

share|improve this question
2  
Have you considered windowing functions, eg cume_dist()? –  Jack Douglas Apr 26 '12 at 12:55
    
I had not, many thanks. –  Bob Jansen Apr 26 '12 at 13:56
    
Postgres has PERCENT_RANK() –  Phil Apr 26 '12 at 14:07

1 Answer 1

up vote 10 down vote accepted

I have come up with the following:

select cume, max(var) AS max_var
from (
   select var
        , ntile(5) over (order by var) as cume
   from table
   ) as tmp
group by cume
order by cume;

It selects the maximum of each group that is divided using ntile().

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.