A sliding time-based window retains a variable number of rows, depending on how many rows have arrived within the specified interval.
As time passes, rows that have been in the window longer than the specified interval expire from the window. Expiration of rows happens regardless of whether new rows arrive in the window or not.
This is significantly different from count-based windows. In count-based windows, rows only expire when new rows arrive.
Because rows can arrive and expire at different times, the output of a time-based window query will often differ dramatically from a count-based window.
The basic KEEP clause syntax for a sliding time-based window is:
KEEP interval-literal
where interval-literal specifies the length of time a row will stay in the window before it expires.
Let's rewrite the sliding count-based window example used in the previous section. Instead of retaining 5 rows in the window, an interval of 10 seconds is used:
INSERT INTO StockTradesMicrosoft SELECT volume, price FROM StockTrades WHERE symbol = 'MSFT'; INSERT INTO AvgPriceMicrosoft SELECT AVG(price) FROM StockTradesMicrosoft KEEP 10 SECONDS;
In this example, a new average price is calculated every time a new row enters the window, just as before. However, additional average prices will be calculated each time a row expires from the window as well, because whenever a row expires, the average price for the preceding 10 seconds will change:
In this example (unlike in count-based examples), more rows come out of the query than go in. This is caused by the changing value of the average price each time a row expires in the window. These expirations occur at 07:15:11, 07:15:13, 07:15:15, and 07:15:26.
Specifically notice that when the row which entered at 07:15:05 expires at 07:15:15, the output row for that timestamp shows a value of NULL, because at the moment, there are no other rows in the window to produce an average value.