percentile_approx aggregate function (Databricks SQL)

Returns the approximate percentile of the expr within the group.

Syntax

percentile_approx ( [ALL | DISTINCT ] expr, percentile [, accuracy] ) [FILTER ( WHERE cond ) ]

Arguments

  • expr: A numeric expression.

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

  • accuracy: An INTEGER literal greater than 0. If accuracy is omitted it is set to 10000.

  • cond: An optional boolean expression filtering the rows used for aggregation.

Returns

The aggregate function returns the expression which is the smallest value in the ordered group (sorted from least to greatest) such that no more than percentile of expr values is less than the value or equal to that value. If percentile is an array percentile_approx, returns the approximate percentile array of expr at the specified percentile.

The accuracy parameter controls approximation accuracy at the cost of memory. Higher value of accuracy yields better accuracy, 1.0/accuracy is the relative error of the approximation.

If DISTINCT is specified the function operates only on a unique set of expr values.

This function is a synonym for approx_percentile aggregate function (Databricks SQL).

Examples

> SELECT percentile_approx(col, array(0.5, 0.4, 0.1), 100)
    FROM VALUES (0), (1), (2), (10) AS tab(col);
 [1,1,0]
> SELECT percentile_approx(col, 0.5, 100)
    FROM VALUES (0), (6), (7), (9), (10), (10), (10) AS tab(col);
 9
> SELECT percentile_approx(DISTINCT col, 0.5, 100)
    FROM VALUES (0), (6), (7), (9), (10), (10), (10) AS tab(col);
 7