Maintaining State per Column Value

Modify the count-based window to maintain the rows within the window.

1. Insert "PER Symbol" before the semi-colon at the end of the Create Window statement and then remove the WHERE clause from the Query statement. The results should look like this:

   -- Create a window
   CREATE WINDOW FilteredTradesWindow
   SCHEMA (Symbol STRING, Price FLOAT,
   	Volume INTEGER)
   KEEP 3 ROWS PER Symbol;
   
   -- Insert rows into the window
   INSERT INTO FilteredTradesWindow
   SELECT *
   FROM StockTrades;
   

   Adding the PER clause specifies that the window maintain three rows for each unique value in the Symbol column, rather than three rows total. Note the green bar to the left of the statement in the Queries tab, which is an indicator of the relationship between this CCL statement and the associated component in the Explorer view and the Flow view.

2. Start the project again, and then examine the window viewer:

   
   
   

   Notice that rows entering and leaving the window are marked just as before.

3. Click the Disconnect All Viewers button to examine the rows more carefully. Scroll up and down in the viewer to see that Sybase CEP Engine removes a row when the window reaches the limit of three for a specific value of the Symbol column.
4. Reconnect the viewers and then stop the project.