Cloud

percentile_cont

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:

  • 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 (for example, 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 (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');

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;

The query returns:

 rating | 50th percentile
--------+-----------------
 PG-13  |           112.5
 PG     |             121
 NC-17  |             150
 G      |              77
(4 rows)