Postgresql – Fast general method to calculate percentiles

postgresqlstatistics

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

Best Answer

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