Tuple functions¶

tuple¶

A function that allows grouping multiple columns. For columns C1, C2, ... with the types T1, T2, ..., it returns a named Tuple(C1 T1, C2 T2, ...) type tuple containing these columns if their names are unique and can be treated as unquoted identifiers, otherwise a Tuple(T1, T2, ...) is returned. There is no cost to execute the function. Tuples are normally used as intermediate values for an argument of IN operators, or for creating a list of formal parameters of lambda functions. Tuples can’t be written to a table.

The function implements the operator (x, y, ...).

Syntax

tuple(x, y, ...)

tupleElement¶

A function that allows getting a column from a tuple.

If the second argument is a number index, it is the column index, starting from 1. If the second argument is a string name, it represents the name of the element. Besides, we can provide the third optional argument, such that when index out of bounds or no element exist for the name, the default value returned instead of throwing an exception. The second and third arguments, if provided, must be constants. There is no cost to execute the function.

The function implements operators x.index and x.name.

Syntax

tupleElement(tuple, index, [, default_value])
tupleElement(tuple, name, [, default_value])

untuple¶

Performs syntactic substitution of tuple elements in the call location.

The names of the result columns are implementation-specific and subject to change. Do not assume specific column names after untuple.

Syntax

untuple(x)

You can use the EXCEPT expression to skip columns as a result of the query.

Arguments

x: A tuple function, column, or tuple of elements. Tuple.

Returned value

None.

Examples

Input table:

┌─key─┬─v1─┬─v2─┬─v3─┬─v4─┬─v5─┬─v6────────┐
│   1 │ 10 │ 20 │ 40 │ 30 │ 15 │ (33,'ab') │
│   2 │ 25 │ 65 │ 70 │ 40 │  6 │ (44,'cd') │
│   3 │ 57 │ 30 │ 20 │ 10 │  5 │ (55,'ef') │
│   4 │ 55 │ 12 │  7 │ 80 │ 90 │ (66,'gh') │
│   5 │ 30 │ 50 │ 70 │ 25 │ 55 │ (77,'kl') │
└─────┴────┴────┴────┴────┴────┴───────────┘

Example of using a Tuple-type column as the untuple function parameter:

Query:

SELECT untuple(v6) FROM kv

Result:

┌─_ut_1─┬─_ut_2─┐
│    33 │ ab    │
│    44 │ cd    │
│    55 │ ef    │
│    66 │ gh    │
│    77 │ kl    │
└───────┴───────┘

Example of using an EXCEPT expression:

Query:

SELECT untuple((* EXCEPT (v2, v3),)) FROM kv

Result:

┌─key─┬─v1─┬─v4─┬─v5─┬─v6────────┐
│   1 │ 10 │ 30 │ 15 │ (33,'ab') │
│   2 │ 25 │ 40 │  6 │ (44,'cd') │
│   3 │ 57 │ 10 │  5 │ (55,'ef') │
│   4 │ 55 │ 80 │ 90 │ (66,'gh') │
│   5 │ 30 │ 25 │ 55 │ (77,'kl') │
└─────┴────┴────┴────┴───────────┘

tupleHammingDistance¶

Returns the Hamming Distance between two tuples of the same size.

Syntax

tupleHammingDistance(tuple1, tuple2)

Arguments

tuple1: First tuple. Tuple.
tuple2: Second tuple. Tuple.

Tuples should have the same type of the elements.

Returned value

The Hamming distance.

The result type is calculated the same way it is for Arithmetic functions, based on the number of elements in the input tuples.

SELECT
    toTypeName(tupleHammingDistance(tuple(0), tuple(0))) AS t1,
    toTypeName(tupleHammingDistance((0, 0), (0, 0))) AS t2,
    toTypeName(tupleHammingDistance((0, 0, 0), (0, 0, 0))) AS t3,
    toTypeName(tupleHammingDistance((0, 0, 0, 0), (0, 0, 0, 0))) AS t4,
    toTypeName(tupleHammingDistance((0, 0, 0, 0, 0), (0, 0, 0, 0, 0))) AS t5

┌─t1────┬─t2─────┬─t3─────┬─t4─────┬─t5─────┐
│ UInt8 │ UInt16 │ UInt32 │ UInt64 │ UInt64 │
└───────┴────────┴────────┴────────┴────────┘

Examples

Query:

SELECT tupleHammingDistance((1, 2, 3), (3, 2, 1)) AS HammingDistance

Result:

┌─HammingDistance─┐
│               2 │
└─────────────────┘

Can be used with MinHash functions for detection of semi-duplicate strings:

SELECT tupleHammingDistance(wordShingleMinHash(string), wordShingleMinHashCaseInsensitive(string)) AS HammingDistance
FROM (SELECT 'Bazinga is a column-oriented database management system for online analytical processing of queries.' AS string)

Result:

┌─HammingDistance─┐
│               2 │
└─────────────────┘

tupleToNameValuePairs¶

Turns a named tuple into an array of (name, value) pairs. For a Tuple(a T, b T, ..., c T) returns Array(Tuple(String, T), ...) in which the Strings represents the named fields of the tuple and T are the values associated with those names. All values in the tuple should be of the same type.

Syntax

tupleToNameValuePairs(tuple)

Arguments

tuple: Named tuple. Tuple with any types of values.

Returned value

An array with (name, value) pairs. Array(Tuple(String, ...)).

tupleNames¶

Converts a tuple into an array of column names. For a tuple in the form Tuple(a T, b T, ...), it returns an array of strings representing the named columns of the tuple. If the tuple elements do not have explicit names, their indices will be used as the column names instead.

Syntax

tupleNames(tuple)

Arguments

tuple: Named tuple. Tuple with any types of values.

Returned value

An array with strings.

Type: Array(Tuple(String, ...)).

tuplePlus¶

Calculates the sum of corresponding values of two tuples of the same size.

Syntax

tuplePlus(tuple1, tuple2)

Alias: vectorSum.

Arguments

tuple1: First tuple. Tuple.
tuple2: Second tuple. Tuple.

Returned value

Tuple with the sum. Tuple.

Example

Query:

SELECT tuplePlus((1, 2), (2, 3))

Result:

┌─tuplePlus((1, 2), (2, 3))─┐
│ (3,5)                     │
└───────────────────────────┘

tupleMinus¶

Calculates the subtraction of corresponding values of two tuples of the same size.

Syntax

tupleMinus(tuple1, tuple2)

Alias: vectorDifference.

Arguments

tuple1: First tuple. Tuple.
tuple2: Second tuple. Tuple.

Returned value

Tuple with the result of subtraction. Tuple.

Example

Query:

SELECT tupleMinus((1, 2), (2, 3))

Result:

┌─tupleMinus((1, 2), (2, 3))─┐
│ (-1,-1)                    │
└────────────────────────────┘

tupleMultiply¶

Calculates the multiplication of corresponding values of two tuples of the same size.

Syntax

tupleMultiply(tuple1, tuple2)

Arguments

tuple1: First tuple. Tuple.
tuple2: Second tuple. Tuple.

Returned value

Tuple with the multiplication. Tuple.

Example

Query:

SELECT tupleMultiply((1, 2), (2, 3))

Result:

┌─tupleMultiply((1, 2), (2, 3))─┐
│ (2,6)                         │
└───────────────────────────────┘

tupleDivide¶

Calculates the division of corresponding values of two tuples of the same size. Note that division by zero will return inf.

Syntax

tupleDivide(tuple1, tuple2)

Arguments

tuple1: First tuple. Tuple.
tuple2: Second tuple. Tuple.

Returned value

Tuple with the result of division. Tuple.

Example

Query:

SELECT tupleDivide((1, 2), (2, 3))

Result:

┌─tupleDivide((1, 2), (2, 3))─┐
│ (0.5,0.6666666666666666)    │
└─────────────────────────────┘

tupleNegate¶

Calculates the negation of the tuple values.

Syntax

tupleNegate(tuple)

Arguments

tuple: Tuple.

Returned value

Tuple with the result of negation. Tuple.

Example

Query:

SELECT tupleNegate((1,  2))

Result:

┌─tupleNegate((1, 2))─┐
│ (-1,-2)             │
└─────────────────────┘

tupleMultiplyByNumber¶

Returns a tuple with all values multiplied by a number.

Syntax

tupleMultiplyByNumber(tuple, number)

Arguments

tuple: Tuple.
number: Multiplier. Int/UInt, Float or Decimal.

Returned value

Tuple with multiplied values. Tuple.

Example

Query:

SELECT tupleMultiplyByNumber((1, 2), -2.1)

Result:

┌─tupleMultiplyByNumber((1, 2), -2.1)─┐
│ (-2.1,-4.2)                         │
└─────────────────────────────────────┘

tupleDivideByNumber¶

Returns a tuple with all values divided by a number. Note that division by zero will return inf.

Syntax

tupleDivideByNumber(tuple, number)

Arguments

tuple: Tuple.
number: Divider. Int/UInt, Float or Decimal.

Returned value

Tuple with divided values. Tuple.

Example

Query:

SELECT tupleDivideByNumber((1, 2), 0.5)

Result:

┌─tupleDivideByNumber((1, 2), 0.5)─┐
│ (2,4)                            │
└──────────────────────────────────┘

tupleConcat¶

Combines tuples passed as arguments.

tupleConcat(tuples)

Arguments

tuples – Arbitrary number of arguments of Tuple type.

Example

SELECT tupleConcat((1, 2), (3, 4), (true, false)) AS res

┌─res──────────────────┐
│ (1,2,3,4,true,false) │
└──────────────────────┘

tupleIntDiv¶

Does integer division of a tuple of numerators and a tuple of denominators, and returns a tuple of the quotients.

Syntax

tupleIntDiv(tuple_num, tuple_div)

### Parameters

tuple_num: Tuple of numerator values. Tuple of numeric type.
tuple_div: Tuple of divisor values. Tuple of numeric type.

Returned value

Tuple of the quotients of tuple_num and tuple_div. Tuple of integer values.

Implementation details

If either tuple_num or tuple_div contain non-integer values then the result is calculated by rounding to the nearest integer for each non-integer numerator or divisor.
An error will be thrown for division by 0.

Examples

Query:

SELECT tupleIntDiv((15, 10, 5), (5, 5, 5))

Result:

┌─tupleIntDiv((15, 10, 5), (5, 5, 5))─┐
│ (3,2,1)                             │
└─────────────────────────────────────┘

Query:

SELECT tupleIntDiv((15, 10, 5), (5.5, 5.5, 5.5))

Result:

┌─tupleIntDiv((15, 10, 5), (5.5, 5.5, 5.5))─┐
│ (2,1,0)                                   │
└───────────────────────────────────────────┘

tupleIntDivOrZero¶

Like tupleIntDiv it does integer division of a tuple of numerators and a tuple of denominators, and returns a tuple of the quotients. It does not throw an error for 0 divisors, but rather returns the quotient as 0.

Syntax

tupleIntDivOrZero(tuple_num, tuple_div)

tuple_num: Tuple of numerator values. Tuple of numeric type.
tuple_div: Tuple of divisor values. Tuple of numeric type.

Returned value

Tuple of the quotients of tuple_num and tuple_div. Tuple of integer values.
Returns 0 for quotients where the divisor is 0.

Implementation details

If either tuple_num or tuple_div contain non-integer values then the result is calculated by rounding to the nearest integer for each non-integer numerator or divisor as in tupleIntDiv.

Examples

Query:

SELECT tupleIntDivOrZero((5, 10, 15), (0, 0, 0))

Result:

┌─tupleIntDivOrZero((5, 10, 15), (0, 0, 0))─┐
│ (0,0,0)                                   │
└───────────────────────────────────────────┘

tupleIntDivByNumber¶

Does integer division of a tuple of numerators by a given denominator, and returns a tuple of the quotients.

Syntax

tupleIntDivByNumber(tuple_num, div)

### Parameters

tuple_num: Tuple of numerator values. Tuple of numeric type.
div: The divisor value. Numeric type.

Returned value

Tuple of the quotients of tuple_num and div. Tuple of integer values.

Implementation details

If either tuple_num or div contain non-integer values then the result is calculated by rounding to the nearest integer for each non-integer numerator or divisor.
An error will be thrown for division by 0.

Examples

Query:

SELECT tupleIntDivByNumber((15, 10, 5), 5)

Result:

┌─tupleIntDivByNumber((15, 10, 5), 5)─┐
│ (3,2,1)                             │
└─────────────────────────────────────┘

Query:

SELECT tupleIntDivByNumber((15.2, 10.7, 5.5), 5.8)

Result:

┌─tupleIntDivByNumber((15.2, 10.7, 5.5), 5.8)─┐
│ (2,1,0)                                     │
└─────────────────────────────────────────────┘

tupleIntDivOrZeroByNumber¶

Like tupleIntDivByNumber it does integer division of a tuple of numerators by a given denominator, and returns a tuple of the quotients. It does not throw an error for 0 divisors, but rather returns the quotient as 0.

Syntax

tupleIntDivOrZeroByNumber(tuple_num, div)

### Parameters

tuple_num: Tuple of numerator values. Tuple of numeric type.
div: The divisor value. Numeric type.

Returned value

Tuple of the quotients of tuple_num and div. Tuple of integer values.
Returns 0 for quotients where the divisor is 0.

Implementation details

If either tuple_num or div contain non-integer values then the result is calculated by rounding to the nearest integer for each non-integer numerator or divisor as in tupleIntDivByNumber.

Examples

Query:

SELECT tupleIntDivOrZeroByNumber((15, 10, 5), 5)

Result:

┌─tupleIntDivOrZeroByNumber((15, 10, 5), 5)─┐
│ (3,2,1)                                   │
└───────────────────────────────────────────┘

Query:

SELECT tupleIntDivOrZeroByNumber((15, 10, 5), 0)

Result:

┌─tupleIntDivOrZeroByNumber((15, 10, 5), 0)─┐
│ (0,0,0)                                   │
└───────────────────────────────────────────┘

tupleModulo¶

Returns a tuple of the moduli (remainders) of division operations of two tuples.

Syntax

tupleModulo(tuple_num, tuple_mod)

### Parameters

tuple_num: Tuple of numerator values. Tuple of numeric type.
tuple_div: Tuple of modulus values. Tuple of numeric type.

Returned value

Tuple of the remainders of division of tuple_num and tuple_div. Tuple of non-zero integer values.
An error is thrown for division by zero.

Examples

Query:

SELECT tupleModulo((15, 10, 5), (5, 3, 2))

Result:

┌─tupleModulo((15, 10, 5), (5, 3, 2))─┐
│ (0,1,1)                             │
└─────────────────────────────────────┘

tupleModuloByNumber¶

Returns a tuple of the moduli (remainders) of division operations of a tuple and a given divisor.

Syntax

tupleModuloByNumber(tuple_num, div)

### Parameters

tuple_num: Tuple of numerator values. Tuple of numeric type.
div: The divisor value. Numeric type.

Returned value

Tuple of the remainders of division of tuple_num and div. Tuple of non-zero integer values.
An error is thrown for division by zero.

Examples

Query:

SELECT tupleModuloByNumber((15, 10, 5), 2)

Result:

┌─tupleModuloByNumber((15, 10, 5), 2)─┐
│ (1,0,1)                             │
└─────────────────────────────────────┘

flattenTuple¶

Returns a flattened output tuple from a nested named input tuple. Elements of the output tuple are the paths from the original input tuple. For instance: Tuple(a Int, Tuple(b Int, c Int)) -> Tuple(a Int, b Int, c Int). flattenTuple can be used to select all paths from type Object as separate columns.

Syntax

flattenTuple(input)

### Parameters

input: Nested named tuple to flatten. Tuple.

Returned value

output tuple whose elements are paths from the original input. Tuple.

Distance functions¶

All supported functions are described in distance functions documentation.

Data Types

Table engines

Functions

Tuple functions¶

tuple¶

tupleElement¶

untuple¶

tupleHammingDistance¶

tupleToNameValuePairs¶

tupleNames¶

tuplePlus¶

tupleMinus¶

tupleMultiply¶

tupleDivide¶

tupleNegate¶

tupleMultiplyByNumber¶

tupleDivideByNumber¶

tupleConcat¶

tupleIntDiv¶

tupleIntDivOrZero¶

tupleIntDivByNumber¶

tupleIntDivOrZeroByNumber¶

tupleModulo¶

tupleModuloByNumber¶

flattenTuple¶

Distance functions¶