MaxCompute:HLL_COUNT_EXTRACT

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

Parameters

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

Return value

Returns the cardinality estimation value as a BIGINT. If the input sketch is NULL, this function returns 0.

Examples

Query the number of distinct customers in each country who have at least one invoice.

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

The query returns the following result:

+---------+--------------------------------------+
| country | distinct_customers_with_open_invoice |
+---------+--------------------------------------+
| BR      | 1                                    |
| CZ      | 1                                    |
| UA      | 2                                    |
+---------+--------------------------------------+

Related functions

HLL_COUNT_EXTRACT is one of several HyperLogLog++ functions that MaxCompute provides for approximate aggregation. For more information, see HyperLogLog++ Functions Overview.