Approximate aggregates

Counting distinct values and finding medians over billions of rows is slow. BigQuery has a faster, approximate form of each.

How it works

APPROX_COUNT_DISTINCT(x) is the approximate form of COUNT(DISTINCT x). APPROX_QUANTILES(x, n) returns an array of n + 1 boundary values, from the approximate minimum to the approximate maximum, so n = 4 gives the quartiles. Both keep a compact summary of the values instead of every value.

Common mistake

A common slip is to compare APPROX_QUANTILES(x, 4) with a number. It returns an array of five values, so pick one with [OFFSET(2)] for the median.

Worked example

SELECT make, APPROX_COUNT_DISTINCT(vehicle_id) AS vehicles
FROM `learn.mot.fact_mot_test`
WHERE completed_date BETWEEN '2024-03-01' AND '2024-03-31'
GROUP BY make
ORDER BY vehicles DESC, make
LIMIT 5

Each make gets an estimate of the vehicles tested in March 2024.

The estimate is cheap to compute even when a make has a very long list of vehicles.

All BigQuery lessons