Sliding Window

<< Click to Display Table of Contents >>

Navigation:  Glossary >

Sliding Window

Previous pageReturn to chapter overviewNext page

A sliding window is a time-based or row-based window. Sliding window queries do not contain a flooring function, which means that rows are not batched into intervals. The query's results slide forward one row at a time. In other words, for every row that the query finds, it emits one row.

This is an example of a sliding window in a query:

SELECT STREAM *, SUM(amt) OVER w

 FROM sales

 WINDOW w AS (PARTITION BY store_id

              ORDER BY s.ROWTIME

              RANGE BETWEEN INTERVAL '1' HOUR PRECEDING AND CURRENT ROW);

 

 

You can specify sliding windows in two ways:

As ROWS N PRECEDING, such as ROWS 10 PRECEDING

This analytic window looks at the last N rows, such as the last 10 rows. When the query starts up, the window fills up to N rows. From then on, as a new row enters the window, an old row will is discarded. For all cases where N is a number, the number of rows in the analytic window will always be N+1. You can also specify ROWS UNBOUNDED PRECEDING, which includes all available previous rows in its analytic window.

As RANGE BETWEEN INTERVAL, such as RANGE BETWEEN INTERVAL '1' HOUR PRECEDING AND CURRENT ROW

This analytic window looks rows over a specified interval of time, such as all rows from the previous hour. Rows are discarded when their rowtimes exceed the specified interval from the current rowtime. This means that every time rowtime steps forward, zero or more older rows may be dumped out of the window.