APPROX_PERCENTILE()¶

APPROX_PERCENTILE(expr, percentile) estimates a percentile of a numeric expression without sorting the complete input.

Description¶

APPROX_PERCENTILE is an aggregate function. It ignores NULL values in expr and returns an approximate value for the requested percentile in each group.

The first argument supports BIT, signed and unsigned integer types, FLOAT32, FLOAT64, DECIMAL64, and DECIMAL128 inputs. The return type is determined by the input type:

Input type	Return type
`BIT`, integer, unsigned integer, `FLOAT32`, or `FLOAT64`	`FLOAT64`
`DECIMAL64` or `DECIMAL128`	`DECIMAL128(38, scale)`; if the input precision is less than 38, `scale` is increased by 1

For a decimal input whose precision is already 38, the input scale is preserved. Decimal interpolation is rounded to the declared result scale.

Syntax¶

APPROX_PERCENTILE(expr, percentile)

Arguments¶

Argument	Description
`expr`	A supported numeric expression. `NULL` values are ignored.
`percentile`	A non-`NULL`, finite constant in the inclusive range `[0, 1]`; `0.5` is the median.

The DISTINCT modifier is not supported. An empty input or an input containing only NULL values returns NULL.

Examples¶

DROP DATABASE IF EXISTS approx_percentile_demo;
CREATE DATABASE approx_percentile_demo;
USE approx_percentile_demo;

CREATE TABLE t1 (a INT, b INT, d DECIMAL(10, 2));
INSERT INTO t1 VALUES (1, 1, 1.10), (2, 3, 3.30), (3, 5, 5.50), (4, 7, 7.70), (5, 9, 9.90);
SELECT APPROX_PERCENTILE(b, 0.5) AS p50 FROM t1;
SELECT APPROX_PERCENTILE(b, 0.95) AS p95 FROM t1;
SELECT APPROX_PERCENTILE(d, 0.5) AS decimal_p50 FROM t1;
SELECT a, APPROX_PERCENTILE(b, 0.5) AS p50 FROM t1 GROUP BY a ORDER BY a;
SELECT APPROX_PERCENTILE(CAST(NULL AS INT), 0.5) AS all_null_result;

DROP DATABASE approx_percentile_demo;

The following forms are rejected:

-- Expected-Success: false
SELECT APPROX_PERCENTILE(b, NULL) FROM t1;
-- Expected-Success: false
SELECT APPROX_PERCENTILE(b, 1.1) FROM t1;