Skip to main content

percentile_cont aggregate function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.4 LTS and above

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] )

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 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​

SQL
-- 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
Last updated on