APPROX_COUNT_DISTINCT

The APPROX_COUNT_DISTINCT function counts the approximate number of unique items in a field.

Syntax

APPROX_COUNT_DISTINCT(value)

Parameters

  • value - a field or expression that contains the items to be counted.

How the APPROX_COUNT_DISTINCT function works

The APPROX_COUNT_DISTINCT function takes 1 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 data set 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.

APPROX_COUNT_DISTINCT Example

APPROX_COUNT_DISTINCT(Page) - counts the approximate number of unique values in the Page dimension.

Limits of APPROX_COUNT_DISTINCT

  • The APPROX_COUNT_DISTINCT function is only available when used with BigQuery and Cloud Spanner data sources.
  • For data sources which do not support APPROX_COUNT_DISTINCT, APPROX_COUNT_DISTINCT will act like COUNT_DISTINCT.
  • You can't apply this function to a pre-aggregated metric (Aggregation type of Auto), or to an expression which 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.
Was this helpful?
How can we improve it?
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
102097
false