How to Calculate Percentile in PostgreSQL

Last updated on May 17th, 2020 at 06:09 am

Calculating Percentile helps you rank data for analysis as well as calculate median for a set of values. Here’s how to calculate percentile in PostgreSQL using percentile_cont window function. We will also look at how to calculate 50th percentile, 90th percentile, 95th percentile and nth percentile in PostgreSQL, since they are commonly used in data analysis.

Table of Contents

How to Calculate Percentile in PostgreSQL

We will calculate percentile in PostgreSQL using percentile_cont window function

Let’s say you have the following table sales(order_date,amount) and you want to calculate 50th percentile.

postgres=# create table sales(order_date date,sale int);

postgres=# insert into sales values('2020-04-01',210),
           ('2020-04-02',125),('2020-04-03',150),('2020-04-04',230),
           ('2020-04-05',200),('2020-04-10',220),('2020-04-06',25),
           ('2020-04-07',215),('2020-04-08',300),('2020-04-09',250);

postgres=# select * from sales;
 order_date | sale
------------+------
 2020-04-01 |  210
 2020-04-02 |  125
 2020-04-03 |  150
 2020-04-04 |  230
 2020-04-05 |  200
 2020-04-10 |  220
 2020-04-06 |   25
 2020-04-07 |  215
 2020-04-08 |  300
 2020-04-09 |  250

Bonus Read : How to Fill Missing Dates in PostgreSQL

Here’s the query to calculate 50th percentile in PostgreSQL. You can use the same query to calculate median in PostgreSQL.

postgres=# SELECT PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY sale) FROM sales;
 percentile_cont
-----------------
           212.5

In the above query, we use percentile_cont window function that allows you to define what percentile (e.g 0.5 that is 50th percentile) you want to calculate. Then we also use WITHIN GROUP clause to calculate percentile within the group of sale values. We also use ORDER BY clause to sort the sale values before we calculate percentile in PostgreSQL.

When you use percentile_cont window function, PostgreSQL will interpolate the median value as the average of 2 middle values (210, 215), in case there are 2 middle values. That is why it returns 212.5

Bonus Read : How to Calculate Moving Average in PostgreSQL

When you can calculate percentile in PostgreSQL, if you don’t want PostgreSQL to interpolate but only return the closest value, use percentile_disc window function instead. Here’s an example

postgres=# SELECT PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY sale) FROM sales;
 percentile_disc
-----------------
             210

In this case, it will return the one of the middle values, in case there are 2 middle values.

Bonus Read : How to Import CSV File into PostgreSQL

How to Calculate 90th Percentile in PostgreSQL

Similarly, you can calculate 90th percentile in PostgreSQL using percentile_cont. We change 0.5 to 0.9 in percentile_cont function

postgres=# SELECT PERCENTILE_CONT(0.9) WITHIN GROUP(ORDER BY sale) FROM sales;
percentile_cont
-----------------
255

You can also calculate 90th percentile in PostgreSQL using percentile_disc

postgres=# SELECT PERCENTILE_DISC(0.9) WITHIN GROUP(ORDER BY sale) FROM sales;
percentile_disc
-----------------
250

How to Calculate 95th Percentile in PostgreSQL

Similarly, you can calculate 95th percentile in PostgreSQL, using percentile_cont

postgres=# SELECT PERCENTILE_CONT(0.95) WITHIN GROUP(ORDER BY sale) FROM sales;
percentile_cont
-----------------
277.5

You can also calculate 95th percentile in PostgreSQL using percentile_disc

postgres=# SELECT PERCENTILE_DISC(0.95) WITHIN GROUP(ORDER BY sale) FROM sales;
percentile_disc
-----------------
300

How to Calculate nth Percentile in PostgreSQL

Similarly, you can calculate nth percentile in PostgreSQL using percentile_cont. We basically supply n/100 in percentile_cont function

postgres=# SELECT PERCENTILE_CONT(n/100) WITHIN GROUP(ORDER BY sale) FROM sales;

You can also calculate 90th percentile in PostgreSQL using percentile_disc

postgres=# SELECT PERCENTILE_DISC(n/100) WITHIN GROUP(ORDER BY sale) FROM sales;

After you calculate percentile in PostgreSQL, you can use a PostgreSQL Reporting Tool to plot the percentile values along with sale values on a line chart to analyze the difference and also do goal setting. For that we will join the calculated 50th percentile value with original sales table as shown below

postgres=# select * from sales,
(SELECT PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY sale) FROM sales) temp 
order by order_date;
 order_date | sale | percentile_cont
------------+------+-----------------
 2020-04-01 |  210 |           212.5
 2020-04-02 |  125 |           212.5
 2020-04-03 |  150 |           212.5
 2020-04-04 |  230 |           212.5
 2020-04-05 |  200 |           212.5
 2020-04-06 |   25 |           212.5
 2020-04-07 |  215 |           212.5
 2020-04-08 |  300 |           212.5
 2020-04-09 |  250 |           212.5
 2020-04-10 |  220 |           212.5

Here’s a line chart that shows daily sales along with percentile, plotted using Ubiq