> For the complete documentation index, see [llms.txt](https://cortex-docs.paloaltonetworks.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/functions/avg_with_windowcomp_stage.md).

# avg (windowcomp)

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](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/stages/windowcomp.md), [config](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/stages/config.md), timeframe, [dataset](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/stages/dataset.md), [fields](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/stages/fields.md), [sort](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/stages/sort.md), [limit](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/stages/limit.md)
* **Functions**: [count](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/functions/count_with_windowcomp_stage.md), [max](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/functions/max_with_comp_stage.md), [median](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/functions/median_with_comp_stage.md), [min](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/functions/min_with_comp_stage.md), [sum](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/functions/sum_with_comp_stage.md), [stddev\_population](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/functions/stddev_population_with_comp_stage.md), [stddev\_sample](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/functions/stddev_sample_with_comp_stage.md)


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/functions/avg_with_windowcomp_stage.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
