Use the avg() function within the `windowcomp` stage to calculate and return a single average value of a specified numerical field over a defined "window" or group of rows. The resulting average value is then presented for each row within that window frame, allowing for contextual statistical analysis without collapsing rows.
## Syntax
```sql
windowcomp avg(<field>) [by <field> [,<field>,...]] [sort [asc|desc] <field1> [, [asc|desc] <field2>,...]] [between 0|null|<number>|-<number> [and 0|null|<number>|-<number>] [frame_type=range]] [as <alias>]
```
## Parameters
| Name | Type | Required | Description |
| -------------- | ------- | -------- | ------------------------------------------------------------------------------------------------------------------------------- |
| field | numeric | Yes | The numerical field for which the average will be calculated. |
| by field | string | No | Partitions the data into distinct groups, allowing the avg() function to operate independently within each of these partitions. |
| sort field | string | No | Defines the order of rows within each partition. This ordering is critical when defining a window using the between clause. |
| between clause | string | No | Defines the "window frame"—a specific range of rows relative to the current row—over which the avg() function calculates. |
| alias | string | No | Assigns an alias name to the new column displaying the result. |
## Returns
The avg() function returns a single numerical value representing the average for the defined window. This value is displayed in a new column for every row in the group of rows it applies to.
## Usage Notes
* The windowcomp stage must always precede analytic functions like avg() that calculate statistics.
* Only one function can be defined per field within a single windowcomp stage.
* If the between clause is omitted, the default window typically encompasses the entire partition.
## Examples
### Example 1: avg() over an entire partition (default window)
**Goal**: Calculate the average of duration\_seconds for all events within each is\_successful group (partition), and replicate this single average value for every row in that group.
**XQL Code**:
```sql
config timeframe = 1d
| dataset = sample_xql_raw
| fields event_id, _time, is_successful, duration_seconds
| windowcomp avg(duration_seconds) by is_successful as avg_in_partition
| sort asc is_successful, asc _time, asc event_id
| limit 10
```
**Explanation**: The `by is_successful` clause partitions the data into two groups: one for false and one for true. The avg() function then calculates the average of the duration\_seconds field for each partition. The result is a single average value for each partition, which is then displayed for every row in that partition. The sort clause ensures the rows are ordered by is\_successful,\_time, and event\_id for consistent presentation of the data.
**Output**:
| \_time | event\_id | is\_successful | duration\_seconds | avg\_in\_partition |
| ----------------------- | --------- | -------------- | ----------------- | ------------------ |
| 2023-10-26 10:05:30 UTC | 102 | false | 0.8 | 0.9833 |
| 2023-10-26 10:40:10 UTC | 106 | false | 2.1 | 0.9833 |
| 2023-10-26 10:55:55 UTC | 109 | false | 0.05 | 0.9833 |
| 2023-10-26 10:00:00 UTC | 101 | true | 1.5 | 14.2714 |
| 2023-10-26 10:15:15 UTC | 103 | true | 10.2 | 14.2714 |
| 2023-10-26 10:20:00 UTC | 104 | true | 0.1 | 14.2714 |
| 2023-10-26 10:30:45 UTC | 105 | true | 5.0 | 14.2714 |
| 2023-10-26 10:45:00 UTC | 107 | true | 7.8 | 14.2714 |
| 2023-10-26 10:50:20 UTC | 108 | true | 15.3 | 14.2714 |
| 2023-10-26 11:00:10 UTC | 110 | true | 60.0 | 14.2714 |
### Example 2: avg() with a rolling window (preceding 2 rows and current row)\*\*
**Goal**: Calculate a rolling average from the duration\_seconds of the current row and the two immediately preceding rows within its partition, ordered by \_time.
**XQL Code**:
```sql
config timeframe = 1d
| dataset = sample_xql_raw
| fields event_id, _time, is_successful, duration_seconds
| windowcomp avg(duration_seconds) by is_successful sort asc _time between -2 and 0 as rolling_avg
| sort asc is_successful, asc _time, asc event_id
| limit 10
```
**Explanation**: The code defines a rolling window using between -2 and 0 which includes the current row (0) and the two immediately preceding rows (-2, -1) within its partition. For each row, the avg() function calculates the average of duration\_seconds strictly within this rolling window. If there are not enough preceding rows, the window starts from the beginning of the partition.
**Output**:
| \_time | event\_id | is\_successful | duration\_seconds | rolling\_avg |
| ----------------------- | --------- | -------------- | ----------------- | ------------ |
| 2023-10-26 10:05:30 UTC | 102 | false | 0.8 | 0.8 |
| 2023-10-26 10:40:10 UTC | 106 | false | 2.1 | 1.45 |
| 2023-10-26 10:55:55 UTC | 109 | false | 0.05 | 0.9833 |
| 2023-10-26 10:00:00 UTC | 101 | true | 1.5 | 1.5 |
| 2023-10-26 10:15:15 UTC | 103 | true | 10.2 | 5.85 |
| 2023-10-26 10:20:00 UTC | 104 | true | 0.1 | 3.9333 |
| 2023-10-26 10:30:45 UTC | 105 | true | 5.0 | 5.1 |
| 2023-10-26 10:45:00 UTC | 107 | true | 7.8 | 4.3 |
| 2023-10-26 10:50:20 UTC | 108 | true | 15.3 | 9.3667 |
| 2023-10-26 11:00:10 UTC | 110 | true | 60.0 | 27.7 |
### Example 3: avg() with a fixed window (current row to unbounded following row)
**Goal**: Calculate the average for each row over a window that starts from the current row and extends to the end of its partition.
**XQL Code**:
```sql
config timeframe = 1d
| dataset = sample_xql_raw
| fields event_id, _time, is_successful, duration_seconds
| windowcomp avg(duration_seconds) by is_successful sort asc _time between 0 and null as avg_from_current
| sort asc is_successful, asc _time, asc event_id
| limit 10
```
**Explanation**: The code uses the `between 0 and null` clause to set a window starting from the current row (0) through to the very end of the partition (null). For each row, the avg() calculates the average of duration\_seconds utilizing the current row's value up to the last value in its is\_successful partition.
**Output**:
| \_time | event\_id | is\_successful | duration\_seconds | avg\_from\_current |
| ----------------------- | --------- | -------------- | ----------------- | ------------------ |
| 2023-10-26 10:05:30 UTC | 102 | false | 0.8 | 0.9833 |
| 2023-10-26 10:40:10 UTC | 106 | false | 2.1 | 1.075 |
| 2023-10-26 10:55:55 UTC | 109 | false | 0.05 | 0.05 |
| 2023-10-26 10:00:00 UTC | 101 | true | 1.5 | 14.2714 |
| 2023-10-26 10:15:15 UTC | 103 | true | 10.2 | 16.4 |
| 2023-10-26 10:20:00 UTC | 104 | true | 0.1 | 17.64 |
| 2023-10-26 10:30:45 UTC | 105 | true | 5.0 | 22.025 |
| 2023-10-26 10:45:00 UTC | 107 | true | 7.8 | 27.7 |
| 2023-10-26 10:50:20 UTC | 108 | true | 15.3 | 37.65 |
| 2023-10-26 11:00:10 UTC | 110 | true | 60.0 | 60.0 |
## Related Articles
* **Stages**: [windowcomp](../stages/windowcomp), [config](../stages/config), timeframe, [dataset](../stages/dataset), [fields](../stages/fields), [sort](../stages/sort), [limit](../stages/limit)
* **Functions**: [count](count_with_windowcomp_stage), [max](max_with_comp_stage), [median](median_with_comp_stage), [min](min_with_comp_stage), [sum](sum_with_comp_stage), [stddev\_population](stddev_population_with_comp_stage), [stddev\_sample](stddev_sample_with_comp_stage)