Sliding Window

A sliding window is a time-based or row-based analytic window. With a sliding window, you apply an analytic to a given amount of rows that changes incrementally as rows stream in. For example, you might take the sum of a column called Orders for the past hour. At 12:01, the analytic would apply to all rows with timestamps between 11:01 and 12:01.

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 uses 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 is excluded from the analytic. 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 uses rows from a specified interval of time. In this case, all rows whose rowtimes precede the current rowtime by one hour will be excluded from the analytic. In other words, rows that precede the previous hour are “dumped” or “discarded” from the analytic window.

For more information see the WINDOW clause topic in the SQL Reference Guide.