approx_quantile (aggregate function)

Estimates a quantile of floating-point values.

Syntax

approx_quantile(<expr>, quantile => <quantile>)

Arguments

Argument	Type	Description
`<expr>`	`float`	Values to summarize. Cast integer columns to `float` first.
`quantile`	`float`	Constant. Quantile from 0 to 1, inclusive. Must not be NULL.

Returns

Returns float. Ignores NULL values and returns NULL when no values remain.

Usage notes

Approximate results can vary. Use quantile for an exact, non-interpolated result.

Examples

Compare typical and tail latency

Estimate the p50, p90, and p99 latency of 20 checkout requests in one result. The input values are milliseconds.

VALUES
    (82.0), (88.0), (91.0), (95.0), (98.0),
    (102.0), (108.0), (112.0), (118.0), (125.0),
    (132.0), (140.0), (155.0), (180.0), (210.0),
    (260.0), (340.0), (480.0), (850.0), (1600.0)
SELECT $0 AS latency_ms
AGGREGATE
    approx_quantile(latency_ms, quantile => 0.50) AS p50_ms,
    approx_quantile(latency_ms, quantile => 0.90) AS p90_ms,
    approx_quantile(latency_ms, quantile => 0.99) AS p99_ms

+--------+--------+--------+
| p50_ms | p90_ms | p99_ms |
+--------+--------+--------+
| 132.0  | 850.0  | 1600.0 |
+--------+--------+--------+

This is one possible result. The estimated p99 is much higher than the median; the estimates can vary between runs.

Compare checkout channels

Compare p50 and p95 request latency for web and mobile checkout:

VALUES
    ('web', 90.0), ('web', 110.0), ('web', 150.0), ('web', 320.0),
    ('mobile', 130.0), ('mobile', 190.0), ('mobile', 420.0), ('mobile', 1200.0)
SELECT $0 AS channel, $1 AS latency_ms
GROUP BY channel
AGGREGATE
    approx_quantile(latency_ms, quantile => 0.50) AS p50_ms,
    approx_quantile(latency_ms, quantile => 0.95) AS p95_ms
ORDER BY channel

+---------+--------+--------+
| channel | p50_ms | p95_ms |
+---------+--------+--------+
| mobile  | 420.0  | 1200.0 |
| web     | 150.0  | 320.0  |
+---------+--------+--------+