percentile_approx aggregate function

  • Article

Applies to: check marked yes Databricks SQL check marked yes 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 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.

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