MaxCompute:HLL_COUNT_MERGE_PARTIAL

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_MERGE_PARTIAL(BINARY <sketch>)

Parameters

sketch: Required. A BINARY value that represents an HLL++ sketch generated by the HLL_COUNT_INIT function.

• All input sketch values must be initialized with the same data type. An error is returned if you attempt to merge sketches of different types. For example, you cannot merge sketches generated from BIGINT data with sketches generated from STRING data.

• If you merge sketch objects with different precisions, the precision is downgraded to the lowest precision among the merged sketches. For example, if you merge sketch objects with precisions of 14 and 15, a new sketch with a precision of 14 is returned.

Return value

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

Example

The following query returns an HLL++ sketch that counts the number of distinct users who have at least one invoice across all countries.

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
  );

The following result is returned:

+--------------------------------------+
| distinct_customers_with_open_invoice |
+--------------------------------------+
| =02=01=0F=00=03=00=00=00=20s=8E=00=F0=8B=DD=00=98_$=03 |
+--------------------------------------+

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 Overview.