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.
APPROX_QUANTILES(x, 4)[OFFSET(2)]is the approximate median.OFFSET(0)is the minimum andOFFSET(4)the maximum.- The exact median needs the window function
PERCENTILE_CONT(x, 0.5) OVER (), which returns the same value on every row. APPROX_COUNT_DISTINCTaccepts any type exceptARRAY,STRUCTandINTERVAL.
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 5Each 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.