quantile (aggregate function)

Returns an exact quantile of finite floating-point values.

Syntax

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, NaN, and infinities. Returns NULL if no finite values remain.
Selects a value from the input ordered from least to greatest, without interpolation. For N values, the position is max(1, ceil(N * quantile)), counting from 1.

Examples

Compare typical and tail latency

Calculate 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
    quantile(latency_ms, quantile => 0.50) AS p50_ms,
    quantile(latency_ms, quantile => 0.90) AS p90_ms,
    quantile(latency_ms, quantile => 0.99) AS p99_ms

+--------+--------+--------+
| p50_ms | p90_ms | p99_ms |
+--------+--------+--------+
| 125.0  | 480.0  | 1600.0 |
+--------+--------+--------+

Half the requests took at most 125 ms, while the slowest took 1600 ms. Each percentile selects an observed latency.

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
    quantile(latency_ms, quantile => 0.50) AS p50_ms,
    quantile(latency_ms, quantile => 0.95) AS p95_ms
ORDER BY channel

+---------+--------+--------+
| channel | p50_ms | p95_ms |
+---------+--------+--------+
| mobile  | 190.0  | 1200.0 |
| web     | 110.0  | 320.0  |
+---------+--------+--------+