Jumping Count-Based Windows

A jumping count-based window adds rows or buckets to a window until it is full and then expires all of the old rows at the same time upon the arrival of the next row, or creation of the next bucket.

The basic syntax for a jumping count-based window is:

KEEP EVERY integer ROWS

Consider the difference in the output if the previous example were written with a jumping count-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 3 ROWS;

In this example, a jumping window of three rows would be created and after the first three rows were stored in the window, resulting in three output values for the average price being published, all the rows would expire and the next three rows would create a new set of data in the window and a new set of average price results, completely independent of the values from the previous three rows.

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