quantileExact

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.

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

Alias: medianExact.

Parameters

• 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 .
• expr — Expression over the column values resulting in numeric data types, or DateTime.

Returned value

• Quantile of the specified level.

Type:

• for numeric data type input.
• Date if input values have the Date type.
• if input values have the DateTime type.

Example

Query:

SELECT quantileExact(number) FROM numbers(10)

Result:

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

quantileExactLow

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

Depending on the level, i.e if the level is 0.5 then the exact lower median value is returned if there are even number of elements and the middle value is returned if there are odd number of elements. Median is calculated similar to the median_low implementation which is used in python.

For all other levels, the element at the the index corresponding to the value of level * size_of_array is returned. For example:

```$sql
SELECT quantileExactLow(0.1)(number) FROM numbers(10)

┌─quantileExactLow(0.1)(number)─┐
│ 1 │
└───────────────────────────────┘

Alias: medianExactLow.

Parameters

• 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 .
• expr — Expression over the column values resulting in numeric data types, or DateTime.

Returned value

• Quantile of the specified level.

Type:

• for numeric data type input.
• Date if input values have the Date type.
• if input values have the DateTime type.

Example

Query:

SELECT quantileExactLow(number) FROM numbers(10)

Result:

┌─quantileExactLow(number)─┐
│                        4 │

quantileExactHigh

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

Depending on the level, i.e if the level is 0.5 then the exact higher median value is returned if there are even number of elements and the middle value is returned if there are odd number of elements. Median is calculated similar to the median_high implementation which is used in python. For all other levels, the element at the the index corresponding to the value of level * size_of_array is returned.

This implementation behaves exactly similar to the current quantileExact implementation.

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 function.

Syntax

Alias: medianExactHigh.

Parameters

• 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 , Date or .

Returned value

• Quantile of the specified level.

Type:

• Float64 for numeric data type input.
• if input values have the Date type.
• DateTime if input values have the DateTime type.

Example

Query:

SELECT quantileExactHigh(number) FROM numbers(10)

┌─quantileExactHigh(number)─┐
│                         5 │

See Also