uniqCombined64 | RawTree

Calculates the approximate number of different argument values. It is the same as uniqCombined, but uses a 64-bit hash for all data types rather than just for the String data type.

This function provides the result deterministically (it does not depend on the query processing order).

:::note Since it uses 64-bit hash for all types, the result does not suffer from very high error for cardinalities significantly larger than UINT_MAX like uniqCombined does, which uses a 32-bit hash for non-String types. :::

Compared to the uniq function, the uniqCombined64 function:

Consumes several times less memory
Calculates with several times higher accuracy

Implementation details

This function calculates a 64-bit hash for all data types for all parameters in the aggregate, then uses it in calculations. It uses a combination of three algorithms: array, hash table, and HyperLogLog with an error correction table:

For a small number of distinct elements, an array is used
When the set size is larger, a hash table is used
For a larger number of elements, HyperLogLog is used, which will occupy a fixed amount of memory

Syntax

uniqCombined64(HLL_precision)(x[, ...])
uniqCombined64(x[, ...])

Parameters

HLL_precision — Optional. The base-2 logarithm of the number of cells in HyperLogLog. The default value is 17, which is effectively 96 KiB of space (2^17 cells, 6 bits each). Range: [12, 20]. UInt8

Arguments

x — A variable number of parameters. Tuple(T) or Array(T) or Date or DateTime or String or (U)Int* or Float* or Decimal

Returned value

Returns a UInt64-type number representing the approximate number of different argument values. UInt64

Examples

Large dataset example

SELECT uniqCombined64(number) FROM numbers(1e10);

┌─uniqCombined64(number)─┐
│             9998568925 │
└────────────────────────┘

Comparison with uniqCombined

-- uniqCombined64 with large dataset
SELECT uniqCombined64(number) FROM numbers(1e10);

-- uniqCombined with same dataset shows poor approximation
SELECT uniqCombined(number) FROM numbers(1e10);

┌─uniqCombined64(number)─┐
│             9998568925 │ -- 10.00 billion
└────────────────────────┘
┌─uniqCombined(number)─┐
│           5545308725 │ -- 5.55 billion
└──────────────────────┘

Introduced in version 20.1.