Skip to main content
Skip to main content
Edit this page

Tuple Functions

tuple​

A function that allows grouping multiple columns. For columns C1, C2, ... with the types T1, T2, ..., it returns Tuple(T1, T2, ...). If setting enable_named_columns_in_function_tuple is enabled then it returns Tuple(C1 T1, C2 T2, ...) containing these columns if their names are unique and can be treated as unquoted identifiers. 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') │
└─────┴────┴────┴────┴───────────┘

See Also

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.
Note

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 'ClickHouse 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

Example

Query:

CREATE TABLE tupletest (col Tuple(user_ID UInt64, session_ID UInt64)) ENGINE = Memory;

INSERT INTO tupletest VALUES (tuple( 100, 2502)), (tuple(1,100));

SELECT tupleToNameValuePairs(col) FROM tupletest;

Result:

┌─tupleToNameValuePairs(col)────────────┐
│ [('user_ID',100),('session_ID',2502)] │
│ [('user_ID',1),('session_ID',100)]    │
└───────────────────────────────────────┘

It is possible to transform columns to rows using this function:

CREATE TABLE tupletest (col Tuple(CPU Float64, Memory Float64, Disk Float64)) ENGINE = Memory;

INSERT INTO tupletest VALUES(tuple(3.3, 5.5, 6.6));

SELECT arrayJoin(tupleToNameValuePairs(col)) FROM tupletest;

Result:

┌─arrayJoin(tupleToNameValuePairs(col))─┐
│ ('CPU',3.3)                           │
│ ('Memory',5.5)                        │
│ ('Disk',6.6)                          │
└───────────────────────────────────────┘

If you pass a simple tuple to the function, ClickHouse uses the indexes of the values as their names:

SELECT tupleToNameValuePairs(tuple(3, 2, 1));

Result:

┌─tupleToNameValuePairs(tuple(3, 2, 1))─┐
│ [('1',3),('2',2),('3',1)]             │
└───────────────────────────────────────┘

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, ...)).

Example

Query:

CREATE TABLE tupletest (col Tuple(user_ID UInt64, session_ID UInt64)) ENGINE = Memory;

INSERT INTO tupletest VALUES (tuple(1, 2));

SELECT tupleNames(col) FROM tupletest;

Result:

┌─tupleNames(col)──────────┐
│ ['user_ID','session_ID'] │
└──────────────────────────┘

If you pass a simple tuple to the function, ClickHouse uses the indexes of the columns as their names:

SELECT tupleNames(tuple(3, 2, 1));

Result:

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

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

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

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

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.

Example

Query:

CREATE TABLE t_flatten_tuple(t Tuple(t1 Nested(a UInt32, s String), b UInt32, t2 Tuple(k String, v UInt32))) ENGINE = Memory;
INSERT INTO t_flatten_tuple VALUES (([(1, 'a'), (2, 'b')], 3, ('c', 4)));
SELECT flattenTuple(t) FROM t_flatten_tuple;

Result:

┌─flattenTuple(t)───────────┐
│ ([1,2],['a','b'],3,'c',4) │
└───────────────────────────┘

Distance functions​

All supported functions are described in distance functions documentation.

flattenTuple​

Introduced in: v22.6

Flattens a named and nested tuple. The elements of the returned tuple are the paths of the input tuple.

Syntax

flattenTuple(input)

Arguments

Returned value

Returns an output tuple whose elements are paths from the original input. Tuple(T)

Examples

Usage example

CREATE TABLE tab(t Tuple(a UInt32, b Tuple(c String, d UInt32))) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO tab VALUES ((3, ('c', 4)));

SELECT flattenTuple(t) FROM tab;

┌─flattenTuple(t)┐
│ (3, 'c', 4)    │
└────────────────┘

tuple​

Introduced in: v

Returns a tuple by grouping input arguments.

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

Arguments

  • None. Returned value

Examples

typical

SELECT tuple(1, 2)

(1,2)

tupleConcat​

Introduced in: v23.8

Combines tuples passed as arguments.

Syntax

tupleConcat(tuple1[, tuple2, [...]])

Arguments

  • tupleN — Arbitrary number of arguments of Tuple type. Tuple(T)

Returned value

Returns a tuple containing all elements from the input tuples. Tuple(T)

Examples

Usage example

SELECT tupleConcat((1, 2), ('a',), (true, false))

(1, 2, 'a', true, false)

tupleDivide​

Introduced in: v21.11

Calculates the division of corresponding elements of two tuples of the same size.

Note

Division by zero will return inf.

Syntax

tupleDivide(t1, t2)

Arguments

Returned value

Returns tuple with the result of division. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

Basic usage

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

(0.5, 0.6666666666666666)

tupleDivideByNumber​

Introduced in: v21.11

Returns a tuple with all elements divided by a number.

Note

Division by zero will return inf.

Syntax

tupleDivideByNumber(tuple, number)

Arguments

Returned value

Returns a tuple with divided elements. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

Basic usage

SELECT tupleDivideByNumber((1, 2), 0.5)

(2, 4)

tupleElement​

Introduced in: v1.1

Extracts an element from a tuple by index or name.

For access by index, an 1-based numeric index is expected. For access by name, the element name can be provided as a string (works only for named tuples).

An optional third argument specifies a default value which is returned instead of throwing an exception when the accessed element does not exist. All arguments must be constants.

This function has zero runtime cost and implements the operators x.index and x.name.

Syntax

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

Arguments

Returned value

Returns the element at the specified index or name. Any

Examples

Index access

SELECT tupleElement((1, 'hello'), 2)

hello

Named tuple with table

CREATE TABLE example (values Tuple(name String, age UInt32)) ENGINE = Memory;
INSERT INTO example VALUES (('Alice', 30));
SELECT tupleElement(values, 'name') FROM example;

Alice

With default value

SELECT tupleElement((1, 2), 5, 'not_found')

not_found

Operator syntax

SELECT (1, 'hello').2

hello

tupleHammingDistance​

Introduced in: v21.1

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

Note

The result type is determined 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 │
└───────┴────────┴────────┴────────┴────────┘

Syntax

tupleHammingDistance(t1, t2)

Arguments

Returned value

Returns the Hamming distance. UInt8/16/32/64

Examples

Usage example

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

2

With MinHash to detect semi-duplicate strings

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

2

tupleIntDiv​

Introduced in: v23.8

Performs an integer division with a tuple of numerators and a tuple of denominators. Returns a tuple of quotients. If either tuple contains non-integer elements then the result is calculated by rounding to the nearest integer for each non-integer numerator or divisor. Division by 0 causes an error to be thrown.

Syntax

tupleIntDiv(tuple_num, tuple_div)

Arguments

Returned value

Returns a tuple of the quotients. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

Basic usage

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

(3, 2, 1)

With decimals

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

(2, 1, 0)

tupleIntDivByNumber​

Introduced in: v23.8

Performs integer division of a tuple of numerators by a given denominator, and returns a tuple of the quotients. If either of the input parameters contain non-integer elements 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.

Syntax

tupleIntDivByNumber(tuple_num, div)

Arguments

Returned value

Returns a tuple of the quotients. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

Basic usage

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

(3, 2, 1)

With decimals

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

(2, 1, 0)

tupleIntDivOrZero​

Introduced in: v23.8

Like tupleIntDiv performs integer division of a tuple of numerators and a tuple of denominators, and returns a tuple of the quotients. In case of division by 0, returns the quotient as 0 instead of throwing an exception. If either tuple contains non-integer elements then the result is calculated by rounding to the nearest integer for each non-integer numerator or divisor.

Syntax

tupleIntDivOrZero(tuple_num, tuple_div)

Arguments

Returned value

Returns tuple of the quotients. Returns 0 for quotients where the divisor is 0. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

With zero divisors

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

(0, 0, 0)

tupleIntDivOrZeroByNumber​

Introduced in: v23.8

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 zero divisors, but rather returns the quotient as zero. If either the tuple or div contain non-integer elements then the result is calculated by rounding to the nearest integer for each non-integer numerator or divisor.

Syntax

tupleIntDivOrZeroByNumber(tuple_num, div)

Arguments

Returned value

Returns a tuple of the quotients with 0 for quotients where the divisor is 0. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

Basic usage

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

(3, 2, 1)

With zero divisor

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

(0, 0, 0)

tupleMinus​

Introduced in: v21.11

Calculates the difference between corresponding elements of two tuples of the same size.

Syntax

tupleMinus(t1, t2)

Arguments

Returned value

Returns a tuple containing the results of the subtractions. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

Basic usage

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

(-1, -1)

tupleModulo​

Introduced in: v23.8

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

Syntax

tupleModulo(tuple_num, tuple_mod)

Arguments

Returned value

Returns tuple of the remainders of division. An error is thrown for division by zero. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

Basic usage

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

(0, 1, 1)

tupleModuloByNumber​

Introduced in: v23.8

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

Syntax

tupleModuloByNumber(tuple_num, div)

Arguments

Returned value

Returns tuple of the remainders of division. An error is thrown for division by zero. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

Basic usage

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

(1, 0, 1)

tupleMultiply​

Introduced in: v21.11

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

Syntax

tupleMultiply(t1, t2)

Arguments

Returned value

Returns a tuple with the results of the multiplications. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

Basic usage

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

(2, 6)

tupleMultiplyByNumber​

Introduced in: v21.11

Returns a tuple with all elements multiplied by a number.

Syntax

tupleMultiplyByNumber(tuple, number)

Arguments

Returned value

Returns a tuple with multiplied elements. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

Basic usage

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

(-2.1, -4.2)

tupleNames​

Introduced in: v

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

Arguments

  • None. Returned value

Examples

typical

SELECT tupleNames(tuple(1 as a, 2 as b))

['a','b']

tupleNegate​

Introduced in: v21.11

Calculates the negation of the tuple elements.

Syntax

tupleNegate(t)

Arguments

Returned value

Returns a tuple with the result of negation. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

Basic usage

SELECT tupleNegate((1, 2))

(-1, -2)

tuplePlus​

Introduced in: v21.11

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

Syntax

tuplePlus(t1, t2)

Arguments

Returned value

Returns a tuple containing the sums of corresponding input tuple arguments. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

Basic usage

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

(3, 5)

tupleToNameValuePairs​

Introduced in: v21.9

Converts a tuple to an array of (name, value) pairs. For example, tuple Tuple(n1 T1, n2 T2, ...) is converted to Array(Tuple('n1', T1), Tuple('n2', T2), ...). All values in the tuple must be of the same type.

Syntax

tupleToNameValuePairs(tuple)

Arguments

Returned value

Returns an array with (name, value) pairs. Array(Tuple(String, T))

Examples

Named tuple

SELECT tupleToNameValuePairs(tuple(1593 AS user_ID, 2502 AS session_ID))

[('1', 1593), ('2', 2502)]

Unnamed tuple

SELECT tupleToNameValuePairs(tuple(3, 2, 1))

[('1', 3), ('2', 2), ('3', 1)]