Array functions

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

- `arr` — An array to remove duplicates from. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns an array without duplicate values [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arrayCompact([1, 1, nan, nan, 2, 3, 3, 3]);
```

```
[1,nan,2,3]
```

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

Introduced in: v1.1.0

Combines arrays passed as arguments.

**Syntax**

```
arrayConcat(arr1 [, arr2, ... , arrN])
```

**Arguments**

- `arr1 [, arr2, ... , arrN]` — N number of arrays to concatenate. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns a single combined array from the provided array arguments. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arrayConcat([1, 2], [3, 4], [5, 6]) AS res
```

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

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

Introduced in: v1.1.0

Returns the number of elements for which `func(arr1[i], ..., arrN[i])` returns true.
If `func` is not specified, it returns the number of non-zero elements in the array.

`arrayCount` is a [higher-order function](/docs/sql-reference/functions/overview#higher-order-functions).

**Syntax**

```
arrayCount([func, ] arr1, ...)
```

**Arguments**

- `func` — Optional. Function to apply to each element of the array(s). [`Lambda function`](/docs/sql-reference/functions/overview#arrow-operator-and-lambda)
- `arr1, ..., arrN` — N arrays. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns the number of elements for which `func` returns true. Otherwise, returns the number of non-zero elements in the array. [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT arrayCount(x -> (x % 2), groupArray(number)) FROM numbers(10)
```

```
5
```

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

Introduced in: v1.1.0

Returns an array of the partial (running) sums of the elements in the source array. If a lambda function is specified, the sum is computed from applying the lambda to the array elements at each position.

**Syntax**

```
arrayCumSum([func,] arr1[, arr2, ... , arrN])
```

**Arguments**

- `func` — Optional. A lambda function to apply to the array elements at each position. [`Lambda function`](/docs/sql-reference/functions/overview#arrow-operator-and-lambda)
- `arr1` — The source array of numeric values. [`Array(T)`](/docs/sql-reference/data-types/array)
- `[arr2, ..., arrN]` — Optional. Additional arrays of the same size, passed as arguments to the lambda function if specified. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns an array of the partial sums of the elements in the source array. The result type matches the input array's numeric type. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Basic usage**

```
SELECT arrayCumSum([1, 1, 1, 1]) AS res
```

```
[1, 2, 3, 4]
```

**With lambda**

```
SELECT arrayCumSum(x -> x * 2, [1, 2, 3]) AS res
```

```
[2, 6, 12]
```

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

Introduced in: v18.12.0

Returns an array of the partial (running) sums of the elements in the source array, replacing any negative running sum with zero. If a lambda function is specified, the sum is computed from applying the lambda to the array elements at each position.

**Syntax**

```
arrayCumSumNonNegative([func,] arr1[, arr2, ... , arrN])
```

**Arguments**

- `func` — Optional. A lambda function to apply to the array elements at each position. [`Lambda function`](/docs/sql-reference/functions/overview#arrow-operator-and-lambda)
- `arr1` — The source array of numeric values. [`Array(T)`](/docs/sql-reference/data-types/array)
- `[arr2, ..., arrN]` — Optional. Additional arrays of the same size, passed as arguments to the lambda function if specified. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns an array of the partial sums of the elements in the source array, with any negative running sum replaced by zero. The result type matches the input array's numeric type. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Basic usage**

```
SELECT arrayCumSumNonNegative([1, 1, -4, 1]) AS res
```

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

**With lambda**

```
SELECT arrayCumSumNonNegative(x -> x * 2, [1, -2, 3]) AS res
```

```
[2, 0, 6]
```

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

Introduced in: v1.1.0

Calculates an array of differences between adjacent array elements.
The first element of the result array will be 0, the second `arr[1] - arr[0]`, the third `arr[2] - arr[1]`, etc.
The type of elements in the result array are determined by the type inference rules for subtraction (e.g. `UInt8` - `UInt8` = `Int16`).

**Syntax**

```
arrayDifference(arr)
```

**Arguments**

- `arr` — Array for which to calculate differences between adjacent elements. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns an array of differences between adjacent array elements [`UInt*`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

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

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

**Example of overflow due to result type Int64**

```
SELECT arrayDifference([0, 10000000000000000000]);
```

```
┌─arrayDifference([0, 10000000000000000000])─┐
│ [0,-8446744073709551616]                   │
└────────────────────────────────────────────┘
```

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

Introduced in: v1.1.0

Returns an array containing only the distinct elements of an array.

**Syntax**

```
arrayDistinct(arr)
```

**Arguments**

- `arr` — Array for which to extract distinct elements. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns an array containing the distinct elements [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arrayDistinct([1, 2, 2, 3, 1]);
```

```
[1,2,3]
```

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

Introduced in: v23.5.0

Returns the dot product of two arrays.

Note

The sizes of the two vectors must be equal. Arrays and Tuples may also contain mixed element types.

**Syntax**

```
arrayDotProduct(v1, v2)
```

**Arguments**

- `v1` — First vector. [`Array((U)Int* | Float* | Decimal)`](/docs/sql-reference/data-types/array) or [`Tuple((U)Int* | Float* | Decimal)`](/docs/sql-reference/data-types/tuple)
- `v2` — Second vector. [`Array((U)Int* | Float* | Decimal)`](/docs/sql-reference/data-types/array) or [`Tuple((U)Int* | Float* | Decimal)`](/docs/sql-reference/data-types/tuple)

**Returned value**

The dot product of the two vectors.

Note

The return type is determined by the type of the arguments. If Arrays or Tuples contain mixed element types then the result type is the supertype.

[`(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)

**Examples**

**Array example**

```
SELECT arrayDotProduct([1, 2, 3], [4, 5, 6]) AS res, toTypeName(res);
```

```
32    UInt16
```

**Tuple example**

```
SELECT dotProduct((1::UInt16, 2::UInt8, 3::Float32),(4::Int16, 5::Float32, 6::UInt8)) AS res, toTypeName(res);
```

```
32    Float64
```