Cloud

percentile_disc

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:

  • Discrete Percentile

PERCENTILE_DISC(fraction) WITHIN GROUP (ORDER BY order_list)

If multiple values share the same rank at the specified percentile, percentile_disc() returns the first one encountered in the ordering.

+ Parameters

  • fraction: Decimal value between 0 and 1 representing the desired percentile (for example, 0.25 for the 25th percentile).

    Multiple Discrete Percentile
    PERCENTILE_DISC(fractions) WITHIN GROUP (ORDER BY order_list)

If multiple values share the same rank at the specified percentile, percentile_disc() returns the first one encountered in the ordering.

+ Parameters

  • fractions: Array of decimal values between 0 and 1 representing the desired percentiles (for example, ARRAY[0.25, 0.50, 0.75, 0.90]).

Examples

The following example uses a simplified version of the film table from the Pagila database, containing 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');

The following query 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;

The query returns:

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