ReferenceSQL ReferenceFunctions

Tuple

Tuple functions reference.

dotProduct

Calculates the dot product (scalar product) of two vectors (tuples or arrays of equal size). Returns the sum of the products of the corresponding elements.

Syntax

dotProduct(vector1, vector2)

Arguments

Returned value

Returns the dot product of the two vectors. Two BFloat16 arrays accumulate in and return Float32. (U)Int* or Float* or Decimal

Examples

Basic usage

SELECT dotProduct((1, 2), (3, 4))
11

Introduced in version 21.11.0.

flattenTuple

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

Introduced in version 22.6.0.

tuple

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

tuple([t1[, t2[ ...]])

Examples

typical

SELECT tuple(1, 2)
(1,2)

Introduced in version 1.1.0.

tupleConcat

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)

Introduced in version 23.8.0.

tupleDivide

Calculates the element-wise division of two or more tuples of the same size, applied left-to-right.

:::note Division by zero will return inf. :::

Syntax

tupleDivide(t1, t2[, tN, ...])

Arguments

Returned value

Returns a tuple containing the element-wise quotients. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

Two tuples

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

Three tuples

SELECT tupleDivide((100.0, 60.0), (5.0, 3.0), (2.0, 4.0))
(10, 5)

Introduced in version 21.11.0.

tupleDivideByNumber

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)

Introduced in version 21.11.0.

tupleElement

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

Negative indexes are supported. In this case, the corresponding element is selected, numbered from the end. For example, tuple.-1 is the last element in the tuple.

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

Negative indexing

SELECT tupleElement((1, 'hello'), -1)
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

Introduced in version 1.1.0.

tupleHammingDistance

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 'RawTree is a column-oriented database management system for online analytical processing of queries.' AS string)
2

Introduced in version 21.1.0.

tupleIntDiv

Performs element-wise integer division of two or more tuples of the same size, applied left-to-right. Returns a tuple of quotients. If any tuple contains non-integer elements, the result is calculated by rounding to the nearest integer for each non-integer numerator or divisor. Division by 0 causes an exception to be thrown.

Syntax

tupleIntDiv(t1, t2[, tN, ...])

Arguments

Returned value

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

Examples

Two tuples

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)

Three tuples

SELECT tupleIntDiv((120, 60), (4, 3), (2, 4))
(15, 5)

Introduced in version 23.8.0.

tupleIntDivByNumber

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)

Introduced in version 23.8.0.

tupleIntDivOrZero

Like tupleIntDiv, performs element-wise integer division of two or more tuples of the same size, applied left-to-right. In case of division by 0, returns 0 for that element instead of throwing an exception. If any tuple contains non-integer elements, the result is calculated by rounding to the nearest integer for each non-integer numerator or divisor.

Syntax

tupleIntDivOrZero(t1, t2[, tN, ...])

Arguments

Returned value

Returns a tuple of integer quotients, with 0 for any element 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)

Three tuples

SELECT tupleIntDivOrZero((120, 60), (4, 3), (2, 4))
(15, 5)

Introduced in version 23.8.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 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)

Introduced in version 23.8.0.

tupleMinus

Calculates the element-wise difference of two or more tuples of the same size, applied left-to-right.

Syntax

tupleMinus(t1, t2[, tN, ...])

Arguments

Returned value

Returns a tuple containing the element-wise differences. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

Two tuples

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

Three tuples

SELECT tupleMinus((10, 10), (3, 4), (2, 1))
(5, 5)

Introduced in version 21.11.0.

tupleModulo

Returns a tuple of element-wise remainders from dividing two or more tuples of the same size, applied left-to-right.

Syntax

tupleModulo(t1, t2[, tN, ...])

Arguments

Returned value

Returns a tuple of element-wise remainders. An exception is thrown for division by zero. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

Two tuples

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

Three tuples

SELECT tupleModulo((10, 20), (7, 9), (3, 5))
(0, 2)

Introduced in version 23.8.0.

tupleModuloByNumber

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)

Introduced in version 23.8.0.

tupleMultiply

Calculates the element-wise product of two or more tuples of the same size.

Syntax

tupleMultiply(t1, t2[, tN, ...])

Arguments

Returned value

Returns a tuple containing the element-wise products. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

Two tuples

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

Three tuples

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

Introduced in version 21.11.0.

tupleMultiplyByNumber

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)

Introduced in version 21.11.0.

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)

Examples

typical

SELECT tupleNames(tuple(1 as a, 2 as b))
['a','b']

Introduced in version 24.8.0.

tupleNegate

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)

Introduced in version 21.11.0.

tuplePlus

Calculates the element-wise sum of two or more tuples of the same size.

Syntax

tuplePlus(t1, t2[, tN, ...])

Arguments

Returned value

Returns a tuple containing the element-wise sums. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

Two tuples

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

Three tuples

SELECT tuplePlus((1, 2), (2, 3), (3, 4))
(6, 9)

Introduced in version 21.11.0.

tuplePositiveModuloByNumber

Returns a tuple of the positive moduli (remainders) of division operations of a tuple and a given divisor. Unlike tupleModuloByNumber, the result is always non-negative.

Syntax

tuplePositiveModuloByNumber(tuple_num, div)

Arguments

Returned value

Returns a tuple of the non-negative remainders. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Examples

Basic usage

SELECT tuplePositiveModuloByNumber((15, 10, 5), 2)
(1, 0, 1)

Introduced in version 26.4.0.

tupleToNameValuePairs

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)]

Introduced in version 21.9.0.

Time Window

Time Window functions reference.

Type Conversion

Type Conversion functions reference.

On this page

dotProductflattenTupletupletupleConcattupleDividetupleDivideByNumbertupleElementtupleHammingDistancetupleIntDivtupleIntDivByNumbertupleIntDivOrZerotupleIntDivOrZeroByNumbertupleMinustupleModulotupleModuloByNumbertupleMultiplytupleMultiplyByNumbertupleNamestupleNegatetuplePlustuplePositiveModuloByNumbertupleToNameValuePairs