HLL_MERGE_AGG()¶

Aggregate function that merges multiple HyperLogLog (HLL) sketches into a single combined sketch. This enables hierarchical approximate distinct-counting where pre-aggregated sketches from different partitions or time windows can be merged.

Description¶

HLL_MERGE_AGG() is an aggregate function that takes a column of HLL sketches (typically produced by HLL_ADD_AGG()) and merges them into a single combined sketch. The merged sketch can then be queried with HLL_CARDINALITY() to estimate the total number of distinct values across all the original source data.

This is useful for hierarchical aggregation: you can pre-aggregate sketches per day, store them in a table, and then merge across days to get weekly or monthly distinct counts without re-scanning the raw data.

If all input sketches are NULL, the result is an empty sketch whose cardinality is 0.

Syntax¶

> HLL_MERGE_AGG(sketch)

Arguments¶

Arguments	Description
sketch	Required. A `BLOB` column containing HLL sketches produced by `HLL_ADD_AGG()`.

Returned Value¶

Returns a BLOB containing the merged HyperLogLog sketch. If all input sketches are NULL, returns an empty sketch whose cardinality is 0.

Examples¶

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

-- Create daily sketches and then merge across days
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);

-- Store daily sketches
CREATE TABLE hll_daily(dt DATE, sketch BLOB);
INSERT INTO hll_daily SELECT dt, HLL_ADD_AGG(user_id) FROM hll_01 GROUP BY dt;

-- Merge all daily sketches
SELECT HLL_CARDINALITY(HLL_MERGE_AGG(sketch)) FROM hll_daily;

-- Merging NULL input produces an empty sketch; HLL_CARDINALITY returns 0
SELECT HLL_CARDINALITY(HLL_MERGE_AGG(NULL));

DROP TABLE hll_daily;
DROP TABLE hll_01;
DROP DATABASE dbfuncs;