Array functions

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

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

Introduced in: v1.1.0

Creates an array from the function arguments.

The arguments should be constants and have types that share a common supertype.
At least one argument must be passed, because otherwise it isn't clear which type of array to create.
This means that you can't use this function to create an empty array. To do so, use the `emptyArray*` function.

Use the `[ ]` operator for the same functionality.

**Syntax**

```
array(x1 [, x2, ..., xN])
```

**Arguments**

- `x1` — Constant value of any type T. If only this argument is provided, the array will be of type T. - `[, x2, ..., xN]` — Additional N constant values sharing a common supertype with `x1`

**Returned value**

Returns an array, where 'T' is the smallest common type out of the passed arguments. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Valid usage**

```
SELECT array(toInt32(1), toUInt16(2), toInt8(3)) AS a, toTypeName(a)
```

```
┌─a───────┬─toTypeName(a)─┐
│ [1,2,3] │ Array(Int32)  │
└─────────┴───────────────┘
```

**Invalid usage**

```
SELECT array(toInt32(5), toDateTime('1998-06-16'), toInt8(5)) AS a, toTypeName(a)
```

```
Received exception from server (version 25.4.3):
Code: 386. DB::Exception: Received from localhost:9000. DB::Exception:
There is no supertype for types Int32, DateTime, Int8 ...
```

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

Introduced in: v20.4.0

Calculates the area under the precision-recall (PR) curve.
A precision-recall curve is created by plotting precision on the y-axis and recall on the x-axis across all thresholds.
The resulting value ranges from 0 to 1, with a higher value indicating better model performance.
The PR AUC is particularly useful for imbalanced datasets, providing a clearer comparison of performance compared to ROC AUC on those cases.
For more details, please see [here](https://developers.google.com/machine-learning/glossary#pr-auc-area-under-the-pr-curve), [here](https://developers.google.com/machine-learning/crash-course/classification/roc-and-auc#expandable-1) and [here](https://en.wikipedia.org/wiki/Receiver_operating_characteristic#Area_under_the_curve).

**Syntax**

```
arrayAUCPR(scores, labels[, partial_offsets])
```

**Aliases**: `arrayPRAUC`

**Arguments**

- `cores` — Scores prediction model gives. [`Array((U)Int*)`](/docs/sql-reference/data-types/array) or [`Array(Float*)`](/docs/sql-reference/data-types/array)
- `labels` — Labels of samples, usually 1 for positive sample and 0 for negative sample. [`Array((U)Int*)`](/docs/sql-reference/data-types/array) or [`Array(Enum)`](/docs/sql-reference/data-types/array)
- `partial_offsets` —
- Optional. An [`Array(T)`](/docs/sql-reference/data-types/array) of three non-negative integers for calculating a partial area under the PR curve (equivalent to a vertical band of the PR space) instead of the whole AUC. This option is useful for distributed computation of the PR AUC. The array must contain the following elements [`higher_partitions_tp`, `higher_partitions_fp`, `total_positives`].
  - `higher_partitions_tp`: The number of positive labels in the higher-scored partitions.
  - `higher_partitions_fp`: The number of negative labels in the higher-scored partitions.
  - `total_positives`: The total number of positive samples in the entire dataset.

Note

When `arr_partial_offsets` is used, the `arr_scores` and `arr_labels` should be only a partition of the entire dataset, containing an interval of scores.
The dataset should be divided into contiguous partitions, where each partition contains the subset of the data whose scores fall within a specific range.
For example:

- One partition could contain all scores in the range [0, 0.5).
- Another partition could contain scores in the range [0.5, 1.0].

**Returned value**

Returns area under the precision-recall (PR) curve. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Usage example**

```
SELECT arrayAUCPR([0.1, 0.4, 0.35, 0.8], [0, 0, 1, 1]);
```

```
┌─arrayAUCPR([0.1, 0.4, 0.35, 0.8], [0, 0, 1, 1])─┐
│                              0.8333333333333333 │
└─────────────────────────────────────────────────┘
```

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

Introduced in: v1.1.0

Returns `1` if lambda `func(x [, y1, y2, ... yN])` returns true for all elements. Otherwise, it returns `0`.

**Syntax**

```
arrayAll(func(x[, y1, ..., yN]), source_arr[, cond1_arr, ... , condN_arr])
```

**Arguments**

- `func(x[, y1, ..., yN])` — A lambda function which operates on elements of the source array (`x`) and condition arrays (`y`). [`Lambda function`](/docs/sql-reference/functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Array(T)`](/docs/sql-reference/data-types/array)
- `cond1_arr, ...` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns `1` if the lambda function returns true for all elements, `0` otherwise [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**All elements match**

```
SELECT arrayAll(x, y -> x=y, [1, 2, 3], [1, 2, 3])
```

```
1
```

**Not all elements match**

```
SELECT arrayAll(x, y -> x=y, [1, 2, 3], [1, 1, 1])
```

```
0
```

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

Introduced in: v26.4.0

Calculates the autocorrelation of an array.
If `max_lag` is provided, calculates correlation only for lags in range `[0, max_lag)`.
If `max_lag` is not provided, calculates for all possible lags.

**Syntax**

```
arrayAutocorrelation(arr, [max_lag])
```

**Arguments**

- `arr` — Array of numbers. [`Array(T)`](/docs/sql-reference/data-types/array)
- `max_lag` — Optional. Maximum number of lags to compute. Must be a non-negative integer. [`Integer`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an array of Float64. Returns NaN if variance is 0. [`Array(Float64)`](/docs/sql-reference/data-types/array)

**Examples**

**Linear**

```
SELECT arrayAutocorrelation([1, 2, 3, 4, 5]);
```

```
[1, 0.4, -0.1, -0.4, -0.4]
```

**Symmetric**

```
SELECT arrayAutocorrelation([10, 20, 10]);
```

```
[1, -0.6666666666666669, 0.16666666666666674]
```

**Constant**

```
SELECT arrayAutocorrelation([5, 5, 5]);
```

```
[nan, nan, nan]
```

**Limited**

```
SELECT arrayAutocorrelation([1, 2, 3, 4, 5], 2);
```

```
[1, 0.4]
```

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

Introduced in: v21.1.0

Returns the average of elements in the source array.

If a lambda function `func` is specified, returns the average of elements of the lambda results.

**Syntax**

```
arrayAvg([func(x[, y1, ..., yN])], source_arr[, cond1_arr, ... , condN_arr])
```

**Arguments**

- `func(x[, y1, ..., yN])` — Optional. A lambda function which operates on elements of the source array (`x`) and condition arrays (`y`). [`Lambda function`](/docs/sql-reference/functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Array(T)`](/docs/sql-reference/data-types/array)
- `[, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns the average of elements in the source array, or the average of elements of the lambda results if provided. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Basic example**

```
SELECT arrayAvg([1, 2, 3, 4]);
```

```
2.5
```

**Usage with lambda function**

```
SELECT arrayAvg(x, y -> x*y, [2, 3], [2, 3]) AS res;
```

```
6.5
```

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

Introduced in: v20.1.0

Removes consecutive duplicate elements from an array, including `null` values. The order of values in the resulting array is determined by the order in the source array.

**Syntax**

```
arrayCompact(arr)
```

**Arguments**