APPROX_PERCENTILE

​

Syntax

​

Aggregate

approx_percentile(value, percentile [, accuracy])

• Source dialect is not PRESTO; Spark variant.

​

Window

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

• Source dialect is not PRESTO; Spark variant.
• 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.

​

Presto (aggregate)

approx_percentile(value, percentile)

• Source dialect is PRESTO.

​

Presto weighted (aggregate)

approx_percentile(value, weight, percentile)

• Source dialect is PRESTO.

​

Arguments

• F is TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE or DECIMAL(p, s).

​

Aggregate and window

​

Presto

​

Presto weighted

​

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: The Spark-style form has relative rank error 1/accuracy; accuracy defaults to 10000. The Presto-style form has no fixed maximum error guarantee and does not accept an accuracy argument.

​

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.
• In the PRESTO form, weighted calls use (value, weight, percentile). The Spark-style form uses (value, percentile, accuracy).
• PRESTO ignores NULL values and NULL weights. Non-NULL values paired with weight <1 raise errors; weights on NULL values are ignored.
• In the PRESTO form, DISTINCT removes duplicate values from unweighted calls and duplicate (value, weight) pairs from weighted calls.
• 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

​

Basic aggregate example

WITH t("value") AS (VALUES (0),(1),(2),(10)) SELECT approx_percentile("value",array(0.5,0.4,0.1),100) AS result FROM t;

​

Basic Presto example

• PRESTO source dialect.

WITH t(v) AS (VALUES (CAST(1 AS DOUBLE)),(CAST(2 AS DOUBLE)),(CAST(3 AS DOUBLE))) SELECT approx_percentile(v,array(1.0,0.0,0.5)) AS result FROM t;

​

Default accuracy across all rows in each group

WITH sample(v) AS (VALUES (2), (2), (2))
SELECT approx_percentile(v, 0.5) OVER () AS median
FROM sample;

​

Limitations

• Sliding windows that drop earlier rows are not supported. Start the frame at the first row in the group.