Open in ChatGPTOpen in Claude

approx_count_distinct

Description

Returns an approximate count of unique, non-null values for one or more expressions.

• Accepts any number of expressions, of any type.
• Given a single expression, counts its unique, non-null values.
• Given several expressions, counts the unique combinations of their values. A document is counted if at least one of the expressions is non-null.
• The result is approximate, optimized for performance rather than precision.

Note

approx_count_distinct is an aggregation function and must be used with a grouping keyword such as groupby.

Syntax

approx_count_distinct(expression: any, ...values: any): number

Arguments

Name	Type	Required	Description
expression	any	true	Expression whose unique, non-null values are counted approximately
...values	any	false	Additional expressions, of any type, counted together with expression as unique combinations

Example 1

Use case: Estimate the number of active users per application

Suppose you want to know how many unique users have been active in a given timeframe. Since you only care whether the number is above a threshold, an approximate count is sufficient.

Example data

{ "applicationname": "checkout-service", "username": "alice" },
{ "applicationname": "checkout-service", "username": "bob" },
{ "applicationname": "checkout-service", "username": "charlie" },
{ "applicationname": "checkout-service", "username": "alice" }

Example query

groupby $l.applicationname aggregate approx_count_distinct($d.username) as active_users

Example output

applicationname	active_users
checkout-service	3

Example 2

Use case: Estimate the number of distinct endpoint and status code pairs

Pass several expressions to count unique combinations of their values rather than each field separately. The expressions don't have to share a type.

Example data

{ "applicationname": "checkout-service", "endpoint": "/cart", "status": 200 },
{ "applicationname": "checkout-service", "endpoint": "/cart", "status": 500 },
{ "applicationname": "checkout-service", "endpoint": "/pay", "status": 200 },
{ "applicationname": "checkout-service", "endpoint": "/cart", "status": 200 }

Example query

groupby $l.applicationname aggregate approx_count_distinct($d.endpoint, $d.status) as unique_pairs

Example output

applicationname	unique_pairs
checkout-service	3