Tuple functions - ClickHouse Documentation

The documentation below is generated from the system.functions system table.

dotProduct

Introduced in: v21.11.0 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)

Aliases: scalarProduct Arguments

vector1 — First vector. Array(T) or Tuple(T)
vector2 — Second vector. Must be the same size as the first vector. Array(T) or Tuple(T)

Returned value Returns the dot product of the two vectors. (U)Int* or Float* or Decimal Examples Basic usage

Query

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

Response

flattenTuple

Introduced in: v22.6.0 Flattens a named and nested tuple. The elements of the returned tuple are the paths of the input tuple. Syntax

flattenTuple(input)

Arguments

input — Named and nested tuple to flatten. Tuple(n1 T1[, n2 T2, ... ])

Returned value Returns an output tuple whose elements are paths from the original input. Tuple(T) Examples Usage example

Query

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;

Response

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

tuple

Introduced in: v1.1.0 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[ ...]])

Arguments

None.

Returned value Examples typical

Query

SELECT tuple(1, 2)

Response

(1,2)

tupleConcat

Introduced in: v23.8.0 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

Query

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

Response

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

tupleDivide

Introduced in: v21.11.0 Calculates the element-wise division of two or more tuples of the same size, applied left-to-right.

Division by zero will return inf.

Syntax

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

Arguments

t1 — First input tuple. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)
t2, ..., tN — One or more further input tuples. All tuples must have the same size. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Returned value Returns a tuple containing the element-wise quotients. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Two tuples

Query

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

Response

(0.5, 0.6666666666666666)

Three tuples

Query

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

Response

(10, 5)

tupleDivideByNumber

Introduced in: v21.11.0 Returns a tuple with all elements divided by a number.

Division by zero will return inf.

Syntax

tupleDivideByNumber(tuple, number)

Arguments

tuple — Tuple to divide. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)
number — Divider. (U)Int* or Float* or Decimal

Returned value Returns a tuple with divided elements. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Basic usage

Query

SELECT tupleDivideByNumber((1, 2), 0.5)

Response

(2, 4)

tupleElement

Introduced in: v1.1.0 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

tuple — A tuple or array of tuples. Tuple(T) or Array(Tuple(T))
index — Column index, starting from 1. const UInt8/16/32/64
name — Name of the element. const String
default_value — Default value returned when index is out of bounds or element doesn’t exist. Any

Returned value Returns the element at the specified index or name. Any Examples Index access

Query

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

Response

hello

Negative indexing

Query

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

Response

hello

Named tuple with table

Query

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

Response

Alice

With default value

Query

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

Response

not_found

Operator syntax

Query

SELECT (1, 'hello').2

Response

hello

tupleHammingDistance

Introduced in: v21.1.0 Returns the Hamming Distance between two tuples of the same size.

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

t1 — First tuple. Tuple(*)
t2 — Second tuple. Tuple(*)

Returned value Returns the Hamming distance. UInt8/16/32/64 Examples Usage example

Query

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

Response

With MinHash to detect semi-duplicate strings

Query

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

Response

tupleIntDiv

Introduced in: v23.8.0 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

t1 — First input tuple. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)
t2, ..., tN — One or more further input tuples. All tuples must have the same size. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Returned value Returns a tuple of integer quotients. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Two tuples

Query

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

Response

(3, 2, 1)

With decimals

Query

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

Response

(2, 1, 0)

Three tuples

Query

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

Response

(15, 5)

tupleIntDivByNumber

Introduced in: v23.8.0 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

tuple_num — Tuple of numerator values. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)
div — The divisor value. (U)Int* or Float* or Decimal

Returned value Returns a tuple of the quotients. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Basic usage

Query

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

Response

(3, 2, 1)

With decimals

Query

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

Response

(2, 1, 0)

tupleIntDivOrZero

Introduced in: v23.8.0 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

t1 — First input tuple. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)
t2, ..., tN — One or more further input tuples. All tuples must have the same size. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

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

Query

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

Response

(0, 0, 0)

Three tuples

Query

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

Response

(15, 5)

tupleIntDivOrZeroByNumber

Introduced in: v23.8.0 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

tuple_num — Tuple of numerator values. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)
div — The divisor value. (U)Int* or Float* or Decimal

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

Query

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

Response

(3, 2, 1)

With zero divisor

Query

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

Response

(0, 0, 0)

tupleMinus

Introduced in: v21.11.0 Calculates the element-wise difference of two or more tuples of the same size, applied left-to-right. Syntax

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

Aliases: vectorDifference Arguments

t1 — First input tuple. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)
t2, ..., tN — One or more further input tuples. All tuples must have the same size. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Returned value Returns a tuple containing the element-wise differences. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Two tuples

Query

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

Response

(-1, -1)

Three tuples

Query

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

Response

(5, 5)

tupleModulo

Introduced in: v23.8.0 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

t1 — First input tuple. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)
t2, ..., tN — One or more further input tuples. All tuples must have the same size. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

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

Query

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

Response

(0, 1, 1)

Three tuples

Query

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

Response

(0, 2)

tupleModuloByNumber

Introduced in: v23.8.0 Returns a tuple of the moduli (remainders) of division operations of a tuple and a given divisor. Syntax

tupleModuloByNumber(tuple_num, div)

Arguments

tuple_num — Tuple of numerator elements. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)
div — The divisor value. (U)Int* or Float* or Decimal

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

Query

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

Response

(1, 0, 1)

tupleMultiply

Introduced in: v21.11.0 Calculates the element-wise product of two or more tuples of the same size. Syntax

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

Arguments

t1 — First input tuple. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)
t2, ..., tN — One or more further input tuples. All tuples must have the same size. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Returned value Returns a tuple containing the element-wise products. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Two tuples

Query

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

Response

(2, 6)

Three tuples

Query

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

Response

(2, 12)

tupleMultiplyByNumber

Introduced in: v21.11.0 Returns a tuple with all elements multiplied by a number. Syntax

tupleMultiplyByNumber(tuple, number)

Arguments

tuple — Tuple to multiply. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)
number — Multiplier. (U)Int* or Float* or Decimal

Returned value Returns a tuple with multiplied elements. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Basic usage

Query

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

Response

(-2.1, -4.2)

tupleNames

Introduced in: v24.8.0 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

None.

Returned value Examples typical

Query

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

Response

['a','b']

tupleNegate

Introduced in: v21.11.0 Calculates the negation of the tuple elements. Syntax

tupleNegate(t)

Arguments

t — Tuple to negate. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Returned value Returns a tuple with the result of negation. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Basic usage

Query

SELECT tupleNegate((1, 2))

Response

(-1, -2)

tuplePlus

Introduced in: v21.11.0 Calculates the element-wise sum of two or more tuples of the same size. Syntax

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

Aliases: vectorSum Arguments

t1 — First input tuple. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)
t2, ..., tN — One or more further input tuples. All tuples must have the same size. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)

Returned value Returns a tuple containing the element-wise sums. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Two tuples

Query

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

Response

(3, 5)

Three tuples

Query

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

Response

(6, 9)

tuplePositiveModuloByNumber

Introduced in: v26.4.0 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

tuple_num — Tuple of numerator values. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal)
div — The divisor value. (U)Int* or Float* or Decimal

Returned value Returns a tuple of the non-negative remainders. Tuple((U)Int*) or Tuple(Float*) or Tuple(Decimal) Examples Basic usage

Query

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

Response

(1, 0, 1)

tupleToNameValuePairs

Introduced in: v21.9.0 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

tuple — Named tuple with any types of values. Tuple(n1 T1[, n2 T2, ...])

Returned value Returns an array with (name, value) pairs. Array(Tuple(String, T)) Examples Named tuple

Query

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

Response

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

Unnamed tuple

Query

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

Response

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

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;

Response

┌─_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;

Response

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

Distance functions

All supported functions are described in distance functions documentation.

​dotProduct

​flattenTuple

​tuple

​tupleConcat

​tupleDivide

​tupleDivideByNumber

​tupleElement

​tupleHammingDistance

​tupleIntDiv

​tupleIntDivByNumber

​tupleIntDivOrZero

​tupleIntDivOrZeroByNumber

​tupleMinus

​tupleModulo

​tupleModuloByNumber

​tupleMultiply

​tupleMultiplyByNumber

​tupleNames

​tupleNegate

​tuplePlus

​tuplePositiveModuloByNumber

​tupleToNameValuePairs

​untuple

​Distance functions

dotProduct

flattenTuple

tuple

tupleConcat

tupleDivide

tupleDivideByNumber

tupleElement

tupleHammingDistance

tupleIntDiv

tupleIntDivByNumber

tupleIntDivOrZero

tupleIntDivOrZeroByNumber

tupleMinus

tupleModulo

tupleModuloByNumber

tupleMultiply

tupleMultiplyByNumber

tupleNames

tupleNegate

tuplePlus

tuplePositiveModuloByNumber

tupleToNameValuePairs

untuple

Distance functions