> For the complete documentation index, see [llms.txt](https://docs.e6data.com/ingestion-engine/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.e6data.com/ingestion-engine/sql/streaming/windows.md).

# Windows

Because of the unbounded nature of event streams, it is common to aggregate data over a time window. There are a number of types of windows that are used in stream processing systems. Currently e6 Ingestion Engine supports three kinds of windows: *sliding* (aka hopping), *tumbling*, and *session* windows.

Using a streaming window makes the query time and watermark aware.

## Example Dataset

The examples below all use the same `bids` table, representing auction bids arriving over a roughly four-minute period:

| auction\_id | price | event\_time         |
| ----------- | ----- | ------------------- |
| 1           | 10.00 | 2024-01-01 10:00:15 |
| 2           | 25.00 | 2024-01-01 10:00:30 |
| 1           | 12.00 | 2024-01-01 10:00:45 |
| 3           | 30.00 | 2024-01-01 10:01:10 |
| 2           | 28.00 | 2024-01-01 10:01:40 |
| 1           | 15.00 | 2024-01-01 10:02:05 |
| 3           | 35.00 | 2024-01-01 10:02:30 |
| 2           | 20.00 | 2024-01-01 10:03:50 |

## Tumbling Windows

Tumbling windows are consecutive, non-overlapping windows of a fixed size. Usually that size will be some human-friendly time unit like minutes or hours, but that isn't a requirement. Whereas in normal SQL you might group by a `date_trunc` call, in streaming SQL you'd use a tumbling window.

In the e6 Ingestion Engine, windowing is enabled via special UDFs, in this case `TUMBLE()`. For example, to get the number of distinct auction IDs across bids for each minute, you'd write a query like

```sql
SELECT TUMBLE(interval '1 minute') as window,
    COUNT(DISTINCT auction_id) AS num_auctions
    FROM bids
    GROUP BY window
```

Every record in e6 Ingestion Engine has a corresponding timestamp, which will be processed by the `TUMBLE()` function. The resulting records will have a timestamp of the end of the window minus 1 nanosecond.

### Expected Output

| window                         | num\_auctions |
| ------------------------------ | ------------- |
| 2024-01-01 10:00:00 – 10:01:00 | 2             |
| 2024-01-01 10:01:00 – 10:02:00 | 2             |
| 2024-01-01 10:02:00 – 10:03:00 | 2             |
| 2024-01-01 10:03:00 – 10:04:00 | 1             |

### Walkthrough

Each row is assigned to exactly one 1-minute window based on its `event_time`:

* **\[10:00:00 – 10:01:00)**: rows at 10:00:15, 10:00:30, 10:00:45. Auction IDs are {1, 2, 1} - distinct count = 2.
* **\[10:01:00 – 10:02:00)**: rows at 10:01:10, 10:01:40. Auction IDs are {3, 2} - distinct count = 2.
* **\[10:02:00 – 10:03:00)**: rows at 10:02:05, 10:02:30. Auction IDs are {1, 3} - distinct count = 2.
* **\[10:03:00 – 10:04:00)**: row at 10:03:50. Auction ID is {2} - distinct count = 1.

## Sliding Windows

Sliding windows are an extension of tumbling windows, with the addition of a "slide". It is defined by two time durations, a width for each window and a slide that designates the time between the start of consecutive windows. Typically the slide is less than the window. A sliding window can be used to provide a view of data over some lookback time (the width), updated with some frequency (the slide).

In the e6 Ingestion Engine, the `HOP()` function is used to create sliding windows. It takes two arguments, the first is the slide and the second is the window size.

For example, to get the number of distinct auction IDs across bids for the previous minute every 30 seconds, you'd write a query like

```sql
SELECT HOP(interval '30 seconds', interval '1 minute') as window,
    COUNT(DISTINCT auction_id) AS num_auctions
    FROM bids
    GROUP BY window
```

e6 Ingestion Engine has optimized sliding windows so that each hop only needs to perform an incremental amount of work, allowing for long sliding windows with small hops.

### Expected Output

| window                         | num\_auctions |
| ------------------------------ | ------------- |
| 2024-01-01 10:00:00 – 10:01:00 | 2             |
| 2024-01-01 10:00:30 – 10:01:30 | 3             |
| 2024-01-01 10:01:00 – 10:02:00 | 2             |
| 2024-01-01 10:01:30 – 10:02:30 | 2             |
| 2024-01-01 10:02:00 – 10:03:00 | 2             |
| 2024-01-01 10:02:30 – 10:03:30 | 1             |
| 2024-01-01 10:03:00 – 10:04:00 | 1             |
| 2024-01-01 10:03:30 – 10:04:30 | 1             |

### Walkthrough

A new window starts every 30 seconds (the slide) and spans 1 minute (the width). Rows can appear in multiple overlapping windows:

* **\[10:00:00 – 10:01:00)**: rows at 10:00:15, 10:00:30, 10:00:45. Auction IDs {1, 2} - distinct = 2.
* **\[10:00:30 – 10:01:30)**: rows at 10:00:30, 10:00:45, 10:01:10. Auction IDs {2, 1, 3} - distinct = 3.
* **\[10:01:00 – 10:02:00)**: rows at 10:01:10, 10:01:40. Auction IDs {3, 2} - distinct = 2.
* **\[10:01:30 – 10:02:30)**: rows at 10:01:40, 10:02:05. Auction IDs {2, 1} - distinct = 2.
* **\[10:02:00 – 10:03:00)**: rows at 10:02:05, 10:02:30. Auction IDs {1, 3} - distinct = 2.
* **\[10:02:30 – 10:03:30)**: row at 10:02:30. Auction ID {3} - distinct = 1.
* **\[10:03:00 – 10:04:00)**: row at 10:03:50. Auction ID {2} - distinct = 1.
* **\[10:03:30 – 10:04:30)**: row at 10:03:50. Auction ID {2} - distinct = 1.

Notice that the row at 10:00:45 appears in both the first and second windows, demonstrating the overlapping nature of sliding windows.

## Session Windows

Session windows are non-fixed-width windows that are defined by a gap in activity. For example, a session window with gap size 30 minutes, defined on a stream of user clicks would aggregate over all clicks that occur within 30 minutes of each other. Once there has been a 30 minute gap for a given user, the session window would close and a new one would be opened for the next click.

In the e6 Ingestion Engine, the `SESSION()` function is used to create session windows.

For example, to count the number of bids per auction within sessions separated by a 1-minute gap:

```sql
SELECT auction_id,
    SESSION(interval '1 minute') as window,
    COUNT(*) AS bid_count
    FROM bids
    GROUP BY auction_id, window
```

Session windows are limited to a maximum size of 24 hours. If a window is open for longer than that, it will be closed and a new one will be opened.

### Expected Output

| auction\_id | window                         | bid\_count |
| ----------- | ------------------------------ | ---------- |
| 1           | 2024-01-01 10:00:15 – 10:00:45 | 2          |
| 1           | 2024-01-01 10:02:05 – 10:02:05 | 1          |
| 2           | 2024-01-01 10:00:30 – 10:00:30 | 1          |
| 2           | 2024-01-01 10:01:40 – 10:01:40 | 1          |
| 2           | 2024-01-01 10:03:50 – 10:03:50 | 1          |
| 3           | 2024-01-01 10:01:10 – 10:01:10 | 1          |
| 3           | 2024-01-01 10:02:30 – 10:02:30 | 1          |

### Walkthrough

Session windows group events per key (here `auction_id`) and split whenever the gap between consecutive events exceeds the specified interval (1 minute):

* **auction\_id = 1**: events at 10:00:15, 10:00:45, 10:02:05. The gap from 10:00:45 to 10:02:05 is 80 seconds (> 1 min), so this produces two sessions: one covering {10:00:15, 10:00:45} with bid\_count = 2, and one at {10:02:05} with bid\_count = 1.
* **auction\_id = 2**: events at 10:00:30, 10:01:40, 10:03:50. The gap from 10:00:30 to 10:01:40 is 70 seconds (> 1 min), and from 10:01:40 to 10:03:50 is 130 seconds (> 1 min). Each event is its own session, producing three rows with bid\_count = 1.
* **auction\_id = 3**: events at 10:01:10, 10:02:30. The gap is 80 seconds (> 1 min), so two separate sessions, each with bid\_count = 1.


---

# 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://docs.e6data.com/ingestion-engine/sql/streaming/windows.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.