percentile_cont aggregate function (Databricks SQL)

Returns the value that corresponds to the percentile of the provided sortKeys using a continuous distribution model.

Syntax

percentile_cont ( percentile )
       WITHIN GROUP (ORDER BY sortKey [ASC | DESC] )

Arguments

  • percentile: A numeric literal between 0 and 1 or a literal array of numeric literals, each between 0 and 1.

  • sortKey: A numeric expression over which the percentile will be computed.

  • ASC or DESC: Optionally specify whether the percentile is computed using ascending or descending order. The default is ASC.

Returns

DOUBLE if percentile is numeric, or an ARRAY of DOUBLE if percentile is an ARRAY.

The aggregate function returns the interpolated percentile within the group of sortKeys.

Examples

-- Return the median, 40%-ile and 10%-ile.
> SELECT percentile_cont(array(0.5, 0.4, 0.1)) WITHIN GROUP (ORDER BY col)
    FROM VALUES (0), (1), (2), (10) AS tab(col);
 [1.5, 1.2000000000000002, 0.30000000000000004]

-- Return the interpolatd median.
> SELECT percentile_cont(0.50) WITHIN GROUP (ORDER BY col)
    FROM VALUES (0), (6), (6), (7), (9), (10) AS tab(col);
 6.5