approx_count ↗

Use the approx_count() function to calculate an approximate count of distinct values in a specified field.

Syntax

approx_count(<field>)

Parameters

Name Type Required Description
field string Yes The name of the field for which you want to count distinct values.

Returns

The approx_count() function returns a single approximate integer value representing the number of distinct values found in the specified field.

Usage notes

  • The approx_count() function is an approximate aggregate function. The function is designed to be more scalable in terms of memory usage and processing time compared to exact aggregate functions like count_distinct(), especially for large datasets.
  • This function must be used with the comp stage.
  • You can use the by clause in the comp stage to partition the data into groups, allowing approx_count() to compute the approximate distinct count independently for each group.
  • You can use the addrawdata = true option in the comp stage to include a column listing the raw data events that contributed to the aggregate result.

Examples

Example 1: Calculate approximate distinct count across the entire dataset

Goal: Calculate the approximate number of unique event_description values for all records in the dataset.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| comp approx_count(event_description) as unique_event_descriptions

Explanation: This query computes the approximate number of unique event_description values present in the sample_xql_raw dataset and names the resulting field unique_event_descriptions.

Output:

unique_event_descriptions
10

Example 2: Calculate approximate distinct count grouped by another field

Goal: Calculate the approximate number of unique dst_domain values separately for successful and unsuccessful events.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| comp approx_count(dst_domain) as unique_domains_by_status by is_successful

Explanation: The query groups records by their is_successful status and then calculates the approximate number of unique dst_domain values for each group, presenting the counts in the unique_domains_by_status field.

Output:

is_successful unique_domains_by_status
true 7
false 3

Example 3: Calculate approximate distinct count with raw data inclusion

Goal: Calculate the approximate distinct count of event_description grouped by is_successful and include the raw events that contributed to the calculation.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| comp approx_count(event_description) as unique_descriptions_by_status by is_successful addrawdata = true as raw_events_for_distinct_count

Explanation: This query calculates the approximate distinct count of event_description grouped by is_successful. Additionally, addrawdata = true generates a new column named raw_events_for_distinct_count, which contains a JSON representation of the raw events that contributed to each computed approximate distinct count.

Output:

is_successful unique_descriptions_by_status raw_events_for_distinct_count
true 7 [{"event_id": 101, "event_description": "User login successful", ...}, {"event_id": 103, "event_description": "Network connection established", ...}, ...]
false 3 [{"event_id": 102, "event_description": "File access attempt", ...}, {"event_id": 106, "event_description": "Unauthorized access detected", ...}, ...]