quantilesExactExclusive

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 O(n) 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 PERCENTILE.EXC Excel function, (type R6).

Works more efficiently with sets of levels than quantileExactExclusive.

Syntax

quantilesExactExclusive(level1, level2, ...)(expr)

Arguments

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

Parameters

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

Returned value

Array of quantiles of the specified levels.

Type of array values:

Float64 for numeric data type input.
Date 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:

┌─quantilesExactExclusive(0.25, 0.5, 0.75, 0.9, 0.95, 0.99, 0.999)(x)─┐
│ [249.25,499.5,749.75,899.9,949.9499999999999,989.99,998.999]        │
└─────────────────────────────────────────────────────────────────────┘

quantilesExactExclusive

Introduced in: v20.1

Exactly computes multiple quantiles of a numeric data sequence at different levels simultaneously using the exclusive method.

This function is equivalent to quantileExactExclusive but allows computing multiple quantile levels in a single pass, which is more efficient than calling individual quantile functions.

This function uses the exclusive method for calculating quantiles, as described in the R-6 method. This is equivalent to PERCENTILE.EXC Excel function.

To get exact values, all the passed values are combined into an array, which is then partially sorted. The sorting algorithm's complexity is O(N·log(N)), where N = std::distance(first, last) comparisons.

Syntax

quantilesExactExclusive(level1, level2, ...)(expr)

Parameters

level — Levels of quantiles. Constant floating-point numbers from 0 to 1 (exclusive). We recommend using level values in the range of (0.01, 0.99). Float*

Arguments

expr — Expression over the column values resulting in numeric data types, Date or DateTime. (U)Int* or Float* or Decimal* or Date or DateTime

Returned value

Array of quantiles of the specified levels in the same order as the levels were specified. Array(Float64)

Examples

Computing multiple exact exclusive quantiles

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

┌─quantilesExactExclusive(0.25, 0.5, 0.75, 0.9, 0.95, 0.99, 0.999)(number)─┐
│ [249.25,499.5,749.75,899.9,949.95,989.99,998.999]                        │
└──────────────────────────────────────────────────────────────────────────┘

quantilesExactExclusive​

quantilesExactExclusive​

quantilesExactExclusive

quantilesExactExclusive