Approximate aggregate functions

GoogleSQL for SecOps supports approximate aggregate functions. To learn about the syntax for aggregate function calls, see Aggregate function calls.

Approximate aggregate functions are scalable in terms of memory usage and time, but produce approximate results instead of exact results. These functions typically require less memory than exact aggregation functions like COUNT(DISTINCT ...), but also introduce statistical uncertainty. This makes approximate aggregation appropriate for large data streams for which linear memory usage is impractical, as well as for data that is already approximate.

The approximate aggregate functions in this section work directly on the input data, rather than an intermediate estimation of the data. These functions don't allow users to specify the precision for the estimation with sketches.

To specify precision with sketches, use HyperLogLog++ functions to estimate cardinality.

Function list

Name Summary
APPROX_COUNT_DISTINCT Gets the approximate result for COUNT(DISTINCT expression).

APPROX_COUNT_DISTINCT

APPROX_COUNT_DISTINCT(
  expression
)

Description

Returns the approximate result for COUNT(DISTINCT expression). The value returned is a statistical estimate, not necessarily the actual value.

This function is less accurate than COUNT(DISTINCT expression), but performs better on very large input.

Supported Argument Types

Any data type except:

  • ARRAY
  • STRUCT

Returned Data Types

INT64

Examples

SELECT APPROX_COUNT_DISTINCT(x) as approx_distinct
FROM UNNEST([0, 1, 1, 2, 3, 5]) as x;

/*-----------------+
 | approx_distinct |
 +-----------------+
 | 5               |
 +-----------------*/