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:
3
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
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
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.