​
Overview

PERCENTILE_DISC() is an ordered-set aggregate function used to compute discrete percentiles from a set of values. The discrete percentile returns the first input value, which position in the ordering equals or exceeds the specified fraction, while multiple discrete percentiles return an array of results matching the shape of the fractions parameter, with each non-null element being replaced by the input value corresponding to that percentile.

Syntax

The syntax for this function is as follows:

PERCENTILE_DISC(fraction) WITHIN GROUP (ORDER BY order_list)
If multiple values share the same rank at the specified percentile, PERCENTILE_DISC() will return the first one encountered in the ordering.

Parameters

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

​
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,
  rating text,
  length int
);
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');

The query below calculates the quartile, median and the third quartile of film lengths:

SELECT rating, percentile_disc(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY length) AS "quartiles" FROM film
GROUP BY rating;

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

 rating |   quartiles   
--------+---------------
 G      | {54,77,125}
 PG     | {106,121,137}
 PG-13  | {47,83,142}
 NC-17  | {131,150,176}
(4 rows)
PERCENTILE_CONTAVG