PERCENTILE_CONT()

Overview

PERCENTILE_CONT() is an ordered-set aggregate function used to compute continuous percentiles from a set of values. The continuous percentile returns an interpolated value based on the distribution of the input data, while multiple continuous percentiles return an array of results matching the shape of the fractions parameter with each non-null element replaced by the value corresponding to that percentile.

Syntax

The syntax for this function is as follows:

Continuous Percentile

PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY order_list)

This function is often used in conjunction with the WITHIN GROUP clause to specify how to order the data before calculating the percentile.

Parameters

- fraction: decimal value between 0 and 1 representing the desired percentile (e.g. 0.25 for the 25th percentile)

Multiple Continuous Percentile

PERCENTILE_CONT(fractions) WITHIN GROUP (ORDER BY order_list)

This function is often used in conjunction with the WITHIN GROUP clause to specify how to order the data before calculating the percentile.

Parameters

- fractions: array of decimal values between 0 and 1 representing the desired percentiles (e.g. ARRAY[0.25, 0.50, 0.75, 0.90])

Example

For the needs of this section we will use a simplified version of the film table from the Pagila database, that will contain only the title, length and rating columns.

DROP TABLE IF EXISTS film;
CREATE TABLE film (
  title text NOT NULL,
  length int,
  rating text
);
INSERT INTO film(title, length, rating) VALUES
  ('ATTRACTION NEWTON', 83, 'PG-13'),
  ('CHRISTMAS MOONSHINE', 150, 'NC-17'),
  ('DANGEROUS UPTOWN', 121, 'PG'),
  ('KILL BROTHERHOOD', 54, 'G'),
  ('HALLOWEEN NUTS', 47, 'PG-13'),
  ('HOURS RAGE', 122, 'NC-17'),
  ('PIANIST OUTFIELD', 136, 'NC-17'),
  ('PICKUP DRIVING', 77, 'G'),
  ('INDEPENDENCE HOTEL', 157, 'NC-17'),
  ('PRIVATE DROP', 106, 'PG'),
  ('SAINTS BRIDE', 125, 'G'),
  ('FOREVER CANDIDATE', 131, 'NC-17'),
  ('MILLION ACE', 142, 'PG-13'),
  ('SLEEPY JAPANESE', 137, 'PG'),
  ('WRATH MILE', 176, 'NC-17'),
  ('YOUTH KICK', 179, 'NC-17'),
  ('CLOCKWORK PARADISE', 143, 'PG-13');

This query calculates the median film length within each rating category.

SELECT rating, PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY length) AS "50th percentile" FROM film
GROUP BY rating; 

By executing the code above we will get the following output:

 rating | 25th percentile 
--------+-----------------
 PG-13  |              74
 PG     |           113.5
 NC-17  |           133.5
 G      |            65.5
(4 rows)