HLL_COUNT_MERGE merges one or more HyperLogLog++ (HLL++) sketches into a single sketch and returns the cardinality estimate of the merged result.
Use this function to aggregate sketches computed separately—for example, combining per-partition sketches into a single distinct count, or accumulating daily sketches into a weekly total. This is similar to computing a weekly total by summing daily subtotals: build HLL++ sketches per time window or partition using HLL_COUNT_INIT, store them, and then merge them with HLL_COUNT_MERGE to get the combined cardinality.
Usage notes
HLL_COUNT_MERGEaccepts only BINARY sketches generated by HLL_COUNT_INIT. Sketches from other systems or methods cannot be used. The same requirement applies to HLL_COUNT_EXTRACT and HLL_COUNT_MERGE_PARTIAL.All input sketches must be initialized with the same data type. Merging sketches derived from different types—for example, a BIGINT-based sketch with a STRING-based sketch—causes an error.
Syntax
BIGINT HLL_COUNT_MERGE(BINARY <sketch>)Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
sketch | BINARY | Yes | An HLL++ sketch generated by HLL_COUNT_INIT. If sketch is null, it is ignored. |
Return value
Returns the estimated cardinality as a BIGINT value. Returns 0 if the merged sketch is null.
Examples
Count unique customers across all countries
The following example counts the number of unique customers who have at least one invoice, across all countries. The inner query groups rows by country and builds an HLL++ sketch for each group. The outer query merges all sketches into one and returns the estimated total distinct customer count.
SELECT HLL_COUNT_MERGE(hll_sketch) AS distinct_customers_with_open_invoice
FROM
(
SELECT country, HLL_COUNT_INIT(customer_id) AS hll_sketch
FROM values
('UA', 'customer_id_1', 'invoice_id_11'),
('BR', 'customer_id_3', 'invoice_id_31'),
('CZ', 'customer_id_2', 'invoice_id_22'),
('CZ', 'customer_id_2', 'invoice_id_23'),
('BR', 'customer_id_3', 'invoice_id_31'),
('UA', 'customer_id_2', 'invoice_id_24')
t(country, customer_id, invoice_id)
GROUP BY country
);Result:
+--------------------------------------+
| distinct_customers_with_open_invoice |
+--------------------------------------+
| 3 |
+--------------------------------------+Related functions
HLL_COUNT_MERGE is part of the HyperLogLog++ function family in MaxCompute. For all HLL++ functions, see HyperLogLog++ functions.