quantiles Functions

All the quantile functions also have corresponding quantiles functions: quantiles, quantilesDeterministic, quantilesTiming, quantilesTimingWeighted, quantilesExact, quantilesExactWeighted, quantilesTDigest, quantilesBFloat16. These functions calculate all the quantiles of the listed levels in one pass, and return an array of the resulting values.

Exactly computes the quantiles of a numeric data sequence.

To get exact value, all the passed values are combined into an array, which is then partially sorted. Therefore, the function consumes memory, where n is a number of values that were passed. However, for a small number of values, the function is very effective.

This function is equivalent to Excel function, (type R6).

Works more efficiently with sets of levels than .

Syntax

Arguments

expr — Expression over the column values resulting in numeric data types, or DateTime.

level — Levels of quantiles. Possible values: (0, 1) — bounds not included. .

Returned value

Array of quantiles of the specified levels.

Type of array values:

if input values have the Date type.
DateTime if input values have the DateTime type.

Example

Query:

CREATE TABLE num AS numbers(1000);
SELECT quantilesExactExclusive(0.25, 0.5, 0.75, 0.9, 0.95, 0.99, 0.999)(x) FROM (SELECT number AS x FROM num);

Result:

Exactly computes the of a numeric data sequence.

Works more efficiently with sets of levels than quantileExactInclusive.