A sliding count-based window retains a fixed number of rows or buckets.
Once the window is full, each new row that arrives or each new bucket that is created in the window displaces the oldest row or bucket, which is then removed from the window.
The basic KEEP clause syntax for a sliding count-based window is:
KEEP number ROWS
where number specifies the number of rows the window should keep.
Earlier, there was an example that created the StockTradesMicrosoft stream which consisted of all stock trades for Microsoft. Here is an example of a sliding count-based window that is used to calculate the average price of the preceding three Microsoft trades on a continual basis using the StockTradesMicrosoft stream as a data source:
INSERT INTO StockTradesMicrosoft SELECT volume, price FROM StockTrades WHERE symbol = 'MSFT'; INSERT INTO AvgPriceMicrosoft SELECT AVG(price) FROM StockTradesMicrosoft KEEP 3 ROWS;
This example illustrates the use of two queries in a pipeline. The output stream from the first query (StockTradesMicrosoft) is subscribed to by the second query. Each time a new row is added to the window in the second query, the oldest row in that window is also removed, and the second query is executed which calculates the average price for all of the rows in the window.
The output of the second query (in this case, just the expression AVG(price)), which calculates the average price of the most recent three Microsoft trades, is sent to the AvgPriceMicrosoft stream:
The AVG function in the second query is not a scalar function. It is an aggregator function which operates on more than one row at a time. The AVG function takes the sum of the values for the specified column, and divides the sum by the number of rows that are in the group of rows, which in this case is the same as the number of rows in the window.
If a row has a value of NULL for the column being aggregated, the value is not included in either the sum or the count of the number of rows. This is different from a value of 0, which would be added to the sum (although having no effect on the total) and would be included in the count, thereby reducing the aggregated value of the average. Thus, values 0, 2 and 4 would yield an average of 2 ((0+2+4)/3 = 6/3 = 2) but value of NULL, 2 and 4 would yield an average of 3 ((2+4)/2 = 6/2 = 3).
In addition to AVG, there are several other aggregator functions (such as SUM, MIN, MAX, COUNT), which, like AVG, are almost always used in conjunction with data in windows.
Here is an example of how the data flows from StockTrades through the first query and then into the second query, creating the window which generates the ultimate average price information. Keep in mind that the average price is constantly recalculated every time a new row enters the window from the StockTradesMicrosoft stream:
