> For the complete documentation index, see [llms.txt](https://docs.duql.dev/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.duql.dev/advanced/window.md).

# Windows

## DUQL Window Function

The window function in DUQL is a powerful tool for performing calculations across a set of rows that are related to the current row. It allows for complex analytics and comparisons within specified ranges or groups of data.

Window applies a step pipeline to segments of rows, producing one output value for every input value.

### Syntax

```yaml
window:
  <window_type>: <value>
  <steps>
```

### Parameters

For each row, the segment over which the pipeline is applied is determined by one of:

| Parameter   | Type              | Description                                                      |
| ----------- | ----------------- | ---------------------------------------------------------------- |
| `rows`      | string or integer | Specifies a range of rows relative to the current row position.  |
| `range`     | string            | Specifies a range of values relative to the current row value.   |
| `expanding` | boolean           | When true, creates a cumulative window (alias for `rows: ..0`).  |
| `rolling`   | integer           | Specifies a rolling window of n rows, including the current row. |

{% hint style="info" %}
`0` references the current row.
{% endhint %}

{% hint style="success" %}
For ease of use, there are two flags that override `rows` or `range`:

* `expanding:true` is an alias for `rows:..0`. A sum using this window is also known as “cumulative sum”.
* `rolling:n` is an alias for `rows:(-n+1)..0`, where `n` is an integer. This will include `n` last values, including current row. An average using this window is also knows as a Simple Moving Average.
  {% endhint %}

{% hint style="warning" %}
The bounds of the range are inclusive. If a bound is omitted, the segment will extend until the edge of the table or group.
{% endhint %}

### Window Types

#### Row Range

```yaml
window:
  rows: <start>..<end>
```

* Specifies a range of rows relative to the current row position.
* Examples:
  * `"0..2"`: Current row and next two rows
  * `"-2..0"`: Previous two rows and current row
  * `"..0"`: All preceding rows and current row
  * `".."`: All rows in the partition

**Gotcha:** Large row ranges can impact performance on large datasets.

#### Value Range

```yaml
window:
  range: <start>..<end>
```

* Specifies a range of values relative to the current row value.
* Example: `"-1000..1000"`: All rows within 1000 units of the current row's value

**Gotcha:** Range is based on the values in the sorted column, not row numbers.

#### Expanding Window

```yaml
window:
  expanding: true
```

* Creates a cumulative window (alias for `rows: ..0`).
* A sum using this window is also known as “cumulative sum”.
* Useful for running totals or cumulative aggregations.

**Gotcha:** Can lead to performance issues on very large datasets.

#### Rolling Window

```yaml
window:
  rolling: <n>
```

* Specifies a rolling window of n rows, including the current row.
* `rolling:n` is an alias for `rows:(-n+1)..0`, where `n` is an integer.  This will include `n` last values, including current row.&#x20;
* Example: `7` for a 7-day rolling window.
* An average using this window is also knows as a Simple Moving Average.

**Gotcha:** Ensure the window size is appropriate for your data and analysis needs.

### Best Practices

1. 🎯 Choose the appropriate window type for your analysis needs.
2. 🔢 Be mindful of window sizes, especially on large datasets.
3. 📊 Use sorting within window functions to ensure consistent results.
4. 🚀 Consider performance implications, especially with expanding windows or large ranges.
5. 🧪 Test window functions with sample data to verify results.
6. 📈 Combine window functions with other DUQL operations for complex analyses.

### Related Functions

* [`sort`](/basic/sort.md): Often used within window functions to order data.
* [`generate`](/intermediate/generate.md): Used to create new columns based on window calculations.
* [`select`](/basic/select.md): Can be used to choose specific window function results.

### Limitations and Considerations

* Window functions can be computationally expensive, especially on large datasets.
* Not all database systems support all types of window functions. Check your target database's capabilities.
* Complex window functions may require careful optimization for performance.

***

> 💡 **Tip:** Window functions are powerful tools for time-series analysis, rankings, and running calculations. They allow you to perform complex analytics without the need for self-joins or subqueries. Experiment with different window types and combinations to unlock deeper insights from your data!


---

# 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:

```
GET https://docs.duql.dev/advanced/window.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
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.