PERCENTILE_CONT Function and Linear Interpolation Overview - MaxCompute

PERCENTILE_CONT computes a percentile value using linear interpolation. It sorts the values in the specified column in ascending order and returns the interpolated value at the given percentile.

Syntax

Aggregate function

PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>])

Window function

PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])

Parameters

Parameter	Required	Type	Description
`col_name`	Yes	DOUBLE or DECIMAL	The column to compute the percentile on.
`percentile`	Yes	DOUBLE constant	The target percentile. Must be in the range [0, 1].
`isIgnoreNull`	No	BOOLEAN constant	Whether to ignore NULL values. Defaults to `TRUE`. When set to `FALSE`, NULL values are treated as the minimum value during sorting.
`partition_clause`, `orderby_clause`	No	—	Window function clauses. For syntax details, see Window functions.

Return value

Returns the computed percentile as a DOUBLE value.

Usage notes

percentile must be a DOUBLE constant.
When isIgnoreNull is FALSE, NULL is treated as the minimum value and appears at the lowest percentile positions. For example, percentile = 0 returns NULL.

Examples

Example 1: Compute percentiles in a window, ignoring NULL values (default)

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;

NULL is excluded from the computation. The non-NULL values are [0, 1, 2, 3]. The values at the 0.01 percentile (0.03) and 0.9 percentile (2.7) are interpolated between adjacent data points.

+------------+-------------+------------+--------------+------------+
| min        | percentile1 | median     | percentile90 | max        |
+------------+-------------+------------+--------------+------------+
| 0.0        | 0.03        | 1.5        | 2.7          | 3.0        |
+------------+-------------+------------+--------------+------------+

Example 2: Compute percentiles in a window, treating NULL 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;

NULL is sorted as the minimum value, so the dataset becomes [NULL, 0, 1, 2, 3]. Percentile 0 returns NULL, and the remaining percentiles shift accordingly.

+------------+-------------+------------+--------------+------------+
| min        | percentile1 | median     | percentile90 | max        |
+------------+-------------+------------+--------------+------------+
| NULL       | 0.0         | 1.0        | 2.6          | 3.0        |
+------------+-------------+------------+--------------+------------+

Related functions

PERCENTILE_CONT is both an aggregate function and a window function.

For other aggregate functions such as average and sum, see Aggregate functions.
For window functions that perform calculations over a defined data window, see Window functions.