APPROX_PERCENTILE - Firebolt Documentation

Returns an approximate percentile of the input values. Pass one fraction to get one percentile, or an array of fractions to get several percentiles from a single pass over the data. For an exact result, use PERCENTILE_CONT. NULL values are ignored.

Syntax

APPROX_PERCENTILE(<expression>, <fraction>)
APPROX_PERCENTILE(<expression>, <fractions>)

Parameters

Parameter	Description	Supported input types
`<expression>`	Values to compute the percentile over	`INTEGER`, `BIGINT`, `REAL`, `DOUBLE PRECISION`, `NUMERIC`, `DATE`, `TIMESTAMP`, `TIMESTAMPTZ`
`<fraction>`	The percentile to compute, from 0 to 1	A constant `DOUBLE PRECISION`
`<fractions>`	The percentiles to compute, each from 0 to 1	A constant array of `DOUBLE PRECISION`

Return type

With <fraction>, the function returns a single value. With <fractions>, it returns an array with one element per fraction, in the order of <fractions>. The value type is the type of <expression> for NUMERIC, DATE, TIMESTAMP and TIMESTAMPTZ inputs, and DOUBLE PRECISION otherwise.

Examples

SELECT APPROX_PERCENTILE(x, 0.5) AS median
FROM generate_series(1, 10) r(x);

median double null
5.5

Rows: 1Execution time: 2.74ms

Compute several percentiles at once:

SELECT APPROX_PERCENTILE(x, [0, 0.25, 0.5, 0.75, 1]) AS quartiles
FROM generate_series(1, 100) r(x);

quartiles array(double) null
[1, 25.75, 50.5, 75.25, 100]

Rows: 1Execution time: 2.10ms