Sign In

How to Calculate Percentiles in PostgreSQL

Let's say we want to look at the percentiles for query durations. We can use PostgreSQL's percentile_cont function to do that:

select
  percentile_cont(0.25) within group (order by duration asc) as percentile_25,
  percentile_cont(0.50) within group (order by duration asc) as percentile_50,
  percentile_cont(0.75) within group (order by duration asc) as percentile_75,
  percentile_cont(0.95) within group (order by duration asc) as percentile_95
from query_durations

If we want to view those percentiles by day:

select
  day,
  percentile_cont(0.25) within group (order by duration asc) over (partition by day) as percentile_25,
  percentile_cont(0.50) within group (order by duration asc) over (partition by day) as percentile_50,
  percentile_cont(0.75) within group (order by duration asc) over (partition by day) as percentile_75,
  percentile_cont(0.95) within group (order by duration asc) over (partition by day) as percentile_95
from query_durations
group by 1
order by 1 asc

Previous

How to Use Lateral Joins

Next

How to Get the First Row per Group

Ready for a modern SQL editor?

Get started for free