patternsqlModerate

Fast general method to calculate percentiles

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

fastmethodgeneralcalculatepercentiles

Problem

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

Solution

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

Code Snippets

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;

Context

StackExchange Database Administrators Q#17086, answer score: 12

Revisions (0)

No revisions yet.