Understanding percentile_cont() and percentile_disc() in PostgreSQL

PostgreSQL has two functions to calculate the percentile for a list of values at any percentage: percentile_cont() and percentile_disc(). These two functions work similarly, but they differ in how they produce the final result. Both are used with ordered-set aggregates returned by the WITHIN GROUP clause.

The percentile_disc() function returns a value from the input set that is the closest to the percentile requested. The value returned will actually exist in the set. 

Here's the percentile_disc() syntax:

SELECT percentile_disc(<fraction double precision>) WITHIN GROUP (<sort_expression>) FROM <table>;Copy

The percentile_cont() function returns an interpolated value between multiple values based on the distribution. It is more accurate, but it may return a fractional value between two values in the input set.

percentile_cont() syntax:

SELECT percentile_cont(<fraction double precision>) WITHIN GROUP (<sort_expression>) FROM <table>;Copy

Examples

• Calculating the median

• Calculating multiple percentiles

• Calculating a series of percentiles

For the following examples, we will use this set of weather data stored in a table called city_data:

Calculating the Median

The median is also known as the 50th percentile. You can calculate it from the dataset with the following query:

SELECT percentile_disc(0.5) WITHIN GROUP ( ORDER BY temperature) FROM city_data;Copy

The result is:

Because the query used percentile_disc(), the result is a value that exists in the dataset. If you want to find the true median, it is not a value in this data, and you have to use percentile_cont(). Here is the query:

SELECT percentile_cont(0.5) WITHIN GROUP ( ORDER BY temperature) FROM city_data;Copy

And the result:

But since there are two cities, you might want to calculate the median temperature of each by adding a GROUP BY clause. Here is that query:

SELECT city, percentile_cont(0.5) WITHIN GROUP ( ORDER BY temperature) FROM city_data GROUP BY city;Copy

The results is:

Calculating Multiple Percentiles

For this example, we are going to use a database table called conditions that contains these values:

Let’s say that we want to calculate various percentiles for the humidity for each device. Here is an example query:

SELECT device_id, percentile_cont(0.25) WITHIN GROUP( ORDER BY humidity) AS percentile_25, percentile_cont(0.50) WITHIN GROUP( ORDER BY humidity) AS percentile_50, percentile_cont(0.75) WITHIN GROUP( ORDER BY humidity) AS percentile_75, percentile_cont(0.95) WITHIN GROUP( ORDER BY humidity) AS percentile_95 FROM conditions GROUP BY device_id ;Copy

Here is a part of the result:

Calculating a Series of Percentiles

For this example, we are going back to our original city_data dataset because this query can take a long time to run on a big dataset. We are going to use the generate_series() to create every single whole percentage and then use those values in percentile_cont. Here is the query:

SELECT city, percentile, percentile_cont(p) WITHIN GROUP ( ORDER BY temperature) FROM city_data, generate_series(0.01, 1, 0.01) AS percentile GROUP BY city, percentile;Copy

Here is a selection of the results since the query generates 200 rows of them:

Why Use the Timescale approx_percentile() Function Instead of PostgreSQL Percentile Functions?

Calculating the percentile over large datasets, like time-series data in a Timesscale database, can involve a lot of expensive calculations. It can increase the memory footprint of the database, result in higher network costs, and make streaming data unfeasible. The aggregates are also not partializable or parallelizable.

Many times you don’t need this type of accuracy, and approximate percentile calculations will be close enough. This is why Timescale introduced the approx_percentile() hyperfunction. The approx_percentile() function implements the UDDSketch algorithm that uses a modified histogram to approximate the shape of a distribution. This allows for calculating a “good enough” percentile without needing to use all the data or ordering it before it returns the result.

approx_percentile() syntax:

approx_percentile(     percentile DOUBLE PRECISION,     sketch  uddsketch ) RETURNS DOUBLE PRECISIONCopy

The second parameter is the sketch to perform the approx_percentile on and is usually returned from a percentile_agg() call. Here is an example query:

SELECT     approx_percentile(0.01, percentile_agg(data)) FROM generate_series(0, 100) data;Copy

Result:

Next Steps

To learn more about how to use percentile_cont() and percentile_disc() in PostgreSQL, you can see the PostgreSQL documentation. 

• To find out more about Timescale’s approx_percentile() function, you can read more about it in the Timescale documentation. 

For examples of how to use these functions in your queries, see these sections of the Timescale documentation:

• Additional approx_percentile() documentation

• Info on aggregation and accessor functions

• Advanced percentile aggregation

• Percentile approximation advanced aggregation methods

• Percentile approximation

PostgreSQL Percentile Functions FAQ

Q: What is the difference between percentile_cont() and percentile_disc() functions in PostgreSQL?

A: The percentile_cont() function returns an interpolated value that may not exist in the original dataset, providing a more accurate statistical representation. In contrast, percentile_disc() returns an actual value from the dataset that is closest to the requested percentile. Both functions are used with WITHIN GROUP to specify the ordering of values.

Q: How do I calculate the median (50th percentile) of a dataset in PostgreSQL?

A: You can calculate the median using either percentile function with 0.5 as the parameter. For example, SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY temperature) FROM city_data; will return the interpolated median value, while percentile_disc(0.5) would return an actual value from the dataset.

Q: Can I calculate multiple percentiles in a single query?

A: Yes, you can calculate multiple percentiles in one query by using multiple percentile function calls. For example: SELECT percentile_cont(0.25) WITHIN GROUP(ORDER BY humidity) AS p25, percentile_cont(0.5) WITHIN GROUP(ORDER BY humidity) AS p50, percentile_cont(0.75) WITHIN GROUP(ORDER BY humidity) AS p75 FROM conditions GROUP BY device_id; will return the 25th, 50th, and 75th percentiles.

Q: How can I calculate percentiles by group in PostgreSQL?

A: You can calculate percentiles for each group by adding a GROUP BY clause to your query. For example, SELECT city, percentile_cont(0.5) WITHIN GROUP (ORDER BY temperature) FROM city_data GROUP BY city; will return the median temperature for each city in the dataset.

Q: What is the approx_percentile() function in Timescale, and when should I use it?

A: The approx_percentile() function is a Timescale hyperfunction that approximates percentiles using the UDDSketch algorithm, which is much more efficient for large datasets. You should use it when exact precision isn't required, but performance is important, especially with time-series data, as it requires less memory and computational resources than PostgreSQL's native percentile functions.