uniqTheta

Introduced in: v21.6

Calculates the approximate number of different argument values, using the Theta Sketch Framework.

Implementation details

This function calculates a hash for all parameters in the aggregate, then uses it in calculations. It uses the KMV algorithm to approximate the number of different argument values.

4096(2^12) 64-bit sketch are used. The size of the state is about 41 KB.

The relative error is 3.125% (95% confidence), see the relative error table for detail.

Syntax

uniqTheta(x[, ...])

Arguments

x — The function takes 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

Basic usage

CREATE TABLE example_theta
(
    id UInt32,
    category String
)
ENGINE = Memory;

INSERT INTO example_theta VALUES
(1, 'A'), (2, 'B'), (3, 'A'), (4, 'C'), (5, 'B'), (6, 'A');

SELECT uniqTheta(category) as theta_unique_categories
FROM example_theta;

┌─theta_unique_categories─┐
│                       3 │
└─────────────────────────┘

See Also

uniqTheta​

uniqTheta