Array functions

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

Introduced in: v23.2.0

This function is the same as `arraySort` but with an additional `limit` argument allowing partial sorting.

Tip

To retain only the sorted elements use `arrayResize`.

**Syntax**

```
arrayPartialSort([f,] arr [, arr1, ... ,arrN], limit)
```

**Arguments**

- `f(arr[, arr1, ... ,arrN])` — The lambda function to apply to elements of array `x`. [`Lambda function`](/docs/sql-reference/functions/overview#arrow-operator-and-lambda)
- `arr` — Array to be sorted. [`Array(T)`](/docs/sql-reference/data-types/array)
- `arr1, ... ,arrN` — N additional arrays, in the case when `f` accepts multiple arguments. [`Array(T)`](/docs/sql-reference/data-types/array)
- `limit` — Index value up until which sorting will occur. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an array of the same size as the original array where elements in the range `[1..limit]` are sorted
in ascending order. The remaining elements `(limit..N]` are in an unspecified order.

**Examples**

**simple\_int**

```
SELECT arrayPartialSort(2, [5, 9, 1, 3])
```

```
[1, 3, 5, 9]
```

**simple\_string**

```
SELECT arrayPartialSort(2, ['expenses', 'lasso', 'embolism', 'gladly'])
```

```
['embolism', 'expenses', 'gladly', 'lasso']
```

**retain\_sorted**

```
SELECT arrayResize(arrayPartialSort(2, [5, 9, 1, 3]), 2)
```

```
[1, 3]
```

**lambda\_simple**

```
SELECT arrayPartialSort((x) -> -x, 2, [5, 9, 1, 3])
```

```
[9, 5, 1, 3]
```

**lambda\_complex**

```
SELECT arrayPartialSort((x, y) -> -y, 1, [0, 1, 2], [1, 2, 3]) as res
```

```
[2, 1, 0]
```

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

Introduced in: v1.1.0

Removes the last element from the array.

**Syntax**

```
arrayPopBack(arr)
```

**Arguments**

- `arr` — The array for which to remove the last element from. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns an array identical to `arr` but without the last element of `arr` [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arrayPopBack([1, 2, 3]) AS res;
```

```
[1, 2]
```

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

Introduced in: v1.1.0

Removes the first item from the array.

**Syntax**

```
arrayPopFront(arr)
```

**Arguments**

- `arr` — The array for which to remove the first element from. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns an array identical to `arr` but without the first element of `arr` [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arrayPopFront([1, 2, 3]) AS res;
```

```
[2, 3]
```

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

Introduced in: v21.1.0

Returns the product of elements in the source array.

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

**Syntax**

```
arrayProduct([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 product of elements in the source array, or the product of elements of the lambda results if provided. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Basic example**

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

```
24
```

**Usage with lambda function**

```
SELECT arrayProduct(x, y -> x+y, [2, 2], [2, 2]) AS res;
```

```
16
```

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

Introduced in: v1.1.0

Adds one item to the end of the array.

**Syntax**

```
arrayPushBack(arr, x)
```

**Arguments**

- `arr` — The array for which to add value `x` to the end of. [`Array(T)`](/docs/sql-reference/data-types/array)
- `x` —
- Single value to add to the end of the array. [`Array(T)`](/docs/sql-reference/data-types/array).

Note

- Only numbers can be added to an array with numbers, and only strings can be added to an array of strings.
- When adding numbers, ClickHouse automatically sets the type of `x` for the data type of the array.
- Can be `NULL`. The function adds a `NULL` element to an array, and the type of array elements converts to `Nullable`.

For more information about the types of data in ClickHouse, see [Data types](/docs/sql-reference/data-types).

**Returned value**

Returns an array identical to `arr` but with an additional value `x` at the end of the array [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arrayPushBack(['a'], 'b') AS res;
```

```
['a','b']
```

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

Introduced in: v1.1.0

Adds one element to the beginning of the array.

**Syntax**

```
arrayPushFront(arr, x)
```

**Arguments**

- `arr` — The array for which to add value `x` to the end of. [`Array(T)`](/docs/sql-reference/data-types/array). - `x` —
- Single value to add to the start of the array. [`Array(T)`](/docs/sql-reference/data-types/array).

Note

- Only numbers can be added to an array with numbers, and only strings can be added to an array of strings.
- When adding numbers, ClickHouse automatically sets the type of `x` for the data type of the array.
- Can be `NULL`. The function adds a `NULL` element to an array, and the type of array elements converts to `Nullable`.

For more information about the types of data in ClickHouse, see [Data types](/docs/sql-reference/data-types).

**Returned value**

Returns an array identical to `arr` but with an additional value `x` at the beginning of the array [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arrayPushFront(['b'], 'a') AS res;
```

```
['a','b']
```

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

Introduced in: v20.4.0

Calculates the area under the receiver operating characteristic (ROC) curve.
A ROC curve is created by plotting True Positive Rate (TPR) on the y-axis and False Positive Rate (FPR) on the x-axis across all thresholds.
The resulting value ranges from zero to one, with a higher value indicating better model performance.

The ROC AUC (also known as simply AUC) is a concept in machine learning.
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**

```
arrayROCAUC(scores, labels[, scale[, partial_offsets]])
```

**Aliases**: `arrayAUC`

**Arguments**