The HLL_COUNT_INIT function aggregates values of the same type into a new HyperLogLog++ (HLL++) data structure (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
BINARY HLL_COUNT_INIT(<col_name> [, BIGINT <precision>])Parameters
col_name: Required. The name of the column. The column must be of the BIGINT, DECIMAL, STRING, or BINARY data type.
precision: Optional. The estimated precision. The value must be a BIGINT integer in the range [10, 24]. The default value is 15. A higher value provides greater precision but requires more storage space for the sketch.
Return value
Returns a BINARY HLL++ sketch. If the input col_name is NULL, the return value is NULL.
To merge sketches, use HLL_COUNT_MERGE or HLL_COUNT_MERGE_PARTIAL.
To retrieve the number of distinct values, use HLL_COUNT_EXTRACT.
Scenarios
The following query creates HLL++ sketches to calculate the number of distinct individuals invoiced in each country.
SELECT
country,
HLL_COUNT_INIT(customer_id, 10) 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 results are returned:
+---------+------------+
| country | hll_sketch |
+---------+------------+
| BR | =02=01=0A=00=01=00=00=00=20s=8E=00 |
| CZ | =02=01=0A=00=01=00=00=00=98_$=03 |
| UA | =02=01=0A=00=02=00=00=00=F0=8B=DD=00=98_$=03 |
+---------+------------+Related functions
HLL_COUNT_INIT is an HLL++ function. MaxCompute supports a series of HLL++ functions for approximate aggregation. For more information, see HyperLogLog++ Functions Overview.