Getting a Distinct Count Estimate (Method 1)

Calculating the distinct count of a large column can be time-consuming and memory-intensive. To reduce the cost of this calculation, you run an alternative function that gets an estimate of family_id’s distinct count:

SELECT APPROX_COUNT_DISTINCT(family_id) AS result FROM passenger_booking;

+--------+
| result |
+--------+
| 49933  |
+--------+

APPROX_COUNT_DISTINCT is implemented using the HyperLogLog algorithm.

As shown in the following table, family_id’s estimate calculation runs faster and consumes less memory, as compared to the exact calculation.

Getting a Distinct Count Estimate (Method 2)

Suppose that every day, you want to estimate the number of unique family_ids for flights booked up to and including that date. Rather than calling APPROX_COUNT_DISTINCT to recalculate the count for all family_id values in the passenger_booking table, you call the functions APPROX_COUNT_DISTINCT_ACCUMULATE and APPROX_COUNT_DISTINCT_ESTIMATE. These are low-level functions that allow you to obtain incremental estimates, consuming less time and memory than APPROX_COUNT_DISTINCT.

In general, you work with the low-level functions as follows: You call APPROX_COUNT_DISTINCT_ACCUMULATE multiple times. On each invocation, you provide a data subset to summarize into a state. You then call APPROX_COUNT_DISTINCT_ESTIMATE to provide an estimate from the collection of input states.

The following code snippets illustrate how to use the low-level functions to generate incremental family_id estimates.

First, create a state table that the low-level functions will use.

CREATE TABLE distinct_family_day_states (flight_date DATE,
  state VARBINARY(16384));

At the end of the first day, create a state for that day and insert it into the table containing the states:

INSERT INTO distinct_family_day_states(flight_date, state)
  SELECT flight_date, APPROX_COUNT_DISTINCT_ACCUMULATE(family_id)
  FROM passenger_booking
  WHERE flight_date = TO_DATE('01/01/2018', 'MM/DD/YYYY')
  GROUP BY flight_date;

Then get the distinct family estimate for the first day:

SELECT APPROX_COUNT_DISTINCT_ESTIMATE(state)
  FROM distinct_family_day_states;

+----------------------------------------+
| APPROX_COUNT_DISTINCT_ESTIMATE(state)  |
+----------------------------------------+
|                                  10009 |
+----------------------------------------+  

At the end of the second day, create a state for that day and insert it into the table containing the states:

INSERT INTO distinct_family_day_states(flight_date, state)
  SELECT flight_date, APPROX_COUNT_DISTINCT_ACCUMULATE(family_id)
  FROM passenger_booking
  WHERE flight_date = TO_DATE('01/02/2018', 'MM/DD/YYYY')
  GROUP BY flight_date;

Then get the distinct family estimate for the first and second day:

SELECT APPROX_COUNT_DISTINCT_ESTIMATE(state)
  FROM distinct_family_day_states;

+----------------------------------------+
| APPROX_COUNT_DISTINCT_ESTIMATE(state)  |
+----------------------------------------+
|                                  18227 |
+----------------------------------------+

To find the distinct family estimate for the third and fourth day, repeat the steps above. The results are shown in the table below.

Getting a Distinct Count Estimate (Method 3): Using APPROX_COUNT_DISTINCT_COMBINE

In the previous section, you found a daily rolling estimate of the number of families who booked flights. Imagine that you want to make this estimate every day for multiple years. You can optimize this calculation by taking the following steps, in order, at the end of every month:

• Call APPROX_COUNT_DISTINCT_COMBINE to merge the daily states into a monthly state. Insert the monthly state into a monthly states table. The code below shows how to create the monthly state for January 2018. Note that a WHERE clause is not needed in the SELECT statement; the daily states table contains data for one month only.

INSERT INTO distinct_family_month_states
  SELECT 1, 2018, APPROX_COUNT_DISTINCT_COMBINE(state)
  FROM distinct_family_day_states;

• Truncate the distinct_family_day_states table. Prior to truncating, the table should not contain daily states for any other month.

At the end of each day, insert the states from the distinct_family_day_states and distinct_family_month_states tables into a new temporary result set. Find the distinct family count estimate by running APPROX_COUNT_DISTINCT_ESTIMATE on the states in the result set:

WITH all_states AS (
  SELECT state FROM distinct_family_day_states
  UNION ALL
  SELECT state FROM distinct_family_month_states
)
SELECT APPROX_COUNT_DISTINCT_ESTIMATE(state) FROM all_states;