Link Search Menu Expand Document

PERCENTILE_CONT

Calculates a percentile over a partition, assuming a continuous distribution of values defined. Results are interpolated, rather than matching any of the specific column values.

PERCENTILE_CONT is available as an Window function. See also PERCENTILE_DISC, which returns a percentile over a partition equal to a specific column value. For more information on usage, please refer to Window Functions.

Syntax

PERCENTILE_CONT( <value> ) WITHIN GROUP ( ORDER BY <order_by> [ { ASC | DESC } ] ) [ OVER ( PARTITION BY <partition_by> ) ]

Parameters

Parameter Description Supported input types
<value> A value between 0.0 and 1.0. DOUBLE PRECISION, REAL
<order_by> An expression used for the order by clause. Any numeric type
<partition_by> An expression used for the partition by clause. Any

Return Types

DOUBLE PRECISION

Example

The example below calculates the max percentile value based on continuous distribution of players, partitioned by game level.

SELECT
	nickname,
	PERCENTILE_CONT(1.0) WITHIN GROUP (ORDER BY current_score) OVER (PARTITION BY leve;) AS percentile
FROM
	players;

Returns:

nickname percentile
kennethpark 90
sabrina21 85
ymatthews 80
rileyjon 75