ClickHouse/clickhouse-docs-embeddings huggingface dataset:
quantileInterpolatedWeighted

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/quantileInterpolatedWeighted.md)

## quantileInterpolatedWeighted[​](#quantileInterpolatedWeighted "Direct link to quantileInterpolatedWeighted")

Introduced in: v23.1.0

Computes [quantile](https://en.wikipedia.org/wiki/Quantile) of a numeric data sequence using linear interpolation, taking into account the weight of each element.

To get the interpolated value, all the passed values are combined into an array, which are then sorted by their corresponding weights.
Quantile interpolation is then performed using the [weighted percentile method](https://en.wikipedia.org/wiki/Percentile#The_weighted_percentile_method) by building a cumulative distribution based on weights and then a linear interpolation is performed using the weights and the values to compute the quantiles.

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`](/docs/sql-reference/aggregate-functions/reference/quantiles#quantiles) function.

**Syntax**

```
quantileInterpolatedWeighted(level)(expr, weight)
```

**Aliases**: `medianInterpolatedWeighted`

**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*`](/docs/sql-reference/data-types/float)

**Arguments**

- `expr` — Expression over the column values resulting in numeric data types, Date or DateTime. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime)
- `weight` — Column with weights of sequence members. Weight is a number of value occurrences. [`UInt*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Quantile of the specified level. [`Float64`](/docs/sql-reference/data-types/float) or [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**Computing interpolated weighted quantile**

```
CREATE TABLE t (
    n Int32,
    val Int32
) ENGINE = Memory;

INSERT INTO t VALUES (0, 3), (1, 2), (2, 1), (5, 4);

SELECT quantileInterpolatedWeighted(n, val) FROM t;
```

```
┌─quantileInterpolatedWeighted(n, val)─┐
│                                    1 │
└──────────────────────────────────────┘
```

**See Also**

- [median](/docs/sql-reference/aggregate-functions/reference/median)
- [quantiles](/docs/sql-reference/aggregate-functions/reference/quantiles)

- [quantileInterpolatedWeighted](#quantileInterpolatedWeighted)

Was this page helpful?
quantilePrometheusHistogram

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/quantilePrometheusHistogram.md)

## quantilePrometheusHistogram[​](#quantilePrometheusHistogram "Direct link to quantilePrometheusHistogram")

Introduced in: v25.10.0

Computes [quantile](https://en.wikipedia.org/wiki/Quantile) of a histogram using linear interpolation, taking into account the cumulative value and upper bounds of each histogram bucket.

To get the interpolated value, all the passed values are combined into an array, which are then sorted by their corresponding bucket upper bound values.
Quantile interpolation is then performed similarly to the PromQL [histogram\_quantile()](https://prometheus.io/docs/prometheus/latest/querying/functions/#histogram_quantile) function on a classic histogram, performing a linear interpolation using the lower and upper bound of the bucket in which the quantile position is found.

**See Also**

- [median](/docs/sql-reference/aggregate-functions/reference/median)
- [quantiles](/docs/sql-reference/aggregate-functions/reference/quantiles)

**Syntax**

```
quantilePrometheusHistogram(level)(bucket_upper_bound, cumulative_bucket_value)
```

**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](https://en.wikipedia.org/wiki/Median). [`Float64`](/docs/sql-reference/data-types/float)

**Arguments**

- `bucket_upper_bound` — Upper bounds of the histogram buckets. The highest bucket must have an upper bound of `+Inf`. [`Float64`](/docs/sql-reference/data-types/float)
- `cumulative_bucket_value` — Cumulative values of the histogram buckets. Values must be monotonically increasing as the bucket upper bound increases. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float64`](/docs/sql-reference/data-types/float)

**Returned value**

Returns the quantile of the specified level. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Usage example**

```
SELECT quantilePrometheusHistogram(bucket_upper_bound, cumulative_bucket_value)
FROM VALUES('bucket_upper_bound Float64, cumulative_bucket_value UInt64', (0, 6), (0.5, 11), (1, 14), (inf, 19));
```

```
┌─quantilePrometheusHistogram(bucket_upper_bound, cumulative_bucket_value)─┐
│                                                                     0.35 │
└──────────────────────────────────────────────────────────────────────────┘
```

**See Also**

- [median](/docs/sql-reference/aggregate-functions/reference/median)
- [quantiles](/docs/sql-reference/aggregate-functions/reference/quantiles)

- [quantilePrometheusHistogram](#quantilePrometheusHistogram)

Was this page helpful?
quantileBFloat16

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/quantileBFloat16.md)

## quantileBFloat16[​](#quantileBFloat16 "Direct link to quantileBFloat16")

Introduced in: v21.7.0

Computes an approximate [quantile](https://en.wikipedia.org/wiki/Quantile) of a sample consisting of [bfloat16](https://en.wikipedia.org/wiki/Bfloat16_floating-point_format) numbers.

`bfloat16` is a floating-point data type with 1 sign bit, 8 exponent bits and 7 fraction bits.
The function converts input values to 32-bit floats and takes the most significant 16 bits. Then it calculates `bfloat16` quantile value and converts the result to a 64-bit float by appending zero bits.
The function is a fast quantile estimator with a relative error no more than 0.390625%.

**Syntax**

```
quantileBFloat16[(level)](expr)
```

**Aliases**: `medianBFloat16`

**Parameters**

- `level` — Optional. Level of quantile. Possible values are in the range from 0 to 1. Default value: 0.5. [`Float*`](/docs/sql-reference/data-types/float)

**Arguments**

- `expr` — Column with numeric data. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)

**Returned value**

Approximate quantile of the specified level. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Computing quantile with bfloat16**

```
CREATE TABLE example_table (a UInt32, b Float32) ENGINE = Memory;
INSERT INTO example_table VALUES (1, 1.001), (2, 1.002), (3, 1.003), (4, 1.004);

SELECT quantileBFloat16(0.75)(a), quantileBFloat16(0.75)(b) FROM example_table;
```

```
┌─quantileBFloat16(0.75)(a)─┬─quantileBFloat16(0.75)(b)─┐
│                         3 │                         1 │
└───────────────────────────┴───────────────────────────┘
```

**See Also**

- [median](/docs/sql-reference/aggregate-functions/reference/median)
- [quantiles](/docs/sql-reference/aggregate-functions/reference/quantiles)

- [quantileBFloat16](#quantileBFloat16)

Was this page helpful?
quantileDD

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/quantileDD.md)

## quantileDD[​](#quantileDD "Direct link to quantileDD")

Introduced in: v24.1.0

Computes an approximate [quantile](https://en.wikipedia.org/wiki/Quantile) of a sample with relative-error guarantees.
It works by building a [DD](https://www.vldb.org/pvldb/vol12/p2195-masson.pdf).

**Syntax**

```
quantileDD(relative_accuracy, [level])(expr)
```

**Aliases**: `medianDD`

**Parameters**

- `relative_accuracy` — Relative accuracy of the quantile. Possible values are in the range from 0 to 1. The size of the sketch depends on the range of the data and the relative accuracy. The larger the range and the smaller the relative accuracy, the larger the sketch. The rough memory size of the sketch is `log(max_value/min_value)/relative_accuracy`. The recommended value is 0.001 or higher. [`Float*`](/docs/sql-reference/data-types/float)
- `level` — Optional. Level of quantile. Possible values are in the range from 0 to 1. Default value: 0.5. [`Float*`](/docs/sql-reference/data-types/float)

**Arguments**

- `expr` — Column with numeric data. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)

**Returned value**

Approximate quantile of the specified level. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Computing quantile with DD sketch**

```
CREATE TABLE example_table (a UInt32, b Float32) ENGINE = Memory;
INSERT INTO example_table VALUES (1, 1.001), (2, 1.002), (3, 1.003), (4, 1.004);

SELECT quantileDD(0.01, 0.75)(a), quantileDD(0.01, 0.75)(b) FROM example_table;
```

```
┌─quantileDD(0.01, 0.75)(a)─┬─quantileDD(0.01, 0.75)(b)─┐
│        2.974233423476717  │                      1.01 │
└───────────────────────────┴───────────────────────────┘
```

**See Also**

- [median](/docs/sql-reference/aggregate-functions/reference/median)
- [quantiles](/docs/sql-reference/aggregate-functions/reference/quantiles)

- [quantileDD](#quantileDD)

Was this page helpful?
quantileDeterministic

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/quantileDeterministic.md)

## quantileDeterministic[​](#quantileDeterministic "Direct link to quantileDeterministic")

Introduced in: v1.1.0

Computes an approximate [quantile](https://en.wikipedia.org/wiki/Quantile) of a numeric data sequence.

This function applies [reservoir sampling](https://en.wikipedia.org/wiki/Reservoir_sampling) with a reservoir size up to 8192 and deterministic algorithm of sampling.
The result is deterministic.
To get an exact quantile, use the [`quantileExact`](/docs/sql-reference/aggregate-functions/reference/quantileexact#quantileExact) function.

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`](/docs/sql-reference/aggregate-functions/reference/quantiles#quantiles) function.

**Syntax**

```
quantileDeterministic(level)(expr, determinator)
```

**Aliases**: `medianDeterministic`

**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*`](/docs/sql-reference/data-types/float)

**Arguments**

- `expr` — Expression over the column values resulting in numeric data types, Date or DateTime. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime)
- `determinator` — Number whose hash is used instead of a random number generator in the reservoir sampling algorithm to make the result of sampling deterministic. As a determinator you can use any deterministic positive number, for example, a user id or an event id. If the same determinator value occurs too often, the function works incorrectly. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an approximate quantile of the specified level. [`Float64`](/docs/sql-reference/data-types/float) or [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**Computing deterministic quantile**

```
CREATE TABLE t (val UInt32) ENGINE = Memory;
INSERT INTO t VALUES (1), (1), (2), (3);

SELECT quantileDeterministic(val, 1) FROM t;
```

```
┌─quantileDeterministic(val, 1)─┐
│                           1.5 │
└───────────────────────────────┘
```

**See Also**

- [median](/docs/sql-reference/aggregate-functions/reference/median)
- [quantiles](/docs/sql-reference/aggregate-functions/reference/quantiles)

- [quantileDeterministic](#quantileDeterministic)

Was this page helpful?
quantileExact Functions

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/quantileExact.md)

## quantileExact[​](#quantileExact "Direct link to quantileExact")

Introduced in: v1.1.0

Exactly computes the [quantile](https://en.wikipedia.org/wiki/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`](/docs/sql-reference/aggregate-functions/reference/quantiles#quantiles) function.

**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*`](/docs/sql-reference/data-types/float)

**Arguments**

- `expr` — Expression over the column values resulting in numeric data types, Date or DateTime. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/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*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**Computing exact quantile**

```
SELECT quantileExact(number) FROM numbers(10);
```

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

**See Also**

- [median](/docs/sql-reference/aggregate-functions/reference/median)
- [quantiles](/docs/sql-reference/aggregate-functions/reference/quantiles)

- [quantileExact](#quantileExact)

Was this page helpful?
quantileExactWeighted

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/quantileExactWeighted.md)

## quantileExactWeighted[​](#quantileExactWeighted "Direct link to quantileExactWeighted")

Introduced in: v1.1.0

Exactly computes the [quantile](https://en.wikipedia.org/wiki/Quantile) of a numeric data sequence, taking into account the weight of each element.

To get the 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`](/docs/sql-reference/aggregate-functions/reference/quantileexact#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](/docs/sql-reference/aggregate-functions/reference/quantiles#quantiles) function.

**Syntax**

```
quantileExactWeighted(level)(expr, weight)
```

**Aliases**: `medianExactWeighted`

**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*`](/docs/sql-reference/data-types/float)

**Arguments**

- `expr` — Expression over the column values resulting in numeric data types, Date or DateTime. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime)
- `weight` — Column with weights of sequence members. Weight is a number of value occurrences. [`UInt*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Quantile of the specified level. [`Float64`](/docs/sql-reference/data-types/float) or [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**Computing exact weighted quantile**

```
CREATE TABLE t (
    n Int32,
    val Int32
) ENGINE = Memory;

-- Insert the sample data
INSERT INTO t VALUES
(0, 3),
(1, 2),
(2, 1),
(5, 4);

SELECT quantileExactWeighted(n, val) FROM t;
```

```
┌─quantileExactWeighted(n, val)─┐
│                             1 │
└───────────────────────────────┘
```

**See Also**

- [median](/docs/sql-reference/aggregate-functions/reference/median)
- [quantiles](/docs/sql-reference/aggregate-functions/reference/quantiles)

- [quantileExactWeighted](#quantileExactWeighted)

Was this page helpful?
quantiles Functions

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/quantiles.md)

## quantiles[​](#quantiles "Direct link to quantiles")

Introduced in: v1.1.0

Computes multiple approximate [quantiles](https://en.wikipedia.org/wiki/Quantile) of a numeric data sequence at different levels simultaneously.

This function applies [reservoir sampling](https://en.wikipedia.org/wiki/Reservoir_sampling) with a reservoir size up to 8192 and a random number generator for sampling.
The result is non-deterministic.

Using `quantiles` is more efficient than calling multiple individual `quantile` functions when you need multiple quantile values, as all quantiles are calculated in a single pass through the data.

**Syntax**

```
quantiles(level1, level2, ...)(expr)
```

**Parameters**

- `level` — Levels of quantiles. One or more constant floating-point numbers from 0 to 1. We recommend using `level` values in the range of `[0.01, 0.99]`. [`Float*`](/docs/sql-reference/data-types/float)

**Arguments**

- `expr` — Expression over the column values resulting in numeric data types, Date or DateTime. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime)

**Returned value**

Array of approximate quantiles of the specified levels in the same order as the levels were specified. [`Array(Float64)`](/docs/sql-reference/data-types/array) or [`Array(Date)`](/docs/sql-reference/data-types/array) or [`Array(DateTime)`](/docs/sql-reference/data-types/array)

**Examples**

**Computing multiple quantiles efficiently**

```
CREATE TABLE t (val UInt32) ENGINE = Memory;
INSERT INTO t VALUES (1), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

SELECT quantiles(0.25, 0.5, 0.75, 0.9)(val) FROM t;
```

```
┌─quantiles(0.25, 0.5, 0.75, 0.9)(val)─┐
│ [3, 5.5, 8, 9.5]                     │
└──────────────────────────────────────┘
```

- [quantiles](#quantiles)

Was this page helpful?
quantilesExactExclusive

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/quantilesExactExclusive.md)

## quantilesExactExclusive[​](#quantilesExactExclusive "Direct link to quantilesExactExclusive")

Introduced in: v20.1.0

Exactly computes multiple [quantiles](https://en.wikipedia.org/wiki/Quantile) of a numeric data sequence at different levels simultaneously using the exclusive method.

This function is equivalent to [`quantileExactExclusive`](/docs/sql-reference/aggregate-functions/reference/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](https://en.wikipedia.org/wiki/Quantile#Estimating_quantiles_from_a_sample).
This is equivalent to [PERCENTILE.EXC](https://support.microsoft.com/en-us/office/percentile-exc-function-bbaa7204-e9e1-4010-85bf-c31dc5dce4ba) 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*`](/docs/sql-reference/data-types/float)

**Arguments**

- `expr` — Expression over the column values resulting in numeric data types, Date or DateTime. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime)

**Returned value**

Array of quantiles of the specified levels in the same order as the levels were specified. [`Array(Float64)`](/docs/sql-reference/data-types/array)

**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)

Was this page helpful?