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_durationsIf 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
Finally, a unified workspace for your SQL development
Get more done, together, with PopSQL and PostgreSQL