Relational Data - Functions - Array Functions

operator-and-lambdaparams-expr-function). - arr1: Array to operate on. Array.

Returned value

The last element in the passed array.
Otherwise, returns NULL

Implementation details

Note that the arrayLastOrNull is a higher-order function. You must pass a lambda function to it as the first argument, and it can’t be omitted.

Example

Query:

SELECT arrayLastOrNull(x -> x >= 2, [1, 2, 3]);

Result:

Query:

SELECT arrayLastOrNull(x -> x >= 2, emptyArrayUInt8());

Result:

\N

arrayFirstIndex(func, arr1, …)

Returns the index of the first element in the arr1 array for which func(arr1[i], ..., arrN[i]) returns something other than 0.

Note that the arrayFirstIndex is a higher-order function. You must pass a lambda function to it as the first argument, and it can’t be omitted.

arrayLastIndex(func, arr1, …)

Returns the index of the last element in the arr1 array for which func(arr1[i], ..., arrN[i]) returns something other than 0.

Note that the arrayLastIndex is a higher-order function. You must pass a lambda function to it as the first argument, and it can’t be omitted.

arrayMin

Returns the minimum of elements in the source array.

If the func function is specified, returns the mininum of elements converted by this function.

Note that the arrayMin is a higher-order function. You can pass a lambda function to it as the first argument.

Syntax

arrayMin([func,] arr)

Arguments

func — Function. Expression.
arr — Array. Array.

Returned value

The minimum of function values (or the array minimum).

If func is specified, then the return type matches the return value type of func, otherwise it matches the type of the array elements.

Examples

Query:

SELECT arrayMin([1, 2, 4]) AS res;

Result:

┌─res─┐
│   1 │
└─────┘

Query:

SELECT arrayMin(x -> (-x), [1, 2, 4]) AS res;

Result:

┌─res─┐
│  -4 │
└─────┘

arrayMax

Returns the maximum of elements in the source array.

If the func function is specified, returns the maximum of elements converted by this function.

Note that the arrayMax is a higher-order function. You can pass a lambda function to it as the first argument.

Syntax

arrayMax([func,] arr)

Arguments

func — Function. Expression.
arr — Array. Array.

Returned value

The maximum of function values (or the array maximum).

if func is specified then the return type matches the return value type of func, otherwise it matches the type of the array elements.

Examples

Query:

SELECT arrayMax([1, 2, 4]) AS res;

Result:

┌─res─┐
│   4 │
└─────┘

Query:

SELECT arrayMax(x -> (-x), [1, 2, 4]) AS res;

Result:

┌─res─┐
│  -1 │
└─────┘

arraySum

Returns the sum of elements in the source array.

If the func function is specified, returns the sum of elements converted by this function.

Note that the arraySum is a higher-order function. You can pass a lambda function to it as the first argument.

Syntax

arraySum([func,] arr)

Arguments

func — Function. Expression.
arr — Array. Array.

Returned value

The sum of the function values (or the array sum).

Return type:

For decimal numbers in the source array (or for converted values, if func is specified) — Decimal128.
For floating point numbers — Float64.
For numeric unsigned — UInt64.
For numeric signed — Int64.

Examples

Query:

SELECT arraySum([2, 3]) AS res;

Result:

┌─res─┐
│   5 │
└─────┘

Query:

SELECT arraySum(x -> x*x, [2, 3]) AS res;

Result:

┌─res─┐
│  13 │
└─────┘

arrayAvg

Returns the average of elements in the source array.

If the func function is specified, returns the average of elements converted by this function.

Note that the arrayAvg is a higher-order function. You can pass a lambda function to it as the first argument.

Syntax

arrayAvg([func,] arr)

Arguments

func — Function. Expression.
arr — Array. Array.

Returned value

The average of function values (or the array average). Float64.

Examples

Query:

SELECT arrayAvg([1, 2, 4]) AS res;

Result:

┌────────────────res─┐
│ 2.3333333333333335 │
└────────────────────┘

Query:

SELECT arrayAvg(x -> (x * x), [2, 4]) AS res;

Result:

┌─res─┐
│  10 │
└─────┘

arrayCumSum([func,] arr1, …)

Returns an array of the partial (running) sums of the elements in the source array arr1. If func is specified, then the sum is computed from applying func to arr1, arr2, …, arrN, i.e. func(arr1[i], ..., arrN[i]).

Syntax

arrayCumSum(arr)

Arguments

arr — Array of numeric values.

Returned value

Returns an array of the partial sums of the elements in the source array. UInt*, Int*, Float*.

Example:

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

┌─res──────────┐
│ [1, 2, 3, 4] │
└──────────────┘

Note that the arrayCumSum is a higher-order function. You can pass a lambda function to it as the first argument.

arrayCumSumNonNegative([func,] arr1, …)

Same as arrayCumSum, returns an array of the partial (running) sums of the elements in the source array. If func is specified, then the sum is computed from applying func to arr1, arr2, …, arrN, i.e. func(arr1[i], ..., arrN[i]). Unlike arrayCumSum, if the current running sum is smaller than 0, it is replaced by 0.

Syntax

arrayCumSumNonNegative(arr)

Arguments

arr — Array of numeric values.

Returned value

Returns an array of non-negative partial sums of elements in the source array. UInt*, Int*, Float*.

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

┌─res───────┐
│ [1,2,0,1] │
└───────────┘

Note that the arraySumNonNegative is a higher-order function. You can pass a lambda function to it as the first argument.

arrayProduct

Multiplies elements of an array.

Syntax

arrayProduct(arr)

Arguments

arr — Array of numeric values.

Returned value

A product of array’s elements. Float64.

Examples

Query:

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

Result:

┌─res───┐
│ 720   │
└───────┘

Query:

SELECT arrayProduct([toDecimal64(1,8), toDecimal64(2,8), toDecimal64(3,8)]) as res, toTypeName(res);

Return value type is always Float64. Result:

┌─res─┬─toTypeName(arrayProduct(array(toDecimal64(1, 8), toDecimal64(2, 8), toDecimal64(3, 8))))─┐
│ 6   │ Float64                                                                                  │
└─────┴──────────────────────────────────────────────────────────────────────────────────────────┘

arrayRotateLeft

Rotates an array to the left by the specified number of elements. If the number of elements is negative, the array is rotated to the right.

Syntax

arrayRotateLeft(arr, n)

Arguments

arr — Array.
n — Number of elements to rotate.

Returned value

An array rotated to the left by the specified number of elements. Array.

Examples

Query:

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

Result:

┌─res───────────┐
│ [3,4,5,6,1,2] │
└───────────────┘

Query:

SELECT arrayRotateLeft([1,2,3,4,5,6], -2) as res;

Result:

┌─res───────────┐
│ [5,6,1,2,3,4] │
└───────────────┘

Query:

SELECT arrayRotateLeft(['a','b','c','d','e'], 3) as res;

Result:

┌─res───────────────────┐
│ ['d','e','a','b','c'] │
└───────────────────────┘

arrayRotateRight

Rotates an array to the right by the specified number of elements. If the number of elements is negative, the array is rotated to the left.

Syntax

arrayRotateRight(arr, n)

Arguments

arr — Array.
n — Number of elements to rotate.

Returned value

An array rotated to the right by the specified number of elements. Array.

Examples

Query:

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

Result:

┌─res───────────┐
│ [5,6,1,2,3,4] │
└───────────────┘

Query:

SELECT arrayRotateRight([1,2,3,4,5,6], -2) as res;

Result:

┌─res───────────┐
│ [3,4,5,6,1,2] │
└───────────────┘

Query:

SELECT arrayRotateRight(['a','b','c','d','e'], 3) as res;

Result:

┌─res───────────────────┐
│ ['c','d','e','a','b'] │
└───────────────────────┘

arrayShiftLeft

Shifts an array to the left by the specified number of elements. New elements are filled with the provided argument or the default value of the array element type. If the number of elements is negative, the array is shifted to the right.

Syntax

arrayShiftLeft(arr, n[, default])

Arguments

arr — Array.
n — Number of elements to shift.
default — Optional. Default value for new elements.

Returned value

An array shifted to the left by the specified number of elements. Array.

Examples

Query:

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

Result:

┌─res───────────┐
│ [3,4,5,6,0,0] │
└───────────────┘

Query:

SELECT arrayShiftLeft([1,2,3,4,5,6], -2) as res;

Result:

┌─res───────────┐
│ [0,0,1,2,3,4] │
└───────────────┘

Query:

SELECT arrayShiftLeft([1,2,3,4,5,6], 2, 42) as res;

Result:

┌─res─────────────┐
│ [3,4,5,6,42,42] │
└─────────────────┘

Query:

SELECT arrayShiftLeft(['a','b','c','d','e','f'], 3, 'foo') as res;

Result:

┌─res─────────────────────────────┐
│ ['d','e','f','foo','foo','foo'] │
└─────────────────────────────────┘

Query:

SELECT arrayShiftLeft([1,2,3,4,5,6] :: Array(UInt16), 2, 4242) as res;

Result:

┌─res─────────────────┐
│ [3,4,5,6,4242,4242] │
└─────────────────────┘

arrayShiftRight

Shifts an array to the right by the specified number of elements. New elements are filled with the provided argument or the default value of the array element type. If the number of elements is negative, the array is shifted to the left.

Syntax

arrayShiftRight(arr, n[, default])

Arguments

arr — Array.
n — Number of elements to shift.
default — Optional. Default value for new elements.

Returned value

An array shifted to the right by the specified number of elements. Array.

Examples

Query:

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

Result:

┌─res───────────┐
│ [0,0,1,2,3,4] │
└───────────────┘

Query:

SELECT arrayShiftRight([1,2,3,4,5,6], -2) as res;

Result:

┌─res───────────┐
│ [3,4,5,6,0,0] │
└───────────────┘

Query:

SELECT arrayShiftRight([1,2,3,4,5,6], 2, 42) as res;

Result:

┌─res─────────────┐
│ [42,42,1,2,3,4] │
└─────────────────┘

Query:

SELECT arrayShiftRight(['a','b','c','d','e','f'], 3, 'foo') as res;

Result:

┌─res─────────────────────────────┐
│ ['foo','foo','foo','a','b','c'] │
└─────────────────────────────────┘

Query:

SELECT arrayShiftRight([1,2,3,4,5,6] :: Array(UInt16), 2, 4242) as res;

Result:

┌─res─────────────────┐
│ [4242,4242,1,2,3,4] │
└─────────────────────┘

arrayRandomSample

Function arrayRandomSample 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 from which to sample elements. (Array(T))
samples — The number of elements to include in the random sample (UInt*)

Returned Value

An array containing a random sample of elements from the input array. Array.

Examples

Query:

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

Result:

┌─res────────────────┐
│ ['cherry','apple'] │
└────────────────────┘

Query:

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

Result:

┌─res───────────┐
│ [[3,4],[5,6]] │
└───────────────┘

Query:

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

Result:

┌─res─────┐
│ [3,1,2] │
└─────────┘

Distance functions

All supported functions are described in distance functions documentation.