percentile_cont
aggregate function
Applies to: Databricks SQL Databricks Runtime 10.4 LTS and above
Returns the value that corresponds to the percentile
of the provided sortKey
s using a continuous distribution model.
Syntax
percentile_cont ( percentile )
WITHIN GROUP (ORDER BY sortKey [ASC | DESC] )
This function can also be invoked as a window function using the OVER
clause.
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
orDESC
: Optionally specify whether the percentile is computed using ascending or descending order. The default isASC
.
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 sortKey
s.
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 interpolated median.
> SELECT percentile_cont(0.50) WITHIN GROUP (ORDER BY col)
FROM VALUES (0), (6), (6), (7), (9), (10) AS tab(col);
6.5