Array functions

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

- `scores` — 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 [`Enum`](/docs/sql-reference/data-types/enum)
- `scale` — Optional. Decides whether to return the normalized area. If false, returns the area under the TP (true positives) x FP (false positives) curve instead. Default value: true. [`Bool`](/docs/sql-reference/data-types/boolean)
- `partial_offsets` —
- An array of four non-negative integers for calculating a partial area under the ROC curve (equivalent to a vertical band of the ROC space) instead of the whole AUC. This option is useful for distributed computation of the ROC AUC. The array must contain the following elements [`higher_partitions_tp`, `higher_partitions_fp`, `total_positives`, `total_negatives`]. [Array](/docs/sql-reference/data-types/array) of non-negative [Integers](/docs/sql-reference/data-types/int-uint). Optional.
  - `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.
  - `total_negatives`: The total number of negative 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 receiver operating characteristic (ROC) curve. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Usage example**

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

```
0.75
```

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

Introduced in: v23.10.0

Returns a subset with `samples`-many random elements of an input array. If `samples` exceeds the size of the input array, the sample size is limited to the size of the array, i.e. all array elements are returned but their order is not guaranteed. The function can handle both flat arrays and nested arrays.

**Syntax**

```
arrayRandomSample(arr, samples)
```

**Arguments**

- `arr` — The input array or multidimensional array from which to sample elements. [`Array(T)`](/docs/sql-reference/data-types/array)
- `samples` — The number of elements to include in the random sample. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

An array containing a random sample of elements from the input array [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arrayRandomSample(['apple', 'banana', 'cherry', 'date'], 2) as res;
```

```
['cherry','apple']
```

**Using a multidimensional array**

```
SELECT arrayRandomSample([[1, 2], [3, 4], [5, 6]], 2) as res;
```

```
[[3,4],[5,6]]
```

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

Introduced in: v1.1.0

Applies an aggregate function to array elements and returns its result.
The name of the aggregation function is passed as a string in single quotes `'max'`, `'sum'`.
When using parametric aggregate functions, the parameter is indicated after the function name in parentheses `'uniqUpTo(6)'`.

**Syntax**

```
arrayReduce(agg_f, arr1 [, arr2, ... , arrN)])
```

**Arguments**

- `agg_f` — The name of an aggregate function which should be a constant. [`String`](/docs/sql-reference/data-types/string)
- `arr1 [, arr2, ... , arrN)]` — N arrays corresponding to the arguments of `agg_f`. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns the result of the aggregate function

**Examples**

**Usage example**

```
SELECT arrayReduce('max', [1, 2, 3]);
```

```
┌─arrayReduce('max', [1, 2, 3])─┐
│                             3 │
└───────────────────────────────┘
```

**Example with aggregate function using multiple arguments**

```
--If an aggregate function takes multiple arguments, then this function must be applied to multiple arrays of the same size.

SELECT arrayReduce('maxIf', [3, 5], [1, 0]);
```

```
┌─arrayReduce('maxIf', [3, 5], [1, 0])─┐
│                                    3 │
└──────────────────────────────────────┘
```

**Example with a parametric aggregate function**

```
SELECT arrayReduce('uniqUpTo(3)', [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]);
```

```
┌─arrayReduce('uniqUpTo(3)', [1, 2, 3, 4, 5, 6, 7, 8, 9, 10])─┐
│                                                           4 │
└─────────────────────────────────────────────────────────────┘
```

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

Introduced in: v20.4.0

Applies an aggregate function to array elements in the given ranges and returns an array containing the result corresponding to each range.
The function will return the same result as multiple `arrayReduce(agg_func, arraySlice(arr1, index, length), ...)`.

**Syntax**

```
arrayReduceInRanges(agg_f, ranges, arr1 [, arr2, ... ,arrN)])
```

**Arguments**

- `agg_f` — The name of the aggregate function to use. [`String`](/docs/sql-reference/data-types/string)
- `ranges` — The range over which to aggregate. An array of tuples, `(i, r)` containing the index `i` from which to begin from and the range `r` over which to aggregate. [`Array(T)`](/docs/sql-reference/data-types/array) or [`Tuple(T)`](/docs/sql-reference/data-types/tuple)
- `arr1 [, arr2, ... ,arrN)]` — N arrays as arguments to the aggregate function. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns an array containing results of the aggregate function over the specified ranges [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arrayReduceInRanges(
    'sum',
    [(1, 5), (2, 3), (3, 4), (4, 4)],
    [1000000, 200000, 30000, 4000, 500, 60, 7]
) AS res
```

```
┌─res─────────────────────────┐
│ [1234500,234000,34560,4567] │
└─────────────────────────────┘
```

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

Introduced in: v25.11.0

Removes all elements equal to a given value from an array.
NULLs are treated as equal.

**Syntax**

```
arrayRemove(arr, elem)
```

**Aliases**: `array_remove`

**Arguments**

- `arr` — Array(T) - `elem` — T

**Returned value**

Returns a subset of the source array [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Example 1**

```
SELECT arrayRemove([1, 2, 2, 3], 2)
```

```
[1, 3]
```

**Example 2**

```
SELECT arrayRemove(['a', NULL, 'b', NULL], NULL)
```

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

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

Introduced in: v1.1.0

Changes the length of the array.

**Syntax**

```
arrayResize(arr, size[, extender])
```

**Arguments**

- `arr` — Array to resize. [`Array(T)`](/docs/sql-reference/data-types/array)
- `size` —
  -The new length of the array.
  If `size` is less than the original size of the array, the array is truncated from the right.
  If `size` is larger than the initial size of the array, the array is extended to the right with `extender` values or default values for the data type of the array items.
- `extender` — Value to use for extending the array. Can be `NULL`.