Grouping a Window

All of the windows explained so far consist of a single group of rows from the stream. However, Sybase CEP has a way of taking a window and partitioning it by one or more column values. For instance, if the original StockTrades stream is used as the input to a window, and a KEEP 2 ROWS policy is specified, only the most recent 2 rows for the entire stream will be kept in the window. An alternative requirement might be to keep the most recent two rows for each company (symbol) whose stock is traded. This can be done by using the GROUP BY clause with a windowed query:

INSERT INTO StockPrices 
SELECT symbol, AVG(price), MAX(price) 
FROM StockTrades 
KEEP 2 ROWS 
GROUP BY symbol;

Here is the schema for the stream StockPrices:

In this query, the GROUP BY clause creates a two-row subwindow for each symbol. Each subwindow is managed independently and output is created for each new row that arrives for each symbol. The average price and maximum price that is calculated is for the current and previous rows for that symbol.

Here is an illustration of the window and stream data for this query:

The window illustration shows the rows grouped (and sorted) by their symbol. Notice that rows expire in the window based on their symbol. Only two rows (the most recent row and the previous row) for each symbol are kept in the window.