Skip to main content

quantileExactWeighted

Exactly computes the quantile of a numeric data sequence, taking into account the weight of each element.

To get exact value, all the passed values ​​are combined into an array, which is then partially sorted. Each value is counted with its weight, as if it is present weight times. A hash table is used in the algorithm. Because of this, if the passed values ​​are frequently repeated, the function consumes less RAM than quantileExact. You can use this function instead of quantileExact and specify the weight 1.

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

quantileExactWeighted(level)(expr, weight)

Alias: medianExactWeighted.

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.
  • weight β€” Column with weights of sequence members. Weight is a number of value occurrences.

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

Input table:

β”Œβ”€n─┬─val─┐
β”‚ 0 β”‚ 3 β”‚
β”‚ 1 β”‚ 2 β”‚
β”‚ 2 β”‚ 1 β”‚
β”‚ 5 β”‚ 4 β”‚
β””β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”˜

Query:

SELECT quantileExactWeighted(n, val) FROM t

Result:

β”Œβ”€quantileExactWeighted(n, val)─┐
β”‚ 1 β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

See Also