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

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

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

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

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.