ClickHouse/clickhouse-docs-embeddings huggingface dataset:
maxMap

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

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

Introduced in: v20.5.0

Calculates the maximum from `value` array according to the keys specified in the `key` array.

Note

- Passing a tuple of keys and value arrays is identical to passing an array of keys and an array of values.
- The number of elements in `key` and `value` must be the same for each row that is totaled.

**Syntax**

```
maxMappedArrays(key, value)
maxMappedArrays(Tuple(key, value))
```

**Arguments**

- `key` — Array of keys. [`Array(T)`](/docs/sql-reference/data-types/array)
- `value` — Array of values. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns a tuple of two arrays: keys in sorted order, and values calculated for the corresponding keys. [`Tuple(Array(T), Array(T))`](/docs/sql-reference/data-types/tuple)

**Examples**

**Usage example**

```
SELECT maxMappedArrays(a, b)
FROM VALUES('a Array(Char), b Array(Int64)', (['x', 'y'], [2, 2]), (['y', 'z'], [3, 1]));
```

```
┌─maxMappedArrays(a, b)────────────────┐
│ [['x', 'y', 'z'], [2, 3, 1]].        │
└──────────────────────────────────────┘
```

- [maxMappedArrays](#maxMappedArrays)

Was this page helpful?
meanZTest

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

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

Introduced in: v22.2.0

Applies mean z-test to samples from two populations.

Values of both samples are in the `sample_data` column.
If `sample_index` equals to 0 then the value in that row belongs to the sample from the first population.
Otherwise it belongs to the sample from the second population.
The null hypothesis is that means of populations are equal.
A normal distribution is assumed.
Populations may have unequal variance and the variances are known.

**Syntax**

```
meanZTest(population_variance_x, population_variance_y, confidence_level)(sample_data, sample_index)
```

**Parameters**

- `population_variance_x` — Variance for population x. [`Float*`](/docs/sql-reference/data-types/float)
- `population_variance_y` — Variance for population y. [`Float*`](/docs/sql-reference/data-types/float)
- `confidence_level` — Confidence level in order to calculate confidence intervals. [`Float*`](/docs/sql-reference/data-types/float)

**Arguments**

- `sample_data` — Sample data. [`(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)
- `sample_index` — Sample index. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a tuple with four elements: calculated z-statistic, calculated p-value, calculated confidence-interval-low, calculated confidence-interval-high. [`Tuple(Float64, Float64, Float64, Float64)`](/docs/sql-reference/data-types/tuple)

**Examples**

**Mean Z-test example**

```
CREATE TABLE mean_ztest (sample_data Float64, sample_index UInt8) ENGINE = Memory;
INSERT INTO mean_ztest VALUES (20.3, 0), (21.9, 0), (22.1, 0), (18.9, 1), (19, 1), (20.3, 1);

SELECT meanZTest(0.7, 0.45, 0.95)(sample_data, sample_index) FROM mean_ztest;
```

```
┌─meanZTest(0.7, 0.45, 0.95)(sample_data, sample_index)───────────────────────────────┐
│ (3.2841296025548123, 0.0010229786769086013, 0.8198428246768334, 3.2468238419898365) │
└─────────────────────────────────────────────────────────────────────────────────────┘
```

- [meanZTest](#meanZTest)

Was this page helpful?
title_wip

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

The `median*` functions are the aliases for the corresponding `quantile*` functions. They calculate median of a numeric data sample.

Functions:

- `median` — Alias for [quantile](/docs/sql-reference/aggregate-functions/reference/quantile).
- `medianDeterministic` — Alias for [quantileDeterministic](/docs/sql-reference/aggregate-functions/reference/quantiledeterministic).
- `medianExact` — Alias for [quantileExact](/docs/sql-reference/aggregate-functions/reference/quantileexact).
- `medianExactWeighted` — Alias for [quantileExactWeighted](/docs/sql-reference/aggregate-functions/reference/quantileexactweighted).
- `medianTiming` — Alias for [quantileTiming](/docs/sql-reference/aggregate-functions/reference/quantiletiming).
- `medianTimingWeighted` — Alias for [quantileTimingWeighted](/docs/sql-reference/aggregate-functions/reference/quantiletimingweighted).
- `medianTDigest` — Alias for [quantileTDigest](/docs/sql-reference/aggregate-functions/reference/quantiletdigest).
- `medianTDigestWeighted` — Alias for [quantileTDigestWeighted](/docs/sql-reference/aggregate-functions/reference/quantiletdigestweighted).
- `medianBFloat16` — Alias for [quantileBFloat16](/docs/sql-reference/aggregate-functions/reference/quantilebfloat16).
- `medianDD` — Alias for [quantileDD](/docs/sql-reference/aggregate-functions/reference/quantileddsketch).

**Example**

Input table:

```
┌─val─┐
│   1 │
│   1 │
│   2 │
│   3 │
└─────┘
```

Query:

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

Result:

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

Was this page helpful?
min

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

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

Introduced in: v1.1.0

Aggregate function that calculates the minimum across a group of values.

**Syntax**

```
min(column)
```

**Arguments**

- `column` — Column name or expression. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns the minimum value across the group with type equal to that of the input. [`Any`](/docs/sql-reference/data-types)

**Examples**

**Simple min example**

```
CREATE TABLE employees (name String, salary UInt32) ENGINE = Memory;
INSERT INTO employees VALUES ('Alice', 3000), ('Bob', 4000), ('Charlie', 3500);

SELECT min(salary) FROM employees;
```

```
┌─min(salary)─┐
│        3000 │
└─────────────┘
```

**Min with GROUP BY**

```
CREATE TABLE sales (department String, revenue UInt32) ENGINE = Memory;
INSERT INTO sales VALUES ('Engineering', 100000), ('Engineering', 120000), ('Marketing', 80000), ('Marketing', 90000);

SELECT department, min(revenue) FROM sales GROUP BY department ORDER BY department;
```

```
┌─department──┬─min(revenue)─┐
│ Engineering │       100000 │
│ Marketing   │        80000 │
└─────────────┴──────────────┘
```

- [min](#min)

Was this page helpful?
minMap

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

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

Introduced in: v20.5.0

Calculates the minimum from `value` array according to the keys specified in the `key` array.

Note

- Passing a tuple of keys and value arrays is identical to passing an array of keys and an array of values.
- The number of elements in `key` and `value` must be the same for each row that is totaled.

**Syntax**

```
minMappedArrays(key, value)
minMappedArrays(Tuple(key, value))
```

**Arguments**

- `key` — Array of keys. [`Array(T)`](/docs/sql-reference/data-types/array)
- `value` — Array of values. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns a tuple of two arrays: keys in sorted order, and values calculated for the corresponding keys. [`Tuple(Array(T), Array(T))`](/docs/sql-reference/data-types/tuple)

**Examples**

**Usage example**

```
SELECT minMappedArrays(a, b)
FROM VALUES('a Array(Int32), b Array(Int64)', ([1, 2], [2, 2]), ([2, 3], [1, 1]));
```

```
┌─minMappedArrays(a, b)───────────┐
│ ([1, 2, 3], [2, 1, 1])          │
└─────────────────────────────────┘
```

- [minMappedArrays](#minMappedArrays)

Was this page helpful?
quantile

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

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

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 a random number generator for sampling.
The result is non-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.

Note that for an empty numeric sequence, `quantile` will return NaN, but its `quantile*` variants will return either NaN or a default value for the sequence type, depending on the variant.

**Syntax**

```
quantile(level)(expr)
```

**Aliases**: `median`

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

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

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

SELECT quantile(val) FROM t;
```

```
┌─quantile(val)─┐
│           1.5 │
└───────────────┘
```

**See Also**

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

- [quantile](#quantile)

Was this page helpful?
quantileExactExclusive

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

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

Introduced in: v20.1.0

Similar to [`quantileExact`](/docs/sql-reference/aggregate-functions/reference/quantileexact), this computes the exact [quantile](https://en.wikipedia.org/wiki/Quantile) of a numeric data sequence.

This function is equivalent to [`quantileExact`](/docs/sql-reference/aggregate-functions/reference/quantileexact) but 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).

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](/docs/sql-reference/aggregate-functions/reference/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*`](/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**

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

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

Was this page helpful?
quantileExactHigh

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

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

Introduced in: v20.8.0

Similar to [`quantileExact`](/docs/sql-reference/aggregate-functions/reference/quantileexact), this computes the exact [quantile](https://en.wikipedia.org/wiki/Quantile) of a numeric data sequence.

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.

The return value depends on the quantile level and the number of elements in the selection, i.e. if the level is 0.5, then the function returns the higher median value for an even number of elements and the middle median value for an odd number of elements.
Median is calculated similarly to the [`median_high`](https://docs.python.org/3/library/statistics.html#statistics.median_high) implementation which is used in python.

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

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

**Syntax**

```
quantileExactHigh(level)(expr)
```

**Aliases**: `medianExactHigh`

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

Returns the 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 high quantile**

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

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

**Computing specific quantile level**

```
SELECT quantileExactHigh(0.1)(number) FROM numbers(10);
```

```
┌─quantileExactHigh(0.1)(number)─┐
│                              1 │
└────────────────────────────────┘
```

- [quantileExactHigh](#quantileExactHigh)

Was this page helpful?
quantileExactInclusive

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

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

Introduced in: v20.1.0

Similar to [`quantileExact`](/docs/sql-reference/aggregate-functions/reference/quantileexact), this computes the exact [quantile](https://en.wikipedia.org/wiki/Quantile) of a numeric data sequence.

This function is equivalent to [`quantileExact`](/docs/sql-reference/aggregate-functions/reference/quantileexact) but uses the inclusive method for calculating quantiles, as described in the [R-7 method](https://en.wikipedia.org/wiki/Quantile#Estimating_quantiles_from_a_sample).

When using this function, the quantile is calculated such that the interpolation formula for a given quantile p takes the form: `x[floor((n-1)*p)] + ((n-1)*p - floor((n-1)*p)) * (x[floor((n-1)*p)+1] - x[floor((n-1)*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](/docs/sql-reference/aggregate-functions/reference/quantiles) function.

**Syntax**

```
quantileExactInclusive(level)(expr)
```

**Parameters**

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

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

**Examples**

**Computing exact inclusive quantile**

```
SELECT quantileExactInclusive(0.25)(number) FROM numbers(5);
```

```
┌─quantileExactInclusive(0.25)(number)─┐
│                                    1 │
└──────────────────────────────────────┘
```

**Computing multiple quantile levels**

```
SELECT quantileExactInclusive(0.1)(number), quantileExactInclusive(0.9)(number) FROM numbers(10);
```

```
┌─quantileExactInclusive(0.1)(number)─┬─quantileExactInclusive(0.9)(number)─┐
│                                 0.9 │                                 8.1 │
└─────────────────────────────────────┴─────────────────────────────────────┘
```

- [quantileExactInclusive](#quantileExactInclusive)

Was this page helpful?
quantileExactLow

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

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

Introduced in: v20.8.0

Similar to [`quantileExact`](/docs/sql-reference/aggregate-functions/reference/quantileexact), this computes the exact [quantile](https://en.wikipedia.org/wiki/Quantile) of a numeric data sequence.

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.

The return value depends on the quantile level and the number of elements in the selection, i.e. if the level is 0.5, then the function returns the lower median value for an even number of elements and the middle median value for an odd number of elements.
Median is calculated similarly to the [median\_low](https://docs.python.org/3/library/statistics.html#statistics.median_low) implementation which is used in python.

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

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

**Syntax**

```
quantileExactLow(level)(expr)
```

**Aliases**: `medianExactLow`

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

Returns the 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 low quantile**

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

```
┌─quantileExactLow(number)─┐
│                        4 │
└──────────────────────────┘
```

**Computing specific quantile level**

```
SELECT quantileExactLow(0.1)(number) FROM numbers(10);
```

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

- [quantileExactLow](#quantileExactLow)

Was this page helpful?