approx_quantiles ↗

Use the approx_quantiles() function to calculate and return approximate boundaries for a specified field, producing an array of values that define the quantiles.

Syntax

approx_quantiles(<field>, <number>, <distinct>)

Parameters

Name Type Required Description
field string, integer, float Yes The name of the field for which to calculate approximate quantiles.
number integer Yes An integer specifying the number of quantiles to compute. The output array will contain <number> + 1 elements.
distinct boolean No Determines whether to consider only distinct values (true) or all values (false). If omitted, the default is false.

Returns

The approx_quantiles() function returns a single array containing <number> + 1 approximate values representing the boundaries of the quantiles. The first element represents the approximate minimum, and the last element represents the approximate maximum.

Usage notes

  • The approx_quantiles() function is an approximate aggregate function designed to produce approximate results, instead of exact results used with regular aggregate functions, which is more scalable in terms of memory usage and time.
  • This function must always be used with the comp stage.
  • You can use the by clause with comp to calculate quantiles independently for different groups.
  • You can use the addrawdata = true option to include a column listing the raw data events that contributed to the calculation. When you include raw data events, the query runs for up to 50 fields that you define and displays up to 100 events.

Examples

Example 1: Calculate approximate quantiles across the entire dataset

Goal: Calculate 3 approximate quantiles (4 boundaries) for the duration_seconds field across all records, considering all values (non-distinct).

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| comp approx_quantiles(duration_seconds, 3) as approx_duration_quantiles

Explanation: This query calculates 3 quantiles for the duration_seconds field. The output is an array containing the approximate minimum, the 25th percentile, the 50th percentile (median), the 75th percentile, and the approximate maximum.

Output:

approx_duration_quantiles
[0.05, 1.0, 3.55, 9.0, 60.0]

Example 2: Calculate approximate quantiles grouped by field

Goal: Calculate 2 approximate quantiles (3 boundaries: min, median, max) for duration_seconds, grouped by the is_successful status.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| comp approx_quantiles(duration_seconds, 2) as approx_duration_quantiles by is_successful

Explanation: The query groups records by is_successful and calculates the approximate boundaries (min, median, max) of the duration_seconds values for each group.

Output:

is_successful approx_duration_quantiles
true [0.1, 7.8, 60.0]
false [0.05, 0.8, 2.1]

Example 3: Calculate approximate quantiles for distinct values

Goal: Calculate 1 approximate quantile (2 boundaries: min, max) for event_description, considering only distinct values.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| comp approx_quantiles(event_description, 1, true) as approx_event_description_range

Explanation: This query sets the third parameter to true, instructing the function to consider only unique event_description values. The query finds the alphabetically first and last descriptions.

Output:

approx_event_description_range
["API request throttled", "User login successful"]