9

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

12

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().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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