quantileExact

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.

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

quantileExact(level)(expr)

Alias: medianExact.

Arguments

level — Level of quantile. Optional parameter. Constant floating-point number from 0 to 1. We recommend using a level value in the range of [0.01, 0.99]. Default value: 0.5. At level=0.5 the function calculates median.
expr — Expression over the column values resulting in numeric data types, Date or DateTime.

Returned value

Quantile of the specified level.

Type:

For numeric data types the output format will be the same as the input format. For example:


SELECT
    toTypeName(quantileExact(number)) AS `quantile`,
    toTypeName(quantileExact(number::Int32)) AS `quantile_int32`,
    toTypeName(quantileExact(number::Float32)) AS `quantile_float32`,
    toTypeName(quantileExact(number::Float64)) AS `quantile_float64`,
    toTypeName(quantileExact(number::Int64)) AS `quantile_int64`
FROM numbers(1)
   ┌─quantile─┬─quantile_int32─┬─quantile_float32─┬─quantile_float64─┬─quantile_int64─┐
1. │ UInt64   │ Int32          │ Float32          │ Float64          │ Int64          │
   └──────────┴────────────────┴──────────────────┴──────────────────┴────────────────┘

1 row in set. Elapsed: 0.002 sec.

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

Example

Query:

SELECT quantileExact(number) FROM numbers(10)

Result:

┌─quantileExact(number)─┐
│                     5 │
└───────────────────────┘

See Also

quantileExact

Introduced in: v1.1

Exactly computes the quantile of a numeric data sequence.

Syntax

quantileExact(level)(expr)

Aliases: medianExact

Parameters

level — Optional. Level of quantile. Constant floating-point number from 0 to 1. We recommend using a level value in the range of [0.01, 0.99]. Default value: 0.5. At level=0.5 the function calculates median. 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

Quantile of the specified level. For numeric data types the output format will be the same as the input format. (U)Int* or Float* or Decimal* or Date or DateTime

Examples

Computing exact quantile

SELECT quantileExact(number) FROM numbers(10);

┌─quantileExact(number)─┐
│                     5 │
└───────────────────────┘

quantileExact​

quantileExact