数组函数

请注意:
下文中的一些示例引用自 ClickHouse 社区文档 并经过一定修改确保可以在 ByteHouse 中正常使用。

array

Creates an array from the function arguments.

The arguments must be constants and have types that have the smallest common type. At least one argument must be passed. Otherwise, it isn’t clear which type of array to create. That is, you can’t use this function to create an empty array (to do that, use the ‘emptyArray*’ function described above).

Syntax

array(x1, …)

Arguments

• x1,... – must be constants and have types that have the smallest common type

Returned value

• Returns an ‘Array(T)’ type result, where ‘T’ is the smallest common type out of the passed arguments.

Example

SELECT array(1,2,3);

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

arrayAll

Returns 1 if func returns something other than 0 for all the elements in arr . Otherwise, it returns 0.
Note that the arrayAll is a higher-order function. You can pass a lambda function to it as the first argument.

Syntax

arrayAll([func,] arr1, …)

Arguments

• func – higher-order function which must return UInt8
• arr1,.. - arrays as input for func

Returned value

• Returns 1 if func returns something other than 0 for all the elements in arr

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                                                                 │
└───────────────────────────────────────────────────────────────────┘

arrayConcat

Combines arrays passed as arguments.

Syntax

arrayConcat(arrays)

Arguments

• arrays – Arbitrary number of arguments of Array type.

Returned value

• A combined array.

Example

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

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

arrayCount

Returns the number of elements in the arr array for which func returns something other than 0. If ‘func’ is not specified, it returns the number of non-zero elements in the array.

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

Syntax

arrayCount([func,] arr1, …)

Arguments

• func – higher-order function which must return UInt8
• arr1,.. - arrays as input for func

Returned value

• number of elements in the arr array for which func returns something other than 0

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 partial sums of elements in the source array (a running sum). If the func function is specified, then the values of the array elements are converted by this function before summing.

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

Syntax

arrayCumSum([func,] arr1, …)

Arguments

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

Returned value

• An array of partial 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 partial sums of elements in the source array (a running sum). Different arrayCumSum , when returned value contains a value less than zero, the value is replace with zero and the subsequent calculation is performed with zero parameters. For example:

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

Syntax

arrayCumSumNonNegative([func,] arr1, …)

Arguments

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

Returned value

• An array of partial sums of 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] │
└──────────────┘

arrayDifference

Calculates the difference between adjacent array elements. Returns an array where the first element will be 0, the second is the difference between a[1] - a[0] , etc. The type of elements in the resulting array is determined by the type inference rules for subtraction (e.g. UInt8 - UInt8 = Int16 ).

Syntax

arrayDifference(array)

Arguments

• array – an Array.

Returned values
Returns an array of differences between adjacent elements.
Type: UInt*, Int*, Float*.

Example

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

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

Example of the overflow due to result type Int64:

SELECT arrayDifference([0, 10000000000000000000]);

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

arrayDistinct

Takes an array, returns an array containing the distinct elements only.

Syntax

arrayDistinct(array)

Arguments

• array – an Array.

Returned values

• Returns an array containing the distinct elements.

Example

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

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

arrayElement

Get the element with the index n from the array arr . n must be any integer type.
Indexes in an array begin from one.

Negative indexes are supported. In this case, it selects the corresponding element numbered from the end. For example, arr[-1] is the last item in the array.

If the index falls outside of the bounds of an array, it returns some default value (0 for numbers, an empty string for strings, etc.), except for the case with a non-constant array and a constant index 0 (in this case there will be an error Array indices are 1-based ).

Syntax

arrayElement(array, n)

Arguments

• array – an Array.
• n - an Index in the array.

Returned values

• Get the element with the index n from the array arr

Example

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

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

arrayEnumerate

Returns the array [1, 2, 3, … ]

This function is normally used with ARRAY JOIN. It allows counting something just once for each array after applying ARRAY JOIN.

Syntax

arrayEnumerate(arr)

Arguments

• arr – an Array.

Returned values

• Returns the array [1, 2, 3, … ]

Example

SELECT number, num FROM numbers(5) ARRAY JOIN arrayEnumerate([1,2,3]) as num

┌─number─┬─num─┐
│ 0      │ 1   │
│ 0      │ 2   │
│ 0      │ 3   │
│ 1      │ 1   │
│ 1      │ 2   │
│ 1      │ 3   │
│ 2      │ 1   │
│ 2      │ 2   │
│ 2      │ 3   │
│ 3      │ 1   │
│ 3      │ 2   │
│ 3      │ 3   │
│ 4      │ 1   │
│ 4      │ 2   │
│ 4      │ 3   │
└────────┴─────┘

arrayEnumerateDense

Returns an array of the same size as the source array, indicating where each element first appears in the source array.

Syntax

arrayEnumerateDense(arr)

Arguments

• arr – an Array.

Returned values

• An array where each element first appears in the source array

Example

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

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

arrayEnumerateUniq

Returns an array the same size as the source array, indicating for each element what its position is among elements with the same value.

For example: arrayEnumerateUniq([10, 20, 10, 30]) = [1, 1, 2, 1].

This function is useful when using ARRAY JOIN and aggregation of array elements.

Syntax

arrayEnumerateUniq(arr, …)

Arguments

• arr – an Array.

Returned values

• Returns an array the same size as the source array, indicating for each element what its position is among elements with the same value.

Example

SELECT arrayEnumerateUniq([10, 20, 10, 30]) as res

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

The arrayEnumerateUniq function can take multiple arrays of the same size as arguments. In this case, uniqueness is considered for tuples of elements in the same positions in all the 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 necessary when using ARRAY JOIN with a nested data structure and further aggregation across multiple elements in this structure.

arrayExists

Returns 1 if there is at least one element in arr for which func returns something other than 0. Otherwise, it returns 0.

Note that the arrayExists is a higher-order function.

Syntax

arrayExists([func,] arr1, …)

Arguments

• func – higher-order function which must return UInt8
• arr1,.. - arrays as input for func

Returned values

• Returns 1 if there is at least one element in arr for which func returns something other than 0. Otherwise, it returns 0.

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                                                                                │
└──────────────────────────────────────────────────────────────────────────────────┘

arrayFilter

Note that the arrayFilter is a higher-order function. You must pass a lambda function to it as the first argument, and it can’t be omitted.
Returns an array containing only the elements in arr1 for which func returns something other than 0.

Syntax

arrayFilter(func, arr1, …)

Arguments

• func – higher-order function which must return UInt8
• arr1,.. - arrays as input for func

Returned values

• Returns an array containing only the elements in arr1 for which func returns something other than 0.

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] │
└─────┘