Categories:

Aggregate functions (Cardinality Estimation) , Window function syntax and usage

HLL_ESTIMATE

Returns the cardinality estimate for the given HyperLogLog state.

A HyperLogLog state produced by HLL_ACCUMULATE and HLL_COMBINE can be used to compute a cardinality estimate using the HLL_ESTIMATE function.

Thus, HLL_ESTIMATE(HLL_ACCUMULATE(…)) is equivalent to HLL(…).

See also:

HLL , HLL_ACCUMULATE , HLL_COMBINE

Syntax

HLL_ESTIMATE( <state> )
Copy

Arguments

state

An expression that contains state information generated by a call to HLL_ACCUMULATE or HLL_COMBINE.

Examples

This example shows how to use the three related functions HLL_ACCUMULATE, HLL_ESTIMATE, and HLL_COMBINE.

Create a simple table and data:

-- Create a sequence to use to generate values for the table.
CREATE OR REPLACE SEQUENCE seq92;
CREATE OR REPLACE TABLE sequence_demo (c1 INTEGER DEFAULT seq92.nextval, dummy SMALLINT);
INSERT INTO sequence_demo (dummy) VALUES (0);

-- Double the number of rows a few times, until there are 8 rows:
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
Copy

Create a table that contains the “state” that represents the current approximate cardinality information for the table named sequence_demo:

CREATE OR REPLACE TABLE resultstate1 AS (
     SELECT hll_accumulate(c1) AS rs1
        FROM sequence_demo);
Copy

Now create a second table and add data. (In a more realistic situation, the user could have loaded more data into the first table and divided the data into non-overlapping sets based on the time that the data was loaded.)

CREATE OR REPLACE TABLE test_table2 (c1 INTEGER);
-- Insert data.
INSERT INTO test_table2 (c1) SELECT c1 + 4 FROM sequence_demo;
Copy

Get the “state” information for just the new data.

CREATE OR REPLACE TABLE resultstate2 AS 
  (SELECT hll_accumulate(c1) AS rs1 
     FROM test_table2);
Copy

Combine the “state” information for the two batches of rows:

CREATE OR REPLACE TABLE combined_resultstate (c1) AS 
  SELECT hll_combine(rs1) AS apc1
    FROM (
        SELECT rs1 FROM resultstate1
        UNION ALL
        SELECT rs1 FROM resultstate2
      )
      ;
Copy

Get the approximate cardinality of the combined set of rows:

SELECT hll_estimate(c1) FROM combined_resultstate;
Copy

Output:

+------------------+
| HLL_ESTIMATE(C1) |
|------------------|
|               12 |
+------------------+
Copy
Language: English