avgif
This page explains how to use the avgif aggregation function in APL.
The avgif aggregation function in APL allows you to calculate the average value of a field, but only for records that satisfy a given condition. This function is particularly useful when you need to perform a filtered aggregation, such as finding the average response time for requests that returned a specific status code or filtering by geographic regions. The avgif function is highly valuable in scenarios like log analysis, performance monitoring, and anomaly detection, where focusing on subsets of data can provide more accurate insights.
Usage
Syntax
Parameters
expr: The field for which you want to calculate the average.predicate: A boolean condition that filters which records are included in the calculation.grouping_field: (Optional) A field by which you want to group the results.
Returns
The function returns the average of the values from the expr field for the records that satisfy the predicate. If no records match the condition, the result is null.
Use case examples
In this example, you calculate the average request duration for HTTP status 200 in different cities.
Query
Output
| geo.city | avg_req_duration_ms |
|---|---|
| New York | 325 |
| London | 400 |
| Tokyo | 275 |
This query calculates the average request duration (req_duration_ms) for HTTP requests that returned a status of 200 (status == "200"), grouped by the city where the request originated (geo.city).
In this example, you calculate the average span duration for traces that ended with HTTP status 500.
Query
Output
| service.name | avg_duration |
|---|---|
| checkoutservice | 500ms |
| frontend | 600ms |
| cartservice | 475ms |
This query calculates the average span duration (duration) for traces where the status code is 500 (status == "500"), grouped by the service name (service.name).
In this example, you calculate the average request duration for failed HTTP requests (status code 400 or higher) by country.
Query
Output
| geo.country | avg_req_duration_ms |
|---|---|
| USA | 450 |
| Canada | 500 |
| Germany | 425 |
This query calculates the average request duration (req_duration_ms) for failed HTTP requests (status >= 400), grouped by the country of origin (geo.country).
List of related aggregations
- minif: Returns the minimum value of an expression, filtered by a predicate. Use when you want to find the smallest value for a subset of data.
- maxif: Returns the maximum value of an expression, filtered by a predicate. Use when you are looking for the largest value within specific conditions.
- countif: Counts the number of records that match a condition. Use when you want to know how many records meet a specific criterion.
- sumif: Sums the values of a field that match a given condition. Ideal for calculating the total of a subset of data.