HLL_COUNT_MERGE
The HLL_COUNT_MERGE function accepts one or more HLL++ data structures (sketches), merges the sketches that have the same storage class into a new sketch, and returns the cardinality estimate of the resulting sketch.
Notes
HLL_COUNT_EXTRACT/HLL_COUNT_MERGE/HLL_COUNT_MERGE_PARTIAL The BINARY data used by the functions must come from theHLL_COUNT_INIT function, and cannot come from other systems or other methods.
Syntax
BIGINT HLL_COUNT_MERGE(BINARY <sketch>)Parameters
sketch: Required. The HLL++ sketch, which is a BINARY value generated by the HLL_COUNT_INIT function.
If sketch is null, it is ignored.
All input sketch values must be initialized with the same data type. An error occurs if you merge sketches with different data types. For example, you cannot merge a sketch generated from BIGINT data with a sketch generated from STRING data.
Return value
Returns the estimated cardinality as a BIGINT value. If the merged sketch is null, the function returns 0.
Example
Count the number of unique customers who have at least one invoice across all countries.
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
);The following result is returned:
+--------------------------------------+
| distinct_customers_with_open_invoice |
+--------------------------------------+
| 3 |
+--------------------------------------+Related functions
HLL_COUNT_MERGE is a HyperLogLog++ function. MaxCompute provides a series of HyperLogLog++ functions for approximate aggregation. For more information, see HyperLogLog++ Functions Overview.