Aggregate Functions (Cardinality Estimation) , Window Functions
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_ESTIMATE( <state> )
An expression that contains state information generated by a call to HLL_ACCUMULATE or HLL_COMBINE.
This example shows how to use the three related functions
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;
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);
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;
Get the “state” information for just the new data.CREATE OR REPLACE TABLE resultstate2 AS (SELECT hll_accumulate(c1) AS rs1 FROM test_table2);
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 ) ;
Get the approximate cardinality of the combined set of rows:SELECT hll_estimate(c1) FROM combined_resultstate;
Output:+------------------+ | HLL_ESTIMATE(C1) | |------------------| | 12 | +------------------+