# Functions for Working with Tuples

## tupleβ

A function that allows grouping multiple columns. For columns with the types T1, T2, β¦, it returns a Tuple(T1, T2, β¦) type tuple containing these columns. 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. βNβ is the column index, starting from 1. βNβ must be a constant. βNβ must be a strict postive integer no greater than the size of the tuple. There is no cost to execute the function.

The function implements the operator `x.N`.

Syntax

``tupleElement(tuple, n)``

## untupleβ

Performs syntactic substitution of tuple elements in the call location.

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    ββββββββββ΄ββββββββ``

Note: the names are implementation specific and are subject to change. You should not assume specific names of the columns after application of the `untuple`.

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.

Type: The result type is calculed 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

• An array with (name, value) pairs.

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

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 colums 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)]             ββββββββββββββββββββββββββββββββββββββββββ``

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

Type: 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.

Type: 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.

Type: 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.

Type: 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.

Type: 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.

Type: 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.

Type: Tuple.

Example

Query:

``SELECT tupleDivideByNumber((1, 2), 0.5);``

Result:

``ββtupleDivideByNumber((1, 2), 0.5)βββ (2,4)                            βββββββββββββββββββββββββββββββββββββ``

## dotProductβ

Calculates the scalar product of two tuples of the same size.

Syntax

``dotProduct(tuple1, tuple2)``

Alias: `scalarProduct`.

Arguments

• `tuple1` β First tuple. Tuple.
• `tuple2` β Second tuple. Tuple.

Returned value

• Scalar product.

Type: Int/UInt, Float or Decimal.

Example

Query:

``SELECT dotProduct((1, 2), (2, 3));``

Result:

``ββdotProduct((1, 2), (2, 3))βββ                          8 βββββββββββββββββββββββββββββββ``

## L1Normβ

Calculates the sum of absolute values of a tuple.

Syntax

``L1Norm(tuple)``

Alias: `normL1`.

Arguments

• `tuple` β Tuple.

Returned value

Type: UInt, Float or Decimal.

Example

Query:

``SELECT L1Norm((1, 2));``

Result:

``ββL1Norm((1, 2))βββ              3 βββββββββββββββββββ``

## L2Normβ

Calculates the square root of the sum of the squares of the tuple values.

Syntax

``L2Norm(tuple)``

Alias: `normL2`.

Arguments

• `tuple` β Tuple.

Returned value

Type: Float.

Example

Query:

``SELECT L2Norm((1, 2));``

Result:

``ββββL2Norm((1, 2))βββ 2.23606797749979 βββββββββββββββββββββ``

## LinfNormβ

Calculates the maximum of absolute values of a tuple.

Syntax

``LinfNorm(tuple)``

Alias: `normLinf`.

Arguments

• `tuple` β Tuple.

Returned value

• Linf-norm or the maximum absolute value.

Type: Float.

Example

Query:

``SELECT LinfNorm((1, -2));``

Result:

``ββLinfNorm((1, -2))βββ                 2 ββββββββββββββββββββββ``

## LpNormβ

Calculates the root of `p`-th power of the sum of the absolute values of a tuple in the power of `p`.

Syntax

``LpNorm(tuple, p)``

Alias: `normLp`.

Arguments

• `tuple` β Tuple.
• `p` β The power. Possible values: real number in `[1; inf)`. UInt or Float.

Returned value

Type: Float.

Example

Query:

``SELECT LpNorm((1, -2), 2);``

Result:

``ββLpNorm((1, -2), 2)βββ   2.23606797749979 βββββββββββββββββββββββ``

## L1Distanceβ

Calculates the distance between two points (the values of the tuples are the coordinates) in `L1` space (1-norm (taxicab geometry distance)).

Syntax

``L1Distance(tuple1, tuple2)``

Alias: `distanceL1`.

Arguments

• `tuple1` β First tuple. Tuple.
• `tuple1` β Second tuple. Tuple.

Returned value

• 1-norm distance.

Type: Float.

Example

Query:

``SELECT L1Distance((1, 2), (2, 3));``

Result:

``ββL1Distance((1, 2), (2, 3))βββ                          2 βββββββββββββββββββββββββββββββ``

## L2Distanceβ

Calculates the distance between two points (the values of the tuples are the coordinates) in Euclidean space (Euclidean distance).

Syntax

``L2Distance(tuple1, tuple2)``

Alias: `distanceL2`.

Arguments

• `tuple1` β First tuple. Tuple.
• `tuple1` β Second tuple. Tuple.

Returned value

• 2-norm distance.

Type: Float.

Example

Query:

``SELECT L2Distance((1, 2), (2, 3));``

Result:

``ββL2Distance((1, 2), (2, 3))βββ         1.4142135623730951 βββββββββββββββββββββββββββββββ``

## LinfDistanceβ

Calculates the distance between two points (the values of the tuples are the coordinates) in `L_{inf}` space (maximum norm).

Syntax

``LinfDistance(tuple1, tuple2)``

Alias: `distanceLinf`.

Arguments

• `tuple1` β First tuple. Tuple.
• `tuple1` β Second tuple. Tuple.

Returned value

• Infinity-norm distance.

Type: Float.

Example

Query:

``SELECT LinfDistance((1, 2), (2, 3));``

Result:

``ββLinfDistance((1, 2), (2, 3))βββ                            1 βββββββββββββββββββββββββββββββββ``

## LpDistanceβ

Calculates the distance between two points (the values of the tuples are the coordinates) in `Lp` space (p-norm distance).

Syntax

``LpDistance(tuple1, tuple2, p)``

Alias: `distanceLp`.

Arguments

• `tuple1` β First tuple. Tuple.
• `tuple1` β Second tuple. Tuple.
• `p` β The power. Possible values: real number from `[1; inf)`. UInt or Float.

Returned value

• p-norm distance.

Type: Float.

Example

Query:

``SELECT LpDistance((1, 2), (2, 3), 3);``

Result:

``ββLpDistance((1, 2), (2, 3), 3)βββ            1.2599210498948732 ββββββββββββββββββββββββββββββββββ``

## L1Normalizeβ

Calculates the unit vector of a given vector (the values of the tuple are the coordinates) in `L1` space (taxicab geometry).

Syntax

``L1Normalize(tuple)``

Alias: `normalizeL1`.

Arguments

• `tuple` β Tuple.

Returned value

• Unit vector.

Type: Tuple of Float.

Example

Query:

``SELECT L1Normalize((1, 2));``

Result:

``ββL1Normalize((1, 2))βββββββββββββββββββββββ (0.3333333333333333,0.6666666666666666) ββββββββββββββββββββββββββββββββββββββββββββ``

## L2Normalizeβ

Calculates the unit vector of a given vector (the values of the tuple are the coordinates) in Euclidean space (using Euclidean distance).

Syntax

``L2Normalize(tuple)``

Alias: `normalizeL1`.

Arguments

• `tuple` β Tuple.

Returned value

• Unit vector.

Type: Tuple of Float.

Example

Query:

``SELECT L2Normalize((3, 4));``

Result:

``ββL2Normalize((3, 4))βββ (0.6,0.8)           ββββββββββββββββββββββββ``

## LinfNormalizeβ

Calculates the unit vector of a given vector (the values of the tuple are the coordinates) in `L_{inf}` space (using maximum norm).

Syntax

``LinfNormalize(tuple)``

Alias: `normalizeLinf `.

Arguments

• `tuple` β Tuple.

Returned value

• Unit vector.

Type: Tuple of Float.

Example

Query:

``SELECT LinfNormalize((3, 4));``

Result:

``ββLinfNormalize((3, 4))βββ (0.75,1)              ββββββββββββββββββββββββββ``

## LpNormalizeβ

Calculates the unit vector of a given vector (the values of the tuple are the coordinates) in `Lp` space (using p-norm).

Syntax

``LpNormalize(tuple, p)``

Alias: `normalizeLp `.

Arguments

• `tuple` β Tuple.
• `p` β The power. Possible values: any number from [1;inf). UInt or Float.

Returned value

• Unit vector.

Type: Tuple of Float.

Example

Query:

``SELECT LpNormalize((3, 4),5);``

Result:

``ββLpNormalize((3, 4), 5)ββββββββββββββββββββ (0.7187302630182624,0.9583070173576831) ββββββββββββββββββββββββββββββββββββββββββββ``

## cosineDistanceβ

Calculates the cosine distance between two vectors (the values of the tuples are the coordinates). The less the returned value is, the more similar are the vectors.

Syntax

``cosineDistance(tuple1, tuple2)``

Arguments

• `tuple1` β First tuple. Tuple.
• `tuple2` β Second tuple. Tuple.

Returned value

• Cosine of the angle between two vectors substracted from one.

Type: Float.

Example

Query:

``SELECT cosineDistance((1, 2), (2, 3));``

Result:

``ββcosineDistance((1, 2), (2, 3))βββ           0.007722123286332261 βββββββββββββββββββββββββββββββββββ``