APPROX_COUNT_DISTINCT
The APPROX_COUNT_DISTINCT function counts the approximate number of unique items in a field.
Syntax
APPROX_COUNT_DISTINCT(X)
Parameters
field_expression- a field or expression that contains the items to be counted.
How the APPROX_COUNT_DISTINCT function works
The APPROX_COUNT_DISTINCT function takes one parameter, which can be the name of a metric, dimension, or expression of any type. APPROX_COUNT_DISTINCT returns the approximate number of unique items in that field or expression.
APPROX_COUNT_DISTINCT is more efficient in terms of query processing than COUNT_DISTINCT, but returns less exact results. If your dataset is very large, or if the performance of your report is more important than exact counts, consider using APPROX_COUNT_DISTINCT. Using APPROX_COUNT_DISTINCT instead of COUNT_DISTINCT can also help reduce query costs when using BigQuery data sources.
For an in-depth explanation of how approximate aggregation works, see the BigQuery documentation.
Example
APPROX_COUNT_DISTINCT(Page)- counts the approximate number of unique values in the Page dimension.
Limits of APPROX_COUNT_DISTINCT
The
APPROX_COUNT_DISTINCTfunction is available only when used with BigQuery data sources.For data sources which don't support
APPROX_COUNT_DISTINCT,APPROX_COUNT_DISTINCTwill act likeCOUNT_DISTINCT.You can't apply this function to a pre-aggregated metric ( Aggregation type of Auto ), or to an expression that is the result of another aggregation function. For example, a formula such as
APPROX_COUNT_DISTINCT(Sessions)in a Google Analytics data source will produce an error.