Jumping Time-Based Windows

A jumping time-based window adds rows to a window for a fixed interval and then expires all of the old rows at the same time when the end of the interval is reached. Provides the syntax for a jumping time-based window.

The syntax for a jumping time-based window is:

KEEP EVERY interval-literal

Consider the difference in the output if the previous example were written with a jumping time-based window like this:

INSERT INTO StockTradesMicrosoft 
SELECT volume, price 
FROM StockTrades 
WHERE symbol = 'MSFT'; 

INSERT INTO AvgPriceMicrosoft 
SELECT AVG(price) 
FROM StockTradesMicrosoft KEEP EVERY 10 seconds;

In this example, a jumping window of 10 seconds would be created and after the first 10 seconds of data was stored in the window, resulting in output values for each new row, all of the rows would expire and the next interval would begin, completely independent of the values from the previous interval.

Contrast the output of this jumping time-based window query with the output from the sliding time-based window query:

Note that unlike the sliding time-based window, no row is produced when the interval expires. In sliding time-based windows, it is important to generate these rows because the output values change at indeterminate moments in time based on when each individual row expires. Since the intervals expire at predetermined points, and since the output of each interval expiration would always be the same when the window is empty, there is no compelling reason to create an additional row with the average price set to NULL when the interval expires.