percentile_approx
aggregate function
Applies to: Databricks SQL Databricks Runtime
Returns the approximate percentile of the expr
within the group. This function is a synonym for approx_percentile aggregate function.
Syntax
percentile_approx ( [ALL | DISTINCT ] expr, percentile [, accuracy] ) [FILTER ( WHERE cond ) ]
This function can also be invoked as a window function using the OVER
clause.
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 to10000
.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.
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