array #

Create an array from function parameters.
Parameters must be constants and have the minimal common type. At least one parameter must be provided. Otherwise, it is unclear which type of array to create. That is, you cannot use this function to create an empty array. To do this, use the "emptyArray*" function described above.
Syntax

array(x1, …)

Parameters

• x1,... – Must be constants and have the minimal common type.

Return value

• Returns a result of type "Array(T)", where "T" is the minimal common type among the provided parameters.

Example

SELECT array(1,2,3);

┌─array(1, 2, 3)─┐ 
│ [1, 2, 3]      │ 
└────────────────┘

arrayAll #

If the return values of all elements in arr from func are not 0, then returns 1. Otherwise, returns 0.
Note that arrayAll is a higher-order function. You can pass a lambda function as the first parameter.
Syntax

arrayAll([func,] arr1, …)

Parameters

• func – The higher-order function must return UInt8.
• arr1,.. Arrays as function input.

Return value

• If all elements returned by func for arr are not 0, then returns 1.

Example

SELECT arrayAll((x,y)->x==y,[1,2,3],[4,5,6]);

┌─arrayAll(lambda(tuple(x, y), equals(x, y)), [1, 2, 3], [4, 5, 6])─┐ 
│ 0                                                                 │ 
└───────────────────────────────────────────────────────────────────┘

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

┌─arrayAll(lambda(tuple(x, y), equals(x, y)), [1, 2, 3], [1, 2, 3])─┐ 
│ 1                                                                 │ 
└───────────────────────────────────────────────────────────────────┘

empty #

Checks whether the input array is empty.
Syntax

empty([x])

If an array does not contain any elements, it is an empty array.
Parameters

• [x] — Input array, type: array.

Return value

• Returns 1 for empty arrays, and 0 for non-empty arrays.

Type: UInt8.
Example
Query statement:

SELECT empty([]);

Result:

┌─empty(array())─┐ 
│              1 │ 
└────────────────┘

notEmpty #

Checks whether the input array is not empty.
Syntax

notEmpty([x])

If an array contains at least one element, it is a non-empty array.
Parameters

• [x] — Input array, type: array.

Return value

• Returns 0 for empty arrays, and 1 for non-empty arrays.

Type: UInt8.
Example
Query statement:

SELECT notEmpty([1,2]);

Result:

┌─notEmpty([1, 2])─┐ 
│                1 │ 
└──────────────────┘

length #

Returns the number of elements in the array. The result type is UInt64. This function also applies to strings.
Example

SELECT length(arr) FROM table

emptyArrayString #

Does not accept any parameters and returns an empty array of the appropriate type.

emptyArrayToSingle #

Accepts an empty array and returns an array containing only one element with the default value.

range #

Returns an array of integer numbers from start to end - 1 with step as the increment. Supported types include UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, and Int64.
Syntax

range([start, ] end [, step])

Parameters

• start — The first element of the array. Optional. If step is set, start must also be specified. The default value is 0.
• end — Counts up to end, but does not include end. Required.
• step — Specifies the increment between each element in the array. Optional. The default value is 1.

Return value

• An array of numbers from start to end - 1, using step as the increment.

Notes

• All parameters start, end, and step must be one of the following types: UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64. The element data type of the resulting array is the minimum superclass of all input types, and must also be one of the above types.

Example
Query statement:

SELECT range(5), range(1, 5), range(1, 5, 2), range(-1, 5, 2);

Result:

┌─range(5)────┬─range(1, 5)─┬─range(1, 5, 2)─┬─range(-1, 5, 2)─┐ 
│ [0,1,2,3,4] │ [1,2,3,4]   │ [1,3]          │ [-1,1,3]        │ 
└─────────────┴─────────────┴────────────────┴─────────────────┘

arrayConcat #

Merges all arrays passed as parameters.
Syntax

arrayConcat(arrays)

Parameters

• arrays – Any number of parameters of array type.

Example

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

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

arr #

Retrieves the element at index «n» from the array arr. n must be of any integer type. Array indices start from one. Negative indices are supported. In this case, the corresponding element is selected counting from the end. For example, arr [-1] is the last item in the array.
Syntax

arr[n]

If the index is out of bounds, returns the default value (0 for numbers, empty string for strings, and so on).

has #

Checks whether the 'arr' array contains the 'elem' element. If the element is not in the array, returns 0; if it is, returns 1.
Syntax

has(arr,elem)

Example

SELECT has([1, 2, NULL], NULL) 

┌─has([1, 2, NULL], NULL)─┐ 
│                       1 │ 
└─────────────────────────┘

hasAll #

Checks whether one array is a subset of another array.
Syntax

hasAll(set, subset)

Parameters

• set – An array of any type containing a set of elements.
• subset – An array of any type whose elements should be tested as a subset of set.

Return value

• 1, if set contains all elements in subset.
• 0, otherwise.

Special definitions

• An empty array is a subset of any array.
• 'Null' is treated as an element value in the array.
• The order of element values in both arrays is ignored.

Example

SELECT hasAll([], []) --returns 1. 
SELECT hasAll([1, Null], [Null]) --returns 1. 
SELECT hasAll([1.0, 2, 3, 4], [1, 3]) --returns 1. 
SELECT hasAll(['a', 'b'], ['a']) --returns 1. 
SELECT hasAll([1], ['a']) --returns 0. 
SELECT hasAll([[1, 2], [3, 4]], [[1, 2], [3, 5]]) --returns 0.

hasAny #

Checks whether two arrays have any intersection.
Syntax

hasAny(array1, array2)

Parameters

• array1 – An array of any type containing a set of elements.
• array2 – An array of any type containing a set of elements.

Return value

• 1, if array1 and array2 have any intersection.
• 0, otherwise.

Special definitions

• 'Null' is treated as an element value in the array.
• Ignore the order of element values in the two arrays.

Example

SELECT hasAny([1], []) --returns 0. 
SELECT hasAny([Null], [Null, 1]) --returns 1. 
SELECT hasAny([-128, 1., 512], [1]) --returns 1. 
SELECT hasAny([[1, 2], [3, 4]], ['a', 'c']) --returns 0. 
SELECT hasAll([[1, 2], [3, 4]], [[1, 2], [1, 2]]) --returns 1.

hasSubstr #

Checks whether all elements of array2 appear in array1 in the same order. The function returns 1 if and only if array1 = prefix + array2 + suffix.
Syntax

hasSubstr(array1, array2)

In other words, the function checks whether all elements of array2 are contained in array1, just like the hasAll function. Additionally, it checks whether the elements appear in the same order in "array1" and "array2".
Example

hasSubstr([1,2,3,4], [2,3]) --returns 1. 
hasSubstr([1,2,3,4], [3,2]) --returns 0. 

hasSubstr([1,2,3,4], [1,2,3]) --returns 1. 
hasSubstr([1,2,3,4], [1,2,4]) --returns 0.

Param

• array1 – any type array containing a set of elements.
• array2 – any type array containing a set of elements.

Return value

• If array1 contains array2, returns 1.
• Returns 0 in other cases.

Special attribute

• When array2 is an empty array, the function returns 1.
• Null is processed as a value. In other words, hasSubstr([1, 2, NULL, 3, 4], [2,3]) will return 0. But hasSubstr([1, 2, NULL, 3, 4], [2,NULL,3]) will return 1.
• The order of values in the two arrays affects the function result.

Example

SELECT hasSubstr([], []) --returns 1. 
SELECT hasSubstr([1, Null], [Null]) --returns 1. 
SELECT hasSubstr([1.0, 2, 3, 4], [1, 3]) --returns 0. 
SELECT hasSubstr(['a', 'b'], ['a']) --returns 1. 
SELECT hasSubstr(['a', 'b' , 'c'], ['a', 'b']) --returns 1. 
SELECT hasSubstr(['a', 'b' , 'c'], ['a', 'c']) --returns 0. 
SELECT hasSubstr([[1, 2], [3, 4], [5, 6]], [[1, 2], [3, 4]]) --returns 1.

indexOf #

Returns the index (starting from 1) of the first 'x' element in the array. If the 'x' element does not exist in the array, returns 0.
Syntax

indexOf(arr,x)

Example

SELECT indexOf([1, 3, NULL, NULL], NULL)

┌─indexOf([1, 3, NULL, NULL], NULL)─┐ 
│                                 3 │ 
└───────────────────────────────────┘

Elements set as «NULL» are processed as normal element values.

arrayCount #

Returns the Quantity of elements in the arr array for which func returns a Not Zero Value. If "func" is not specified, returns the Quantity of Not Zero elements in the array.
Advisory: arrayCount is a higher-order function. You can pass a lambda function as the first parameter.
Syntax

arrayCount([func,] arr1, …)

Param

• func– Higher-order function must return UInt8
• arr1,..Array as Input to the function

Return value

• The Quantity of elements in the arr array for which func returns a Not Zero Value

Example

SELECT arrayCount((x,y)->x==y,[1,2,3],[1,5,3]);

┌─arrayCount(lambda(tuple(x, y), equals(x, y)), [1, 2, 3], [1, 5, 3])─┐ 
│ 2                                                                   │ 
└─────────────────────────────────────────────────────────────────────┘

arrayCumSum #

Returns an array of Part sums (running sums) of elements in the source array. If func is specified, the function transforms the values of array elements before summing.
Advisory: arrayCumSum is a higher-order function. You can pass a lambda function as the first parameter.
Syntax

arrayCumSum([func,] arr1, …)

Param

• func– Higher-order function
• arr1,..Array as Input to the function

Return value

• An array of Part sums of elements in the source array

Example

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

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

SELECT arrayCumSum(x->x+1,[1, 1, 1, 1]) AS res

┌─res──────────┐ 
│ [2, 4, 6, 8] │ 
└──────────────┘

arrayCumSumNonNegative #

Same as arrayCumSum, returns an array of Part sums (running sums) of elements in the source array. The difference lies in arrayCumSum: when the return value contains values smaller than zero, those values are replaced with zero, and subsequent calculations are performed using zero as the parameter. For example:
Caution: arraySumNonNegative is a higher-order function. You can pass a lambda function as its first parameter.
Syntax

arrayCumSumNonNegative([func,] arr1, …)

Param

• func – higher-order function
• arr1,.. Array as function input

Return value

• An array of the partial sums of the elements in the source array

Example

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

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

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

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

countEqual #

Returns the number of elements in the array that are equal to x. Equivalent to arrayCount(elem -> elem = x, arr).
NULL values are processed as individual element values.
Example

SELECT countEqual([1, 2, NULL, NULL], NULL)

┌─countEqual([1, 2, NULL, NULL], NULL)─┐ 
│                                    2 │ 
└──────────────────────────────────────┘

arrayEnumerate #

Returns Array [1, 2, 3, ..., length(arr)]
This function is typically used with ARRAY JOIN. It allows you to calculate each array once after applying ARRAY JOIN.
Example

SELECT 
    count() AS Reaches, 
    countIf(num = 1) AS Hits 
FROM test.hits 
ARRAY JOIN 
    GoalsReached, 
    arrayEnumerate(GoalsReached) AS num 
WHERE CounterID = 160656 
LIMIT 10

┌─Reaches─┬──Hits─┐ 
│   95606 │ 31406 │ 
└─────────┴───────┘

In this example, Reaches is the number of conversions (the count after applying ARRAY JOIN), and Hits is the page views (the count before ARRAY JOIN). In this special case, you can obtain the same result more easily:

SELECT 
    sum(length(GoalsReached)) AS Reaches, 
    count() AS Hits 
FROM test.hits 
WHERE (CounterID = 160656) AND notEmpty(GoalsReached)

┌─Reaches─┬──Hits─┐ 
│   95606 │ 31406 │ 
└─────────┴───────┘

This function can also be used in higher-order functions. For example, you can use it to get the array indices of elements that match a condition.

arrayEnumerateUniq #

Returns an array of the same size as the source array, where each element represents the number of times the corresponding element at that index appears in the source array. For example: arrayEnumerateUniq([10, 20, 10, 30]) = [1, 1, 2, 1].
This function is useful when using ARRAY JOIN and aggregating array elements.
Example

SELECT 
    Goals.ID AS GoalID, 
    sum(Sign) AS Reaches, 
    sumIf(Sign, num = 1) AS Visits 
FROM test.visits 
ARRAY JOIN 
    Goals, 
    arrayEnumerateUniq(Goals.ID) AS num 
WHERE CounterID = 160656 
GROUP BY GoalID 
ORDER BY Reaches DESC 
LIMIT 10

┌──GoalID─┬─Reaches─┬─Visits─┐ 
│   53225 │    3214 │   1097 │ 
│ 2825062 │    3188 │   1097 │ 
│   56600 │    2803 │    488 │ 
│ 1989037 │    2401 │    365 │ 
│ 2830064 │    2396 │    910 │ 
│ 1113562 │    2372 │    373 │ 
│ 3270895 │    2262 │    812 │ 
│ 1084657 │    2262 │    345 │ 
│   56599 │    2260 │    799 │ 
│ 3271094 │    2256 │    812 │ 
└─────────┴─────────┴────────┘

In this example, each GoalID counts the number of conversions (each element in the nested Goals data structure is a goal that has been reached, which we call a conversion) and the number of sessions. If ARRAY JOIN is not used, the number of sessions is counted as the sum of Sign. But in this special case, the rows are multiplied by the nested Goals structure, so to count each session once after this, we apply a condition to the value of the arrayEnumerateUniq(Goals.ID) function.
The arrayEnumerateUniq function can use multiple arrays of the same size as parameters. In this case, uniqueness is considered for the tuple of elements at the same location in all arrays.

SELECT arrayEnumerateUniq([1, 1, 1, 2, 2, 2], [1, 1, 2, 1, 1, 2]) AS res

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

This is required when using ARRAY JOIN with nested data structures and further aggregating across multiple elements in this structure.

arrayPopBack #

Delete the last element from the array.

arrayPopBack(array)

Param

• array – array.

Example

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

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

arrayPopFront #

Delete the first element from the array.
Syntax

arrayPopFront(array)

Param

• array – array.

Example

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

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

arrayPushBack #

Add an element to the end of the array.
Syntax

arrayPushBack(array, single_value)

Param

• array – array.
• single_value – single value. Only numbers can be added to arrays containing numbers, and only strings can be added to string arrays. When adding numbers, ByteHouse will automatically set the array's data type to the single_value type.

Example

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

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

arrayPushFront #

Add an element to the beginning of the array.
Syntax

arrayPushFront(array, single_value)

Param

• array – array.
• single_value – single value. Only numbers can be added to arrays containing numbers, and only strings can be added to string arrays. When adding numbers, ByteHouse will automatically set the array's data type to the single_value type. This function adds a "NULL" element to the array, and the array element type is converted to Nullable`.

Example

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

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

arrayResize #

Change the length of the array.
Syntax

arrayResize(array, size[, extender])

Param:

• array — array.
• size — the required length of the array.
  • If size is smaller than the original size of the array, the array will be truncated from the right.
• If size is greater than the initial size of the array, use the extender value or the default value of the array element's data type to extend the array to the right.
• extender — value to extend the array. Can be 'NULL'.

Return value:
An array of size length.
Example

SELECT arrayResize([1], 3);

┌─arrayResize([1], 3)─┐ 
│ [1,0,0]             │ 
└─────────────────────┘

SELECT arrayResize([1], 3, NULL);

┌─arrayResize([1], 3, NULL)─┐ 
│ [1,NULL,NULL]             │ 
└───────────────────────────┘

arraySlice #

Return a subarray containing elements of the specified length starting from the specified location.
Syntax

arraySlice(array, offset[, length])

Param

• array – array.
• offset – array offset. A positive value indicates an offset from the left, and a negative value indicates an offset from the right. Array indices start from 1.
• length The length of the subarray. If a negative value is specified, the function returns [offset, array_length - length]. If this value is omitted, the function returns [offset, the end of the array].

Example

SELECT arraySlice([1, 2, NULL, 4, 5], 2, 3) AS res

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

Array elements set to "NULL" are processed as normal array element values.

arraySort #

Sort the elements of the arr array in ascending order. If the func function is specified, the sorting order is determined by the result of calling the func function. If func accepts multiple parameters, the arraySort function will also parse the same quantity of array parameters as the func function parameters. More detailed examples are at the end of arraySort.
Syntax

arraySort([func,] arr, ...)

Integer sorting example:

SELECT arraySort([1, 3, 3, 0]);

┌─arraySort([1, 3, 3, 0])─┐ 
│ [0,1,3,3]               │ 
└─────────────────────────┘

String sorting example:

SELECT arraySort(['hello', 'world', '!']);

┌─arraySort(['hello', 'world', '!'])─┐ 
│ ['!','hello','world']              │ 
└────────────────────────────────────┘

NULL, NaN, and Inf sorting order:

SELECT arraySort([1, nan, 2, NULL, 3, nan, -4, NULL, inf, -inf]);

┌─arraySort([1, nan, 2, NULL, 3, nan, -4, NULL, inf, -inf])─┐ 
│ [-inf,-4,1,2,3,inf,nan,nan,NULL,NULL]                     │ 
└───────────────────────────────────────────────────────────┘

• -Inf is the first element in the array.
• NULL is the last element in the array.
• NaN comes before NULL.
• Inf comes before NaN.

Caution: arraySort is a higher-order function. You can pass a lambda function as the first parameter. In this case, the sorting order is determined by the result of the lambda function call.
Let us look at the following example:

SELECT arraySort((x) -> -x, [1, 2, 3]) as res;

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

For each element in the source array, the lambda function returns a sorting key, that is, [1 -> -1, 2 -> -2, 3 -> -3]. Since the arraySort function sorts the keys in Asc order, the result is [3,2,1]. Therefore, the (x) -> -x lambda function sets the sorting order to Desc.
The lambda function can accept multiple parameters. In this case, you need to pass the same number of arrays to arraySort as the number of lambda parameters. The function uses the elements from the first input array to compose the result; the subsequent arrays are used as sorting keys. Example:

SELECT arraySort((x, y) -> y, ['hello', 'world'], [2, 1]) as res;

┌─res────────────────┐ 
│ ['world', 'hello'] │ 
└────────────────────┘

Here, the sorting keys for the corresponding elements of the first array ([‘hello’, ‘world’]) are defined in the second array ([2, 1]), that is, [‘hello’ -> 2, ‘world’ -> 1]. Since x is not used in the lambda function, the actual values in the source array do not affect the order of the result. Therefore, 'world' will be the first element in the result, and 'hello' will be the second element.
Other examples are shown below.

SELECT arraySort((x, y) -> y, [0, 1, 2], ['c', 'b', 'a']) as res;

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

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

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

arrayReverseSort #

Sorts the elements of the arr array in Desc order. If the func function is specified, the sorting order is determined by the result of the func function call. If func accepts multiple parameters, then the arrayReverseSort function will also accept the same number of arrays as parameters as the func function. More detailed examples are provided at the end of arrayReverseSort.
Syntax

arrayReverseSort([func,] arr, ...)

Example
Integer sorting:

SELECT arrayReverseSort([1, 3, 3, 0]);

┌─arrayReverseSort([1, 3, 3, 0])─┐ 
│ [3,3,1,0]                      │ 
└────────────────────────────────┘

String sorting:

--String sorting 
SELECT arrayReverseSort(['hello', 'world', '!']);

┌─arrayReverseSort(['hello', 'world', '!'])─┐ 
│ ['world','hello','!']                     │ 
└───────────────────────────────────────────┘

NULL, NaN, and Inf sorting order:

SELECT arrayReverseSort([1, nan, 2, NULL, 3, nan, -4, NULL, inf, -inf]) as res;

┌─res───────────────────────────────────┐ 
│ [inf,3,2,1,-4,-inf,nan,nan,NULL,NULL] │ 
└───────────────────────────────────────┘

• Inf is the first element in the array.
• NULL is the last element in the array.
• NaN comes before NULL.
• -Inf comes before NaN.

Caution: arraySort is a higher-order function. You can pass a lambda function as the first parameter. As shown in the following example.

SELECT arrayReverseSort((x) -> -x, [1, 2, 3]) as res;

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

The array is sorted as follows:

1. First, the source array ([1, 2, 3]) is sorted according to the result of the lambda function call. The result is [3, 2, 1].
2. The array obtained in the previous step is then reversed. Therefore, the final result is [1, 2, 3].

Lambda functions can accept multiple Params. In this case, you need to pass the same number of arrays to arrayReverseSort as the number of Params in the lambda function. The function uses the elements from the first input array to compose the result; it uses the subsequent arrays passed in as sorting keys. For example:

SELECT arrayReverseSort((x, y) -> y, ['hello', 'world'], [2, 1]) as res;

┌─res───────────────┐ 
│ ['hello','world'] │ 
└───────────────────┘

In this example, the array is sorted as follows:

1. First, the source array ([‘hello’, ‘world’]) is sorted according to the result of calling the lambda function. In the second array ([2, 1]), the sorting keys for the corresponding elements in the source array are defined. So, the sorting result is [‘world’, ‘hello’].
2. Reverse the sorted array obtained in the previous step. Therefore, the final result is [‘hello’, ‘world’].

Other examples are as follows.

SELECT arrayReverseSort((x, y) -> y, [4, 3, 5], ['a', 'b', 'c']) AS res;

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

SELECT arrayReverseSort((x, y) -> -y, [4, 3, 5], [1, 2, 3]) AS res;

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

arrayUniq #

If one Param is passed, it calculates the quantity of distinct elements in the array. If multiple Params are passed, it calculates the number of distinct tuples of elements at corresponding locations in multiple arrays.
Syntax

arrayUniq(arr, ...)

To obtain a list of unique items in an array, you can use arrayReduce('groupUniqArray', arr).

arrayJoin #

This is a very useful function.
Syntax

arrayJoin(arr)

Normal functions do not change the number of rows in the result set, but only calculate the value in each row (map). Aggregate functions compress multiple rows into one (fold or reduce). The 'arrayJoin' function takes each row and unfolds it into multiple rows (unfold).
This function takes an array as a Param and copies the row in the result set as many times as there are elements in the array. Except for the values in the column where this function is applied, all values are simply copied; the value in that column is replaced by the corresponding array value.
The query can use multiple arrayJoin functions. In this case, the transformation is carried out multiple times.
Caution: Note the ARRAY JOIN syntax in the SELECT query, which provides broader possibilities.
Example

SELECT arrayJoin([1, 2, 3] AS src) AS dst, 'Hello', src

┌─dst─┬─'Hello'─┬─src─────┐ 
│   1 │ Hello     │ [1,2,3] │ 
│   2 │ Hello     │ [1,2,3] │ 
│   3 │ Hello     │ [1,2,3] │ 
└─────┴───────────┴─────────┘

arrayDifference #

Calculates the difference between adjacent elements in the array. Returns an array, where the first element is 0, the second is the difference a[1] - a[0], and so on. The type of elements in the result array is determined by the rules for type inference of subtraction (for example, UInt8 - UInt8 = Int16).
Syntax

arrayDifference(array)

Param

• array – Type: array.

Return value
Returns an array of differences between adjacent elements.
Type: UInt*, Int*, Float*.
Example
Query statement:

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

Result:

┌─arrayDifference([1, 2, 3, 4])─┐ 
│ [0,1,1,1]                     │ 
└───────────────────────────────┘

Example of overflow caused by the result type being Int64:
Query statement:

SELECT arrayDifference([0, 10000000000000000000]);

Result:

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

arrayDistinct #

Returns an array containing all distinct elements in the array.
Syntax

arrayDistinct(array)

Param

• array – Type: array.

Return value
Returns an array containing distinct elements.
Example
Query statement:

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

Result:

┌─arrayDistinct([1, 2, 2, 3, 1])─┐ 
│ [1,2,3]                        │ 
└────────────────────────────────┘

arrayEnumerateDense #

Returns an array of the same size as the source array, indicating the position of each element's first appearance in the source array. For example: arrayEnumerateDense([10,20,10,30]) = [1,2,1,3].
Syntax

arrayEnumerateDense(arr)

Example

SELECT arrayEnumerateDense([10, 20, 10, 30])

┌─arrayEnumerateDense([10, 20, 10, 30])─┐ 
│ [1,2,1,3]                             │ 
└───────────────────────────────────────┘

arrayIntersect #

Returns the intersection of elements across all arrays.
Syntax

arrayIntersect(arr)

Example

SELECT 
    arrayIntersect([1, 2], [1, 3], [2, 3]) AS no_intersect, 
    arrayIntersect([1, 2], [1, 3], [1, 4]) AS intersect

┌─no_intersect─┬─intersect─┐ 
│ []           │ [1]       │ 
└──────────────┴───────────┘

arrayReduce #

Applies an aggregate function to array elements and returns its result. The aggregate function's name is passed as a string in single quotes, such as 'max', 'sum'. When using parameterized aggregate functions, parameters are indicated after the function name in parentheses, for example "uniqUpTo(6)".
Syntax

arrayReduce(agg_func, arr1, arr2, ..., arrN)

Param

• agg_func — Name of the aggregate function, should be a constant string.
• arr — Any quantity of array type columns as parameters for the aggregate function.

Return value
Example
Query statement:

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

Result:

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

If the aggregate function takes multiple parameters, it must be applied to multiple arrays of the same size.
Query statement:

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

Result:

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

Example with parameterized aggregate function:
Query statement:

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

Result:

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

arrayReduceInRanges #

Applies an aggregate function to array elements within the given ranges and returns an array containing the result corresponding to each range. This function returns the same result as multiple arrayReduce(agg_func, arraySlice(arr1, index, length), ...).
Syntax

arrayReduceInRanges(agg_func, ranges, arr1, arr2, ..., arrN)

Param

• agg_func — Name of the aggregate function, should be a constant string.
• ranges — The ranges to be aggregated should be an array of tuples as elements, each containing an index and length range.
• arr — Any quantity of array type columns as parameters for the aggregate function.

Return value

• Returns an array containing the results of the aggregate function within the specified ranges.

Type: array.
Example
Query statement:

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

Result:

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

arrayReverse #

Returns an array of the same size as the original array, containing elements in reverse order.
Syntax

arrayReverse(arr)

Example

SELECT arrayReverse([1, 2, 3])

┌─arrayReverse([1, 2, 3])─┐ 
│ [3,2,1]                 │ 
└─────────────────────────┘

reverse #

Has the same effect as "arrayReverse".
Syntax

reverse(arr)

arrayFlatten #

Flatten nested arrays.
Function:

• Applicable to nested arrays of any depth.
• No changes are made to arrays that are already flattened.

The flattened array contains all elements from all source arrays.
Syntax

flatten(array_of_arrays)

alias: flatten.
Param

• array_of_arrays — nested array. For example: [[1,2,3], [4,5]].

Example

SELECT flatten([[[1]], [[2], [3]]]);

┌─flatten(array(array([1]), array([2], [3])))─┐ 
│ [1,2,3]                                     │ 
└─────────────────────────────────────────────┘

arrayCompact #

Delete consecutive repeated elements from an array. The order of the resulting values is determined by the order in the source array.
Syntax

arrayCompact(arr)

Param
arr — Type: array.
Return value
An array with no repeated elements.
Type: Array.
Example
Search statement:

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

As a result:

┌─arrayCompact([1, 1, nan, nan, 2, 3, 3, 3])─┐ 
│ [1,nan,nan,2,3]                            │ 
└────────────────────────────────────────────┘

arrayZip #

Combine multiple arrays into a single array. The resulting array contains the corresponding elements of the source arrays grouped into tuples in the order listed in the parameters.
Syntax

arrayZip(arr1, arr2, ..., arrN)

Param

• arrN — N arrays.

This function can adopt any number of arrays of different types. The size of all input arrays must be equal.
Return value

• An array grouping elements from the source arrays into tuples. The data type in the tuple is the same as the input array type, and the order matches the order in which the arrays are passed.

Type: array.
Example
Search statement:

SELECT arrayZip(['a', 'b', 'c'], [5, 2, 1]);

As a result:

┌─arrayZip(['a', 'b', 'c'], [5, 2, 1])─┐ 
│ [('a',5),('b',2),('c',1)]            │ 
└──────────────────────────────────────┘

arrayAUC #

Calculate AUC.
AUC: Area under the ROC curve, which is a concept in machine learning. For more details, view: https://en.wikipedia.org/wiki/Receiver_operating_characteristic#Area_under_the_curve
Syntax

arrayAUC(arr_scores, arr_labels)

Param

• arr_scores — Scores provided by the prediction model.
• arr_labels — Tags of the sample, usually 1 for positive sample and 0 for negative sample.

Return value
Return a Float64 type AUC value.
Example
Search statement:

select arrayAUC([0.1, 0.4, 0.35, 0.8], [0, 0, 1, 1]);

As a result:

┌─arrayAUC([0.1, 0.4, 0.35, 0.8], [0, 0, 1, 1])─┐ 
│                                          0.75 │ 
└───────────────────────────────────────────────┘

arrayMap #

Returns the array obtained by applying the func function to each element in the arr array.
Syntax

arrayMap(func, arr1, ...)

Example:

SELECT arrayMap(x -> (x + 2), [1, 2, 3]) as res;

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

The following example shows how to create a tuple of elements from different arrays:

SELECT arrayMap((x, y) -> (x, y), [1, 2, 3], [4, 5, 6]) AS res

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

Caution, arrayMap is a higher-order function. You must pass a lambda function as the first parameter, and it cannot be omitted.

arrayFilter #

Returns an array containing only the elements in arr1 for which func returns a value that is not 0.
Syntax

arrayFilter(func, arr1, ...)

Example

SELECT arrayFilter(x -> x LIKE '%World%', ['Hello', 'abc World']) AS res

┌─res───────────┐ 
│ ['abc World'] │ 
└───────────────┘

SELECT 
    arrayFilter( 
        (i, x) -> x LIKE '%World%', 
        arrayEnumerate(arr), 
        ['Hello', 'abc World'] AS arr) 
    AS res

┌─res─┐ 
│ [2] │ 
└─────┘

Caution, arrayFilter is a higher-order function. You must pass a lambda function as the first parameter, and it cannot be omitted.

arrayFill #

Scan arr1 from the first element to the last element. If func returns 0, replace arr1[i] with arr1[i - 1]. The first element of arr1 will not be replaced.
Syntax

arrayFill(func, arr1, ...)

Example

SELECT arrayFill(x -> not isNull(x), [1, null, 3, 11, 12, null, null, 5, 6, 14, null, null]) AS res

┌─res──────────────────────────────┐ 
│ [1,1,3,11,12,12,12,5,6,14,14,14] │ 
└──────────────────────────────────┘

Note, arrayFill is a higher-order function. You must pass the lambda function as the first parameter, and it cannot be omitted.

arrayReverseFill #

Scan arr1 from the last element to the first element. If func returns 0, replace arr1[i] with arr1[i + 1]. The last element of arr1 will not be replaced.
Syntax

arrayReverseFill(func, arr1, ...)

Example

SELECT arrayReverseFill(x -> not isNull(x), [1, null, 3, 11, 12, null, null, 5, 6, 14, null, null]) AS res

┌─res────────────────────────────────┐ 
│ [1,3,3,11,12,5,5,5,6,14,NULL,NULL] │ 
└────────────────────────────────────┘

Note, arrayReverseFill is a higher-order function. You must pass the lambda function as the first parameter, and it cannot be omitted.

arraySplit #

Split arr1 into multiple arrays. When func returns a value other than 0, the array is split to the left of the element. The array will not be split before the first element.
Syntax

arraySplit(func, arr1, ...)

Example

SELECT arraySplit((x, y) -> y, [1, 2, 3, 4, 5], [1, 0, 0, 1, 0]) AS res

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

Note, arraySplit is a higher-order function. You must pass the lambda function as the first parameter, and it cannot be omitted.

arrayReverseSplit #

Split arr1 into multiple arrays. When func returns a value other than 0, the array is split to the right of the element. The array will not be split after the last element.
Syntax

arrayReverseSplit(func, arr1, ...)

Example

SELECT arrayReverseSplit((x, y) -> y, [1, 2, 3, 4, 5], [1, 0, 0, 1, 0]) AS res

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

Note, arrayReverseSplit is a higher-order function. You must pass the lambda function as the first parameter, and it cannot be omitted.

arrayExists #

If at least one element in arr causes func to return a value other than 0, then 1 is returned. Otherwise, it returns 0.
Note, arrayExists is a higher-order function.
Syntax

arrayExists([func,] arr1, …)

Param

• func– The higher-order function must return UInt8
• arr1,..- Array as the function input

Return value

• If at least one element in arr causes func to return a non-zero value, then 1 is returned. Otherwise, 0 is returned.

Example

SELECT arrayExists((x,y)->x==y,[1, 2, 2, 3, 1],[4, 5, 6, 7, 8]);

┌─arrayExists(lambda(tuple(x, y), equals(x, y)), [1, 2, 2, 3, 1], [4, 5, 6, 7, 8])─┐ 
│ 0                                                                                │ 
└──────────────────────────────────────────────────────────────────────────────────┘

SELECT arrayExists((x,y)->x==y,[1, 2, 2, 3, 1],[1, 5, 6, 7, 8]);

┌─arrayExists(lambda(tuple(x, y), equals(x, y)), [1, 2, 2, 3, 1], [1, 5, 6, 7, 8])─┐ 
│ 1                                                                                │ 
└──────────────────────────────────────────────────────────────────────────────────┘

arrayAll #

If func returns a value other than 0 for all elements in arr, then 1 is returned. Otherwise, it returns 0.
Syntax

arrayAll([func,] arr1, ...)

Note, arrayAll is a higher-order function. You must pass the lambda function as the first parameter, and it cannot be omitted.

arrayFirst #

Returns the first element in the arr1 array for which func returns a non-zero value. If such a value is not found, the default value is returned.
Syntax

arrayFirst(func, arr1, ...)

Note, arrayFirst is a higher-order function. You must pass the lambda function as the first Param, and it cannot be omitted.

arrayLast #

Returns the last element in the arr1 array for which the value returned by func is not 0. If no such value is found, the Default will be returned.
Syntax

arrayLast(func, arr1, ...)

Note, arrayLast is a higher-order function. You must pass the lambda function as the first Param, and it cannot be omitted.

arrayFirstIndex #

Returns the index of the first element in the arr1 array for which the value returned by func is not 0. If no such value is found, 0 will be returned.
Syntax

arrayFirstIndex(func, arr1, ...)

Note, arrayFirstIndex is a higher-order function. You must pass the lambda function as the first Param, and it cannot be omitted.

arrayLastIndex #

Returns the index of the last element in the arr1 array for which the value returned by func is not 0. If no such value is found, 0 will be returned.
Syntax

arrayLastIndex(func, arr1, ...)

Note, arrayLastIndex is a higher-order function. You must pass the lambda function as the first Param, and it cannot be omitted.

arrayMin #

Returns the Min element in the source array.
If the func function is specified, returns the Min value of the elements transformed by this function.
Note, arrayMin is a higher-order function. You can pass the lambda function as the first Param, and it cannot be omitted.
Syntax

arrayMin([func,] arr)

Param

• func — Type: Expression.
• arr — Type: array.

Return value

• The Min value of the function value (or the Min of the array).

Type: If func is specified, Match the Type of the value returned by func, Otherwise Match the element type of the array.
Example
Query statement:

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

As a result:

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

Query statement:

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

As a result:

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

arrayMax #

Returns the Max value of the elements in the source array.
If the func function is specified, returns the Max value of the elements transformed by this function.
Note, arrayMax is a higher-order function. You can pass the lambda function as the first Param, and it cannot be omitted.
Syntax

arrayMax([func,] arr)

Param

• func — Type: Expression.
• arr — Type: array.

Return value

• The Max value of the function value (or the Max of the array).

Type: If func is specified, Match the Type of the value returned by func, Otherwise Match the element type of the array.
Example
Query statement:

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

As a result:

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

Query statement:

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

As a result:

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

arraySum #

Back the sum of the elements in the source array.
If the func function is specified, Back the sum of the elements converted by this function.
Note, arraySum is a higher-order function. You can pass a lambda function as the first Param to it, and it cannot be omitted.
Syntax

arraySum([func,] arr)

Param

• func — Type: Expression.
• arr — Type: array.

Return value

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

Type: Decimal numbers in the source array (or converted values, if func is specified) — Decimal128, for floating-point numbers — Float64, for unsigned numbers — UInt64, for signed numbers — Int64.
Example
Query statement:

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

Result:

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

Query statement:

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

Result:

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

arrayAvg #

Back the average value of the elements in the source array.
If the func function is specified, Back the average value of the elements converted by this function.
Note, arrayAvg is a higher-order function. You can pass a lambda function as the first Param to it, and it cannot be omitted.
Syntax

arrayAvg([func,] arr)

Param

• func — Type: Expression.
• arr — Type: array.

Return value

• The average value of the function values (or the array average value).

Type: Float64.
Example
Query statement:

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

Result:

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

Query statement:

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

Result:

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

arrayCumSum #

Back an array of the partial sums of the elements in the source array (running sum). If the func function is specified, the values of the array elements are converted by this function before calculating the sum.
Syntax

arrayCumSum([func,] arr1, ...)

Example:

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

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

Note, arrayCumSum is a higher-order function. You can pass a lambda function as the first Param to it, and it cannot be omitted.

arrayCumSumNonNegative #

Same as arrayCumSum, Back an array of the partial sums of the elements in the source array (running sum). Unlike arrayCumSum, when the return value contains a value less than zero, replace that value with zero, and run subsequent calculations with zero as the parameter.
Example:

arrayCumSumNonNegative(arr)

Example

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

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

Note arraySumNonNegative is a higher-order function. You can pass a lambda function as the first Param to it, and it cannot be omitted.

arrayProduct #

Multiply the elements in an array.
Syntax

arrayProduct(arr)

Param

• arr — Type: numerical value array.

Return value

• The PRODUCT of the array elements.

Type: Float64.
Example
Query statement:

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

Result:

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

Query statement:

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

The return value type is always Float64. Result:

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

arrayElement #

n retrieves the element at index n from the array arr. n must be of any integer type.
Indices in the array start from one.
Supports negative indices. In this case, it selects the corresponding element counted from the end. For example, arr[-1] is the last item in the array.
If the index is out of the array's bounds, it returns a default value (number is 0, string is an empty string, and others), except for the case of a non-constant array and a constant index of 0 (in this case, an error Array indices are 1-based occurs).
Syntax

arrayElement(array, n)

Param

• array array.
• n index in the array.

Return value

• n retrieves the element at index n from the array arr

Example

SELECT arrayElement([1, 2, 2, 3, 1],3);

┌─arrayElement([1, 2, 2, 3, 1], 3)─┐ 
│ 2                                │ 
└──────────────────────────────────┘