Extracts the estimated distinct count from an HLL++ sketch stored as a BINARY value.
Usage notes
The BINARY data consumed by HLL_COUNT_EXTRACT, HLL_COUNT_MERGE, and HLL_COUNT_MERGE_PARTIAL must come from HLL_COUNT_INIT. Sketches produced by other systems or methods are not compatible.
Syntax
BIGINT HLL_COUNT_EXTRACT(BINARY <sketch>)Parameters
| Parameter | Required | Type | Description |
|---|---|---|---|
sketch |
Yes | BINARY | An HLL++ sketch generated by HLL_COUNT_INIT. |
Return value
Returns a BIGINT cardinality estimate. If sketch is NULL, the function returns 0, not NULL.
Examples
The following example counts distinct customers per country who have at least one invoice. It builds a sketch per country using HLL_COUNT_INIT in a subquery, then reads the cardinality estimate with HLL_COUNT_EXTRACT.
SELECT
country,
HLL_COUNT_EXTRACT(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:
+---------+--------------------------------------+
| country | distinct_customers_with_open_invoice |
+---------+--------------------------------------+
| BR | 1 |
| CZ | 1 |
| UA | 2 |
+---------+--------------------------------------+Related functions
HLL_COUNT_EXTRACT is part of the HyperLogLog++ (HLL++) functions that MaxCompute provides for approximate aggregation. For the full function family, see HyperLogLog++ functions.