Functions

There are at least* two types of functions - regular functions (they are just called “functions”) and aggregate functions. These are completely different concepts. Regular functions work as if they are applied to each row separately (for each row, the result of the function does not depend on the other rows). Aggregate functions accumulate a set of values from various rows (i.e. they depend on the entire set of rows).

In this section we discuss regular functions. For aggregate functions, see the section “Aggregate functions”.

* - There is a third type of function that the ‘arrayJoin’ function belongs to; table functions can also be mentioned separately.*

ARITHMETIC

For all arithmetic functions, the result type is calculated as the smallest number type that the result fits in, if there is such a type. The minimum is taken simultaneously based on the number of bits, whether it is signed, and whether it floats. If there are not enough bits, the highest bit type is taken.

Example

SELECT toTypeName(0), toTypeName(0 + 0), toTypeName(0 + 0 + 0), toTypeName(0 + 0 + 0 + 0)
┌─toTypeName(0)─┬─toTypeName(plus(0, 0))─┬─toTypeName(plus(plus(0, 0), 0))─┬─toTypeName(plus(plus(plus(0, 0), 0), 0))─┐
│ UInt8         │ UInt16                 │ UInt32                          │ UInt64                                   │
└───────────────┴────────────────────────┴─────────────────────────────────┴──────────────────────────────────────────┘

Arithmetic functions work for any pair of types from UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, or Float64.

Overflow is produced the same way as in C++.

plus(a, b), a + b operator​

Calculates the sum of the numbers. You can also add integer numbers with a date or date and time. In the case of a date, adding an integer means adding the corresponding number of days. For a date with time, it means adding the corresponding number of seconds.

Example

"plus(1,2) = 3"

minus(a, b), a - b operator​

Calculates the difference. The result is always signed.

You can also calculate integer numbers from a date or date with time. The idea is the same – see above for ‘plus’.

Example

"minus(5,2) = 3"

multiply(a, b), a * b operator​

Calculates the product of the numbers.

Example

"divide(50,2) = 2.5e+01"

divide(a, b), a / b operator​

Calculates the quotient of the numbers. The result type is always a floating-point type. It is not integer division. For integer division, use the ‘intDiv’ function. When dividing by zero you get ‘inf’, ‘-inf’, or ‘nan’.

Example

"divide(50,2) = 2.5e+01"

intDiv(a, b)​

Calculates the quotient of the numbers. Divides into integers, rounding down (by the absolute value). An exception is thrown when dividing by zero or when dividing a minimal negative number by minus one.

Example

"intDiv(10, -2) = -5"

intDivOrZero(a, b)​

Differs from ‘intDiv’ in that it returns zero when dividing by zero or when dividing a minimal negative number by minus one.

Example

"intDivOrZero(10, -2) = -5"

modulo(a, b), a % b operator​

Calculates the remainder when dividing a by b. The result type is an integer if both inputs are integers. If one of the inputs is a floating-point number, the result is a floating-point number. The remainder is computed like in C++. Truncated division is used for negative numbers. An exception is thrown when dividing by zero or when dividing a minimal negative number by minus one.

Example

"modulo(10, 3) = 1"

moduloOrZero(a, b)​

Differs from modulo in that it returns zero when the divisor is zero.

Example

"moduloOrZero(10, 5) = 0"

negate(a), -a operator​

Calculates a number with the reverse sign. The result is always signed.

Example

"negate(20) = -20"

abs(a)​

Calculates the absolute value of the number (a). That is, if a \< 0, it returns -a. For unsigned types it does not do anything. For signed integer types, it returns an unsigned number.

Example

"abs(-2) = 2"

gcd(a, b)​

Returns the greatest common divisor of the numbers. An exception is thrown when dividing by zero or when dividing a minimal negative number by minus one.

Example

"gcd(27,18) = 9"

lcm(a, b)​

Returns the least common multiple of the numbers. An exception is thrown when dividing by zero or when dividing a minimal negative number by minus one.

Example 

"lcm(27,18) = 54"

ARRAY

empty​

Checks whether the input array is empty.

Syntax

empty([x])

An array is considered empty if it does not contain any elements.

NOTE

Can be optimized by enabling the optimize_functions_to_subcolumns setting. With optimize_functions_to_subcolumns = 1 the function reads only size0 subcolumn instead of reading and processing the whole array column. The query SELECT empty(arr) FROM TABLE; transforms to SELECT arr.size0 = 0 FROM TABLE;.

The function also works for strings or UUID.

Arguments

  • [x] — Input array. Array.

Returned value

  • Returns 1 for an empty array or 0 for a non-empty array.

Type: UInt8.

Example

Query:

SELECT empty([]);

Result:

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

notEmpty​

Checks whether the input array is non-empty.

Syntax

notEmpty([x])

An array is considered non-empty if it contains at least one element.

NOTE

Can be optimized by enabling the optimize_functions_to_subcolumns setting. With optimize_functions_to_subcolumns = 1 the function reads only size0 subcolumn instead of reading and processing the whole array column. The query SELECT notEmpty(arr) FROM table transforms to SELECT arr.size0 != 0 FROM TABLE.

The function also works for strings or UUID.

Arguments

  • [x] — Input array. Array.

Returned value

  • Returns 1 for a non-empty array or 0 for an empty array.

Type: UInt8.

Example

Query:

SELECT notEmpty([1,2]);

Result:

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

length​

Returns the number of items in the array. The result type is UInt64. The function also works for strings.

Syntax

length(string)

Can be optimized by enabling the optimize_functions_to_subcolumns setting. With optimize_functions_to_subcolumns = 1 the function reads only size0 subcolumn instead of reading and processing the whole array column. The query SELECT length(arr) FROM table transforms to SELECT arr.size0 FROM TABLE.

Example

length(\"ABC Corporation\")

emptyArrayUInt8, emptyArrayUInt16, emptyArrayUInt32, emptyArrayUInt64

emptyArrayInt8, emptyArrayInt16, emptyArrayInt32, emptyArrayInt64​

emptyArrayFloat32, emptyArrayFloat64​

emptyArrayDate, emptyArrayDateTime​

emptyArrayString​

Accepts zero arguments and returns an empty array of the appropriate type.

emptyArrayToSingle​

Accepts an empty array and returns a one-element array that is equal to the default value.

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

Returns an array of UInt numbers from start to end - 1 by step.

Syntax

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

Arguments

  • start — The first element of the array. Optional, required if step is used. Default value: 0. UInt

  • end — The number before which the array is constructed. Required. UInt

  • step — Determines the incremental step between each element in the array. Optional. Default value: 1. UInt

Returned value

  • Array of UInt numbers from start to end - 1 by step.

Implementation details

  • All arguments must be positive values: start, end, step are UInt data types, as well as elements of the returned array.

  • An exception is thrown if query results in arrays with a total length of more than number of elements specified by the function_range_max_elements_in_block setting.

Examples

Query:

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

Result:

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

array(x1, …), operator [x1, …]​

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, because 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). Returns an ‘Array(T)’ type result, where ‘T’ is the smallest common type out of the passed arguments.

Example

SELECT array(1,2,3);

arrayConcat​

Combines arrays passed as arguments.

arrayConcat(arrays)

Arguments

  • arrays – Arbitrary number of arguments of Array type. Example

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

has(arr, elem)​

Checks whether the ‘arr’ array has the ‘elem’ element. Returns 0 if the element is not in the array, or 1 if it is.

NULL is processed as a value.

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

hasAll​

Checks whether one array is a subset of another.

hasAll(set, subset)

Arguments

  • set – Array of any type with a set of elements.

  • subset – Array of any type with elements that should be tested to be a subset of set.

Return values

  • 1, if set contains all of the elements from subset.

  • 0, otherwise.

Peculiar properties

  • An empty array is a subset of any array.

  • Null processed as a value.

  • Order of values in both of arrays does not matter.

Examples

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 intersection by some elements.

hasAny(array1, array2)

Arguments

  • array1 – Array of any type with a set of elements.

  • array2 – Array of any type with a set of elements.

Return values

  • 1, if array1 and array2 have one similar element at least.

  • 0, otherwise.

Peculiar properties

  • Null processed as a value.

  • Order of values in both of arrays does not matter.

Examples

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 the elements of array2 appear in array1 in the same exact order. Therefore, the function will return 1, if and only if array1 = prefix + array2 + suffix.

hasSubstr(array1, array2)

In other words, the functions will check whether all the elements of array2 are contained in array1 like the hasAll function. In addition, it will check that the elements are observed in the same order in both array1 and array2.

For Example:

  • hasSubstr([1,2,3,4], [2,3]) returns 1. However, hasSubstr([1,2,3,4], [3,2]) will return 0.

  • hasSubstr([1,2,3,4], [1,2,3]) returns 1. However, hasSubstr([1,2,3,4], [1,2,4]) will return 0.

Arguments

  • array1 – Array of any type with a set of elements.

  • array2 – Array of any type with a set of elements.

Return values

  • 1, if array1 contains array2.

  • 0, otherwise.

Peculiar properties

  • The function will return 1 if array2 is empty.

  • Null processed as a value. In other words hasSubstr([1, 2, NULL, 3, 4], [2,3]) will return 0. However, hasSubstr([1, 2, NULL, 3, 4], [2,NULL,3]) will return 1

  • Order of values in both of arrays does matter.

Examples

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(arr, x)​

Returns the index of the first ‘x’ element (starting from 1) if it is in the array, or 0 if it is not.

Example:

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

Elements set to NULL are handled as normal values.

arrayCount([func,] arr1, …)​

Returns the number of elements for which func(arr1[i], …, arrN[i]) 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.

Example

arrayCount(lambda(tuple(x, y), equals(x, y)), [1, 2, 3], [1, 5, 3]) = 2

countEqual(arr, x)​

Returns the number of elements in the array equal to x. Equivalent to arrayCount (elem -> elem = x, arr).

NULL elements are handled as separate values.

Example

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

arrayEnumerate(arr)​

Returns the array [1, 2, 3, …, length (arr) ]

This function is normally used with ARRAY JOIN. It allows counting something just once for each array 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 strings received after applying ARRAY JOIN), and Hits is the number of pageviews (strings before ARRAY JOIN). In this particular case, you can get the same result in an easier way:

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 array indexes for elements that match a condition.

arrayEnumerateUniq(arr, …)​

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. 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 goal ID has a calculation of the number of conversions (each element in the Goals nested data structure is a goal that was reached, which we refer to as a conversion) and the number of sessions. Without ARRAY JOIN, we would have counted the number of sessions as sum(Sign). But in this particular case, the rows were multiplied by the nested Goals structure, so in order to count each session one time after this, we apply a condition to the value of the arrayEnumerateUniq(Goals.ID) function.

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.

arrayPopBack​

Removes the last item from the array.

arrayPopBack(array)

Arguments

  • array – Array.

Example

SELECT arrayPopBack([1, 2, 3]) AS res;
┌─res───┐
│ [1,2] │
└───────┘

arrayPopFront​

Removes the first item from the array.

arrayPopFront(array)

Arguments

  • array – Array.

Example

SELECT arrayPopFront([1, 2, 3]) AS res;
┌─res───┐
│ [2,3] │
└───────┘

arrayPushBack​

Adds one item to the end of the array.

arrayPushBack(array, single_value)

Arguments

  • array – Array.

  • single_value – A single value. Only numbers can be added to an array with numbers, and only strings can be added to an array of strings. When adding numbers, ClickHouse automatically sets the single_value type for the data type of the array. For more information about the types of data in ClickHouse, see “Data types”. Can be NULL. The function adds a NULL element to an array, and the type of array elements converts to Nullable.

Example

SELECT arrayPushBack(['a'], 'b') AS res;
┌─res───────┐
│ ['a','b'] │
└───────────┘

arrayPushFront​

Adds one element to the beginning of the array.

arrayPushFront(array, single_value)

Arguments

  • array – Array.

  • single_value – A single value. Only numbers can be added to an array with numbers, and only strings can be added to an array of strings. When adding numbers, ClickHouse automatically sets the single_value type for the data type of the array. For more information about the types of data in ClickHouse, see “Data types”. Can be NULL. The function adds a NULL element to an array, and the type of array elements converts to Nullable.

Example

SELECT arrayPushFront(['b'], 'a') AS res;
┌─res───────┐
│ ['a','b'] │
└───────────┘

arrayResize​

Changes the length of the array.

arrayResize(array, size[, extender])

Arguments:

  • array — Array.

  • size — Required length of the array.

    • If size is less than the original size of the array, the array is truncated from the right.

  • If size is larger than the initial size of the array, the array is extended to the right with extender values or default values for the data type of the array items.

  • extender — Value for extending an array. Can be NULL.

Returned value:

An array of length size.

Examples of calls

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

arraySlice​

Returns a slice of the array.

arraySlice(array, offset[, length])

Arguments

  • array – Array of data.

  • offset – Indent from the edge of the array. A positive value indicates an offset on the left, and a negative value is an indent on the right. Numbering of the array items begins with 1.

  • length – The length of the required slice. If you specify a negative value, the function returns an open slice [offset, array_length - length]. If you omit the value, the function returns the slice [offset, the_end_of_array].

Example

SELECT arraySlice([1, 2, NULL, 4, 5], 2, 3) AS res;
┌─res────────┐
│ [2,NULL,4] │
└────────────┘

Array elements set to NULL are handled as normal values.

arraySort([func,] arr, …)​

Sorts the elements of the arr array in ascending order. If the func function is specified, sorting order is determined by the result of the func function applied to the elements of the array. If func accepts multiple arguments, the arraySort function is passed several arrays that the arguments of func will correspond to. Detailed examples are shown at the end of arraySort description.

Example of integer values sorting:

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

Example of string values sorting:

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

Consider the following sorting order for the NULL, NaN and Inf values:

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 values are first in the array.

  • NULL values are last in the array.

  • NaN values are right before NULL.

  • Inf values are right before NaN.

Note that arraySort is a higher-order function. You can pass a lambda function to it as the first argument. In this case, sorting order is determined by the result of the lambda function applied to the elements of the array.

Let’s consider the following example:

SELECT arraySort((x) -> -x, [1, 2, 3]) as res;
┌─res─────┐
│ [3,2,1] │
└─────────┘

For each element of the source array, the lambda function returns the sorting key, that is, [1 –> -1, 2 –> -2, 3 –> -3]. Since the arraySort function sorts the keys in ascending order, the result is [3, 2, 1]. Thus, the (x) –> -x lambda function sets the descending order in a sorting.

The lambda function can accept multiple arguments. In this case, you need to pass the arraySort function several arrays of identical length that the arguments of lambda function will correspond to. The resulting array will consist of elements from the first input array; elements from the next input array(s) specify the sorting keys. For example:

SELECT arraySort((x, y) -> y, ['hello', 'world'], [2, 1]) as res;
┌─res────────────────┐
│ ['world', 'hello'] │
└────────────────────┘

Here, the elements that are passed in the second array ([2, 1]) define a sorting key for the corresponding element from the source array ([‘hello’, ‘world’]), that is, [‘hello’ –> 2, ‘world’ –> 1]. Since the lambda function does not use x, actual values of the source array do not affect the order in the result. So, ‘hello’ will be the second element in the result, and ‘world’ will be the first.

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

NOTE

To improve sorting efficiency, the Schwartzian transform is used.

arrayReverseSort([func,] arr, …)​

Sorts the elements of the arr array in descending order. If the func function is specified, arr is sorted according to the result of the func function applied to the elements of the array, and then the sorted array is reversed. If func accepts multiple arguments, the arrayReverseSort function is passed several arrays that the arguments of func will correspond to. Detailed examples are shown at the end of arrayReverseSort description.

Example of integer values sorting:

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

Example of string values sorting:

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

Consider the following sorting order for the NULL, NaN and Inf values:

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 values are first in the array.

  • NULL values are last in the array.

  • NaN values are right before NULL.

  • -Inf values are right before NaN.

Note that the arrayReverseSort is a higher-order function. You can pass a lambda function to it as the first argument. Example is shown below.

SELECT arrayReverseSort((x) -> -x, [1, 2, 3]) as res;
┌─res─────┐
│ [1,2,3] │
└─────────┘

The array is sorted in the following way:

  1. At first, the source array ([1, 2, 3]) is sorted according to the result of the lambda function applied to the elements of the array. The result is an array [3, 2, 1].

  2. Array that is obtained on the previous step, is reversed. So, the final result is [1, 2, 3].

The lambda function can accept multiple arguments. In this case, you need to pass the arrayReverseSort function several arrays of identical length that the arguments of lambda function will correspond to. The resulting array will consist of elements from the first input array; elements from the next input array(s) specify the 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 in the following way:

  1. At first, the source array ([‘hello’, ‘world’]) is sorted according to the result of the lambda function applied to the elements of the arrays. The elements that are passed in the second array ([2, 1]), define the sorting keys for corresponding elements from the source array. The result is an array [‘world’, ‘hello’].

  2. Array that was sorted on the previous step, is reversed. So, the final result is [‘hello’, ‘world’].

Other examples are shown below.

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(arr, …)​

If one argument is passed, it counts the number of different elements in the array. If multiple arguments are passed, it counts the number of different tuples of elements at corresponding positions in multiple arrays.

If you want to get a list of unique items in an array, you can use arrayReduce(‘groupUniqArray’, arr).

Example

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

arrayJoin(arr)​

A special function. See the section “ArrayJoin function”.

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

Returned values

Returns an array of differences between adjacent elements.

Type: UInt*, Int*, Float*.

Example

Query:

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

Result:

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

Example of the overflow due to result type Int64:

Query:

SELECT arrayDifference([0, 10000000000000000000]);

Result:

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

arrayDistinct​

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

Syntax

arrayDistinct(array)

Arguments

Returned values

Returns an array containing the distinct elements.

Example

Query:

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

Result:

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

arrayEnumerateDense(arr)​

Returns an array of the same size as the source array, indicating 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]                             │
└───────────────────────────────────────┘

arrayIntersect(arr)​

Takes multiple arrays, returns an array with elements that are present in all source arrays.

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 name of the aggregation function is passed as a string in single quotes 'max', 'sum'. When using parametric aggregate functions, the parameter is indicated after the function name in parentheses 'uniqUpTo(6)'.

Syntax

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

Arguments

  • agg_func — The name of an aggregate function which should be a constant string.

  • arr — Any number of array type columns as the parameters of the aggregation function.

Returned value

Example

Query:

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

Result:

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

If an aggregate function takes multiple arguments, then this function must be applied to multiple arrays of the same size.

Query:

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

Result:

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

Example with a parametric aggregate function:

Query:

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 in given ranges and returns an array containing the result corresponding to each range. The function will return the same result as multiple arrayReduce(agg_func, arraySlice(arr1, index, length), ...).

Syntax

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

Arguments

  • agg_func — The name of an aggregate function which should be a constant string.

  • ranges — The ranges to aggretate which should be an array of tuples which containing the index and the length of each range.

  • arr — Any number of Array type columns as the parameters of the aggregation function.

Returned value

  • Array containing results of the aggregate function over specified ranges.

Type: Array.

Example

Query:

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(arr)​

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

Example:

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

reverse(arr)​

Synonym for “arrayReverse”

arrayFlatten​

Converts an array of arrays to a flat array.

Function:

  • Applies to any depth of nested arrays.

  • Does not change arrays that are already flat.

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

Syntax

flatten(array_of_arrays)

Alias: flatten.

Arguments

  • array_of_arrays — Array of arrays. For example, [[1,2,3], [4,5]].

Examples

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

arrayCompact​

Removes consecutive duplicate elements from an array. The order of result values is determined by the order in the source array.

Syntax

arrayCompact(arr)

Arguments

arr — The array to inspect.

Returned value

The array without duplicate.

Type: Array.

Example

Query:

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

Result:

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

arrayZip​

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

Syntax

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

Arguments

The function can take any number of arrays of different types. All the input arrays must be of equal size.

Returned value

  • Array with elements from the source arrays grouped into tuples. Data types in the tuple are the same as types of the input arrays and in the same order as arrays are passed.

Type: Array.

Example

Query:

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

Result:

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

arrayAUC​

Calculate AUC (Area Under the Curve, which is a concept in machine learning, see more details: https://en.wikipedia.org/wiki/Receiver_operating_characteristic#Area_under_the_curve).

Syntax

arrayAUC(arr_scores, arr_labels)

Arguments

  • arr_scores — scores prediction model gives.

  • arr_labels — labels of samples, usually 1 for positive sample and 0 for negtive sample.

Returned value

Returns AUC value with type Float64.

Example

Query:

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

Result:

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

arrayMap(func, arr1, …)​

Returns an array obtained from the original arrays by application of func(arr1[i], …, arrN[i]) for each element. Arrays arr1 … arrN must have the same number of elements.

Examples

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

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

arrayFilter(func, arr1, …)​

Returns an array containing only the elements in arr1 for which func(arr1[i], …, arrN[i]) returns something other than 0.

Examples

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

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.

arrayFill(func, arr1, …)​

Scan through arr1 from the first element to the last element and replace arr1[i] by arr1[i - 1] if func(arr1[i], …, arrN[i]) returns 0. The first element of arr1 will not be replaced.

Examples

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 that the arrayFill is a higher-order function. You must pass a lambda function to it as the first argument, and it can’t be omitted.

arrayReverseFill(func, arr1, …)​

Scan through arr1 from the last element to the first element and replace arr1[i] by arr1[i + 1] if func(arr1[i], …, arrN[i]) returns 0. The last element of arr1 will not be replaced.

Examples:

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 that the arrayReverseFill is a higher-order function. You must pass a lambda function to it as the first argument, and it can’t be omitted.

arraySplit(func, arr1, …)​

Split arr1 into multiple arrays. When func(arr1[i], …, arrN[i]) returns something other than 0, the array will be split on the left hand side of the element. The array will not be split before the first element.

Examples:

SELECT arraySplit((x, y) -> y, [1, 2, 3, 4, 5], [1, 0, 0, 1, 0]) AS res
┌─res─────────────┐
│ [[1,2,3],[4,5]] │
└─────────────────┘

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

arrayReverseSplit(func, arr1, …)​

Split arr1 into multiple arrays. When func(arr1[i], …, arrN[i]) returns something other than 0, the array will be split on the right hand side of the element. The array will not be split after the last element.

Examples:

SELECT arrayReverseSplit((x, y) -> y, [1, 2, 3, 4, 5], [1, 0, 0, 1, 0]) AS res
┌─res───────────────┐
│ [[1],[2,3,4],[5]] │
└───────────────────┘

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

arrayExists([func,] arr1, …)​

Returns 1 if there is at least one element in arr for which func(arr1[i], …, arrN[i]) returns something other than 0. Otherwise, it returns 0.

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

Example

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

arrayAll([func,] arr1, …)​

Returns 1 if func(arr1[i], …, arrN[i]) returns something other than 0 for all the elements in arrays. 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.

Example

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

arrayFirst(func, arr1, …)​

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

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

Example

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

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.

Example

SELECT arrayFirstIndex(x -> x LIKE '%World%', ['Hello World', 'abc World']) AS res
```json
Returns a bitwise 'AND' of two numbers.
```

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

Returned value

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

Type: if func is specified, matches func return value type, else matches the array elements type.

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

Returned value

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

Type: if func is specified, matches func return value type, else matches the array elements type.

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

Returned value

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

Type: for decimal numbers in source array (or for converted values, if func is specified) — Decimal128, for floating point numbers — Float64, for numeric unsigned — UInt64, and 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

Returned value

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

Type: 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 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 func(arr1[i], …, arrN[i]) before summing.

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(arr)​

Same as arrayCumSum, returns an array of partial sums of elements in the source array (a running sum). Different arrayCumSum, when then 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:

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.

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

BIT

Bit functions work for any pair of types from UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, or Float64. Some functions support String and FixedString types.

The result type is an integer with bits equal to the maximum bits of its arguments. If at least one of the arguments is signed, the result is a signed number. If an argument is a floating-point number, it is cast to Int64.

bitAnd(a, b)

​

Returns a bitwise 'AND' of two numbers.

bitOr(a, b)​

bitXor(a, b)​

bitNot(a)​

bitShiftLeft(a, b)​

Shifts the binary representation of a value to the left by a specified number of bit positions.

A FixedString or a String is treated as a single multibyte value.

Bits of a FixedString value are lost as they are shifted out. On the contrary, a String value is extended with additional bytes, so no bits are lost.

Syntax

bitShiftLeft(a, b)

Arguments

Returned value

  • Shifted value.

The type of the returned value is the same as the type of the input value.

Example

In the following queries bin and hex functions are used to show bits of shifted values.

SELECT 99 AS a, bin(a), bitShiftLeft(a, 2) AS a_shifted, bin(a_shifted);
SELECT 'abc' AS a, hex(a), bitShiftLeft(a, 4) AS a_shifted, hex(a_shifted);
SELECT toFixedString('abc', 3) AS a, hex(a), bitShiftLeft(a, 4) AS a_shifted, hex(a_shifted);

Result:

┌──a─┬─bin(99)──┬─a_shifted─┬─bin(bitShiftLeft(99, 2))─┐
│ 99 │ 01100011 │       140 │ 10001100                 │
└────┴──────────┴───────────┴──────────────────────────┘
┌─a───┬─hex('abc')─┬─a_shifted─┬─hex(bitShiftLeft('abc', 4))─┐
│ abc │ 616263     │ &0        │ 06162630                    │
└─────┴────────────┴───────────┴─────────────────────────────┘
┌─a───┬─hex(toFixedString('abc', 3))─┬─a_shifted─┬─hex(bitShiftLeft(toFixedString('abc', 3), 4))─┐
│ abc │ 616263                       │ &0        │ 162630                                        │
└─────┴──────────────────────────────┴───────────┴───────────────────────────────────────────────┘

bitShiftRight(a, b)​

Shifts the binary representation of a value to the right by a specified number of bit positions.

A FixedString or a String is treated as a single multibyte value. Note that the length of a String value is reduced as bits are shifted out.

Syntax

bitShiftRight(a, b)

Arguments

Returned value

  • Shifted value.

The type of the returned value is the same as the type of the input value.

Example

Query:

SELECT 101 AS a, bin(a), bitShiftRight(a, 2) AS a_shifted, bin(a_shifted);
SELECT 'abc' AS a, hex(a), bitShiftRight(a, 12) AS a_shifted, hex(a_shifted);
SELECT toFixedString('abc', 3) AS a, hex(a), bitShiftRight(a, 12) AS a_shifted, hex(a_shifted);

Result:

┌───a─┬─bin(101)─┬─a_shifted─┬─bin(bitShiftRight(101, 2))─┐
│ 101 │ 01100101 │        25 │ 00011001                   │
└─────┴──────────┴───────────┴────────────────────────────┘
┌─a───┬─hex('abc')─┬─a_shifted─┬─hex(bitShiftRight('abc', 12))─┐
│ abc │ 616263     │           │ 0616                          │
└─────┴────────────┴───────────┴───────────────────────────────┘
┌─a───┬─hex(toFixedString('abc', 3))─┬─a_shifted─┬─hex(bitShiftRight(toFixedString('abc', 3), 12))─┐
│ abc │ 616263                       │           │ 000616                                          │
└─────┴──────────────────────────────┴───────────┴─────────────────────────────────────────────────┘

bitRotateLeft(a, b)​

bitRotateRight(a, b)​

bitTest​

Takes any integer and converts it into binary form, returns the value of a bit at specified position. The countdown starts from 0 from the right to the left.

Syntax

SELECT bitTest(number, index)

Arguments

  • number – Integer number.

  • index – Position of bit.

Returned values

Returns a value of bit at specified position.

Type: UInt8.

Example

For example, the number 43 in base-2 (binary) numeral system is 101011.

Query:

SELECT bitTest(43, 1);

Result:

┌─bitTest(43, 1)─┐
│              1 │
└────────────────┘

Another example:

Query:

SELECT bitTest(43, 2);

Result:

┌─bitTest(43, 2)─┐
│              0 │
└────────────────┘

bitTestAll​

Returns result of logical conjuction (AND operator) of all bits at given positions. The countdown starts from 0 from the right to the left.

The conjuction for bitwise operations:

0 AND 0 = 0

0 AND 1 = 0

1 AND 0 = 0

1 AND 1 = 1

Syntax

SELECT bitTestAll(number, index1, index2, index3, index4, ...)

Arguments

  • number – Integer number.

  • index1, index2, index3, index4 – Positions of bit. For example, for set of positions (index1, index2, index3, index4) is true if and only if all of its positions are true (index1 ⋀ index2, ⋀ index3 ⋀ index4).

Returned values

Returns result of logical conjuction.

Type: UInt8.

Example

For example, the number 43 in base-2 (binary) numeral system is 101011.

Query:

SELECT bitTestAll(43, 0, 1, 3, 5);

Result:

┌─bitTestAll(43, 0, 1, 3, 5)─┐
│                          1 │
└────────────────────────────┘

Another example:

Query:

SELECT bitTestAll(43, 0, 1, 3, 5, 2);

Result:

┌─bitTestAll(43, 0, 1, 3, 5, 2)─┐
│                             0 │
└───────────────────────────────┘

bitTestAny​

Returns result of logical disjunction (OR operator) of all bits at given positions. The countdown starts from 0 from the right to the left.

The disjunction for bitwise operations:

0 OR 0 = 0

0 OR 1 = 1

1 OR 0 = 1

1 OR 1 = 1

Syntax

SELECT bitTestAny(number, index1, index2, index3, index4, ...)

Arguments

  • number – Integer number.

  • index1, index2, index3, index4 – Positions of bit.

Returned values

Returns result of logical disjuction.

Type: UInt8.

Example

For example, the number 43 in base-2 (binary) numeral system is 101011.

Query:

SELECT bitTestAny(43, 0, 2);

Result:

┌─bitTestAny(43, 0, 2)─┐
│                    1 │
└──────────────────────┘

Another example:

Query:

SELECT bitTestAny(43, 4, 2);

Result:

┌─bitTestAny(43, 4, 2)─┐
│                    0 │
└──────────────────────┘

bitCount​

Calculates the number of bits set to one in the binary representation of a number.

Syntax

bitCount(x)

Arguments

  • x — Integer or floating-point number. The function uses the value representation in memory. It allows supporting floating-point numbers.

Returned value

  • Number of bits set to one in the input number.

The function does not convert input value to a larger type (sign extension). So, for example, bitCount(toUInt8(-1)) = 8.

Type: UInt8.

Example

Take for example the number 333. Its binary representation: 0000000101001101.

Query:

SELECT bitCount(333);

Result:

┌─bitCount(333)─┐
│             5 │
└───────────────┘

bitHammingDistance​

Returns the Hamming Distance between the bit representations of two integer values. Can be used with SimHash functions for detection of semi-duplicate strings. The smaller is the distance, the more likely those strings are the same.

Syntax

bitHammingDistance(int1, int2)

Arguments

  • int1 — First integer value. Int64.

  • int2 — Second integer value. Int64.

Returned value

  • The Hamming distance.

Type: UInt8.

Examples

Query:

SELECT bitHammingDistance(111, 121);

Result:

┌─bitHammingDistance(111, 121)─┐
│                            3 │
└──────────────────────────────┘

With SimHash:

SELECT bitHammingDistance(ngramSimHash('cat ate rat'), ngramSimHash('rat ate cat'));

Result:

┌─bitHammingDistance(ngramSimHash('cat ate rat'), ngramSimHash('rat ate cat'))─┐
│                                                                            5 │
└──────────────────────────────────────────────────────────────────────────────┘

BITMAP

Bitmap functions work for two bitmaps Object value calculation, it is to return new bitmap or cardinality while using formula calculation, such as and, or, xor, and not, etc.

There are 2 kinds of construction methods for Bitmap Object. One is to be constructed by aggregation function groupBitmap with -State, the other is to be constructed by Array Object. It is also to convert Bitmap Object to Array Object.

RoaringBitmap is wrapped into a data structure while actual storage of Bitmap objects. When the cardinality is less than or equal to 32, it uses Set objet. When the cardinality is greater than 32, it uses RoaringBitmap object. That is why storage of low cardinality set is faster.

For more information on RoaringBitmap, see: CRoaring.

bitmapBuild​

Build a bitmap from unsigned integer array.

bitmapBuild(array)

Arguments

  • array – Unsigned integer array.

Example

SELECT bitmapBuild([1, 2, 3, 4, 5]) AS res, toTypeName(res);
┌─res─┬─toTypeName(bitmapBuild([1, 2, 3, 4, 5]))─────┐
│     │ AggregateFunction(groupBitmap, UInt8)        │
└─────┴──────────────────────────────────────────────┘

bitmapToArray​

Convert bitmap to integer array.

bitmapToArray(bitmap)

Arguments

  • bitmap – Bitmap object.

Example

SELECT bitmapToArray(bitmapBuild([1, 2, 3, 4, 5])) AS res;
┌─res─────────┐
│ [1,2,3,4,5] │
└─────────────┘

bitmapSubsetInRange​

Return subset in specified range (not include the range_end).

bitmapSubsetInRange(bitmap, range_start, range_end)

Arguments

  • bitmap – Bitmap object.

  • range_start – Range start point. Type: UInt32.

  • range_end – Range end point (excluded). Type: UInt32.

Example

SELECT bitmapToArray(bitmapSubsetInRange(bitmapBuild([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500]), toUInt32(30), toUInt32(200))) AS res;
┌─res───────────────┐
│ [30,31,32,33,100] │
└───────────────────┘

bitmapSubsetLimit​

Creates a subset of bitmap with n elements taken between range_start and cardinality_limit.

Syntax

bitmapSubsetLimit(bitmap, range_start, cardinality_limit)

Arguments

  • bitmap – Bitmap object.

  • range_start – The subset starting point. Type: UInt32.

  • cardinality_limit – The subset cardinality upper limit. Type: UInt32.

Returned value

The subset.

Type: Bitmap object.

Example

Query:

SELECT bitmapToArray(bitmapSubsetLimit(bitmapBuild([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500]), toUInt32(30), toUInt32(200))) AS res;

Result:

┌─res───────────────────────┐
│ [30,31,32,33,100,200,500] │
└───────────────────────────┘

subBitmap​

Returns the bitmap elements, starting from the offset position. The number of returned elements is limited by the cardinality_limit parameter. Analog of the substring) string function, but for bitmap.

Syntax

subBitmap(bitmap, offset, cardinality_limit)

Arguments

  • bitmap – The bitmap. Type: Bitmap object.

  • offset – The position of the first element of the subset. Type: UInt32.

  • cardinality_limit – The maximum number of elements in the subset. Type: UInt32.

Returned value

The subset.

Type: Bitmap object.

Example

Query:

SELECT bitmapToArray(subBitmap(bitmapBuild([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500]), toUInt32(10), toUInt32(10))) AS res;

Result:

┌─res─────────────────────────────┐
│ [10,11,12,13,14,15,16,17,18,19] │
└─────────────────────────────────┘