Exploring Window Types

Use different window types when maintaining a specific number of rows situations.

1. Edit the query to replace "KEEP 3 ROWS" with "KEEP 2 SECONDS":

   -- Create an unnamed window to track average price by stock symbol
   INSERT INTO FilteredTrades
   SELECT *, AVG(Price)
   FROM StockTrades
   KEEP 2 SECONDS
   WHERE Symbol = 'EBAY' OR Symbol = 'IBM'
   GROUP BY Symbol;
   

   Now the window maintains a variable number of rows, keeping each row for two seconds. The part of the KEEP clause that specifies how rows are maintained is called the window policy.

2. Run the project and examine the output stream:

   
   
   

   Notice that the AvgPrice column sometimes contains Null. The query produces an output row (and therefore calculates the average) not only when a row arrives on the input stream, but also when a row is removed from the window, because either event affects the value of any aggregate functions. If the sub-window for a specific Symbol value is empty when a row is removed from the window (which happens when a row arrives with a particular Symbol value but no additional row with that value arrives in the next two seconds), the calculation results in Null.

   This type of window is called a time-based window. It is also called a sliding window, because Sybase CEP Engine continuously removes rows from the window (based on the policy) as new rows arrive.

3. Stop the project.
4. Insert the word "EVERY" after the word "KEEP" in the query:

   -- Create an unnamed window to track average price by stock symbol
   INSERT INTO FilteredTrades
   SELECT *, AVG(Price)
   FROM StockTrades
   KEEP EVERY 2 SECONDS
   WHERE Symbol = 'EBAY' OR Symbol = 'IBM'
   GROUP BY Symbol;
   

   Adding EVERY to the KEEP clause specifies that the window maintain all rows that arrive during the specified interval. At the end of each interval, the window empties.

5. Run the project and observe the output stream:

   
   
   

   Now the window doesn't remove individual rows based on age, but instead removes all rows at the end of each two-second interval (the interval timing is based on the epoch rather than the timestamp of the first row in the stream). This is called a jumping window, as opposed to sliding.

   Because of this, the query produces output when a row arrives from the input stream that meets the filter condition in the WHERE clause, but not when rows are removed from the window when the interval expires. If no rows arrive during a particular interval, the query produces no output. The calculation never results in NULL because it is never performed when a group is empty.

6. Stop the project.