quantileExactExclusive

Exactly computes the quantile 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).

When using multiple quantileExactExclusive functions with different levels in a query, the internal states are not combined (that is, the query works less efficiently than it could). In this case, use the quantilesExactExclusive function.

Syntax

quantileExactExclusive(level)(expr)

Arguments

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

Parameters

level — Level of quantile. Optional. Possible values: (0, 1) — bounds not included. Default value: 0.5. At level=0.5 the function calculates median. Float.

Returned value

Quantile of the specified level.

Type:

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 quantileExactExclusive(0.6)(x) FROM (SELECT number AS x FROM num);

Result:

┌─quantileExactExclusive(0.6)(x)─┐
│                          599.6 │
└────────────────────────────────┘

quantileExactExclusive

Introduced in: v20.1

Similar to quantileExact, this computes the exact quantile of a numeric data sequence.

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

When using this function, the quantile is calculated such that the interpolation formula for a given quantile p takes the form: x[floor(n*p)] + (n*p - floor(n*p)) * (x[floor(n*p)+1] - x[floor(n*p)]), where x is the sorted array.

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

When using multiple quantile* functions with different levels in a query, the internal states are not combined (that is, the query works less efficiently than it could). In this case, use the quantiles function.

Syntax

quantileExactExclusive(level)(expr)

Parameters

level — Level of quantile. Constant floating-point number from 0 to 1 (exclusive). We recommend using a level value 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

Returns the quantile of the specified level. Float64

Examples

Computing exact exclusive quantile

SELECT quantileExactExclusive(0.25)(number) FROM numbers(5);

┌─quantileExactExclusive(0.25)(number)─┐
│                                  0.5 │
└──────────────────────────────────────┘

Computing multiple quantile levels

SELECT quantileExactExclusive(0.1)(number), quantileExactExclusive(0.9)(number) FROM numbers(10);

┌─quantileExactExclusive(0.1)(number)─┬─quantileExactExclusive(0.9)(number)─┐
│                                 0.4 │                                 8.6 │
└─────────────────────────────────────┴─────────────────────────────────────┘

quantileExactExclusive​

quantileExactExclusive