PERCENTILE_CONT

The PERCENTILE_CONT function calculates a precise percentile using linear interpolation. It sorts the values in a column in ascending order and returns an interpolated value for the given percentile.

Syntax

-- As an aggregate function
PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>])

-- As a window function
PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])

Parameters

col_name: Required. A column of type DOUBLE or DECIMAL.
percentile: Required. A DOUBLE constant in the range [0, 1].
isIgnoreNull: Optional. A BOOLEAN constant that specifies whether to ignore NULL values. The default is TRUE. If set to FALSE, NULL values are treated as the minimum value.
partition_clause and orderby_clause: See Window Functions for details.

Return value

Returns the calculated percentile as a DOUBLE value.

Examples

Example 1: Compute precise percentiles within a window, ignoring NULL values.

SELECT
  PERCENTILE_CONT(x, 0) OVER() AS min,
  PERCENTILE_CONT(x, 0.01) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5) OVER() AS median,
  PERCENTILE_CONT(x, 0.9) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1) OVER() AS max
FROM VALUES(0D),(3D),(NULL),(1D),(2D) AS tbl(x) LIMIT 1;

-- The following result is returned:
+------------+-------------+------------+--------------+------------+
| min        | percentile1 | median     | percentile90 | max        | 
+------------+-------------+------------+--------------+------------+
| 0.0        | 0.03        | 1.5        | 2.7          | 3.0        | 
+------------+-------------+------------+--------------+------------+

Example 2: Compute precise percentiles within a window, treating NULL values as the minimum value.

SELECT
  PERCENTILE_CONT(x, 0, false) OVER() AS min,
  PERCENTILE_CONT(x, 0.01, false) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5, false) OVER() AS median,
  PERCENTILE_CONT(x, 0.9, false) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1, false) OVER() AS max
FROM VALUES(0D),(3D),(NULL),(1D),(2D) AS tbl(x) LIMIT 1;

-- The following result is returned:
+------------+-------------+------------+--------------+------------+
| min        | percentile1 | median     | percentile90 | max        | 
+------------+-------------+------------+--------------+------------+
| NULL       | 0.0         | 1.0        | 2.6          | 3.0        | 
+------------+-------------+------------+--------------+------------+

Related functions

PERCENTILE_CONT can be used as an aggregate function or a window function.

For more functions that aggregate data across multiple records, see Aggregate Functions.
For more functions that perform calculations across a window of rows, such as summation and ranking, see Window Functions.