PERCENTILE_APPROX

​

Syntax

​

Aggregate

percentile_approx(value, percentile [, accuracy])

• Requires native execution and a source database context other than PRESTO.

​

Window

percentile_approx(value, percentile [, accuracy]) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame])

• Requires native execution and a source database context other than PRESTO.
• Use a frame that includes all rows in the group or starts at the first row (UNBOUNDED PRECEDING). Sliding frames that drop earlier rows are not supported. RANGE frames with a numeric or time offset are not supported.

​

Arguments

​

Aggregate and window

​

Returns

• Type: same type as value, or ARRAY<same type as value>
• NULL behavior: NULL values are ignored. An empty input returns NULL, whether requesting one percentile or an array of percentiles.
• Empty input: Returns NULL on empty input.
• Shape: Scalar fraction returns scalar; array fractions return an array of matching length.
• Ordering: Array result order follows requested fractions.

​

Properties

• Approximation bound: Relative rank error is 1/accuracy; default accuracy is 10000. This is a rank-error statement, not an absolute numeric-value error bound.

​

Usage notes

• Fraction and accuracy must be constant expressions. They cannot vary by row.
• Higher accuracy uses more memory. Approximate results can vary with the order in which values are processed.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• DISTINCT removes duplicates in grouped aggregation. Do not use it to remove duplicates in a window call.

​

Examples

​

Median estimate

• Requires native execution and a source database context other than PRESTO.

WITH t("value") AS (VALUES (1),(2),(3)) SELECT percentile_approx("value",0.5) AS result FROM t;

​

Estimate the upper quartile

WITH sample(v) AS (VALUES (10), (20), (30), (40), (50))
SELECT percentile_approx(v, 0.75) AS upper_quartile FROM sample;

​

Limitations

• Sliding windows that drop earlier rows are not supported. Start the frame at the first row in the group.
• PERCENTILE_APPROX is available with native execution outside the PRESTO source context. It is not available with Java execution.