HLL_ADD_AGG()

Aggregate function that builds a HyperLogLog (HLL) sketch from column values, which can later be consumed by HLL_CARDINALITY to estimate the number of distinct values.

Description

HLL_ADD_AGG() is an aggregate function that computes a HyperLogLog sketch over a set of values. The result is a binary BLOB representation of the HLL sketch, which can be stored in a table or passed directly to HLL_CARDINALITY() for an approximate distinct-count estimate.

HyperLogLog is a probabilistic cardinality estimation algorithm that uses significantly less memory than an exact COUNT(DISTINCT ...). The trade-off is a small, tunable error rate (typically around 1-2%).

HLL_ADD_AGG() ignores NULL values. If all values in the aggregation are NULL, the result is an empty sketch that evaluates to cardinality 0.

Syntax

> HLL_ADD_AGG(expr)

Arguments

Arguments	Description
expr	Required. A column or expression whose distinct values are to be estimated. Accepts any comparable data type.

Returned Value

Returns a BLOB containing the binary HyperLogLog sketch. An empty input set or an input set containing only NULL values produces an empty sketch whose cardinality is 0.

Examples

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

-- Build an HLL sketch over user IDs
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);

-- Estimate distinct user_ids across all rows
SELECT HLL_CARDINALITY(HLL_ADD_AGG(user_id)) FROM hll_01;

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

DROP TABLE hll_01;
DROP DATABASE dbfuncs;