HLL_CARDINALITY()¶

Returns the approximate number of distinct values from a HyperLogLog (HLL) sketch produced by HLL_ADD_AGG or HLL_MERGE_AGG. This provides a memory-efficient alternative to exact COUNT(DISTINCT) with a typical error rate of around 1-2%.

Description¶

HLL_CARDINALITY() extracts the approximate cardinality estimate from an HLL sketch. The sketch must be produced by HLL_ADD_AGG() or HLL_MERGE_AGG().

If the sketch argument is NULL, the function returns NULL. Passing a non-HLL-sketch VARBINARY value may produce unexpected results.

Syntax¶

> HLL_CARDINALITY(sketch)

Arguments¶

Arguments	Description
sketch	Required. A `BLOB` containing an HLL sketch produced by `HLL_ADD_AGG()` or `HLL_MERGE_AGG()`.

Returned Value¶

Returns an integer representing the estimated number of distinct values. Returns NULL if the input sketch is NULL.

Examples¶

DROP DATABASE IF EXISTS dbfuncs;
CREATE DATABASE dbfuncs;
USE dbfuncs;

-- Build sketch and get cardinality estimate
CREATE TABLE hll_01(dt DATE, user_id INT);
INSERT INTO hll_01 VALUES ('2026-05-01', 1), ('2026-05-01', 1), ('2026-05-01', 2), ('2026-05-02', 2), ('2026-05-02', 3), ('2026-05-02', NULL);

-- Direct cardinality from sketch
SELECT HLL_CARDINALITY(HLL_ADD_AGG(user_id)) FROM hll_01;

-- Cardinality per day
SELECT dt, HLL_CARDINALITY(HLL_ADD_AGG(user_id)) FROM hll_01 GROUP BY dt ORDER BY dt;

-- NULL sketch returns NULL
SELECT HLL_CARDINALITY(NULL);

DROP TABLE hll_01;
DROP DATABASE dbfuncs;