MaxCompute:HLL_COUNT_MERGE_PARTIAL

Usage notes

The BINARY data passed to HLL_COUNT_MERGE_PARTIAL, HLL_COUNT_EXTRACT, and HLL_COUNT_MERGE must be generated by HLL_COUNT_INIT. Sketches from other systems or methods are not supported.

Syntax

BINARY HLL_COUNT_MERGE_PARTIAL(BINARY <sketch>)

Parameters

Constraints:

• All input sketches must be initialized from the same data type. Merging sketches built from different types — for example, BIGINT and STRING — returns an error.

• If input sketches have different precisions, the result uses the lowest precision. For example, merging sketches with precisions 14 and 15 produces a new sketch at precision 14.

Return value

Returns a new BINARY sketch. If the input is NULL, NULL is returned.

Example

The following query generates a per-country HLL++ sketch in a subquery, then merges all country-level sketches into a single sketch representing distinct customers with at least one invoice.

SELECT HLL_COUNT_MERGE_PARTIAL(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 |
+--------------------------------------+
| =02=01=0F=00=03=00=00=00=20s=8E=00=F0=8B=DD=00=98_$=03 |
+--------------------------------------+

The result is a BINARY sketch, not a numeric count. Pass it to HLL_COUNT_EXTRACT to get the approximate cardinality, or store it for further merging.

Related functions

HLL_COUNT_MERGE_PARTIAL is a HyperLogLog++ function. MaxCompute provides a series of HyperLogLog++ functions for approximate aggregation. For more information, see HyperLogLog++ functions.