Aggregating Values

Perform a common action on multiple rows within a window.

1. Replace the two statements beginning wit "-- Create a window" in the Queries tab with the following text (or copy the text from MaintainingStateCCL.txt between the lines "-- Maintaining State Code Block 2" and "-- Maintaining State Code Block 3"):

   -- 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;
   
   -- Calculate an average and insert into the output stream
   INSERT INTO FilteredTrades
   SELECT *, AVG(Price)
   FROM FilteredTradesWindow
   WHERE Symbol = 'EBAY' OR Symbol = 'IBM'
   GROUP BY Symbol;
   

   The first two statements are exactly the same as before. The third statement sends rows to an output stream by connecting the output of one query to the input of another. Here's a brief explanation of the new code:

   • The new statement takes all of the columns from the row arriving from the input stream and the calculated average of the values in the Price column of the rows in the window (using one of the Sybase CEP predefined aggregate functions) and sends the resulting row to the output stream FilteredTrades.

   • The WHERE clause limits the output to rows with either "EBAY" or "IBM" in the Symbol column.

   • The GROUP BY clause causes the aggregate function to calculate the average for groups of rows in the window based on the value of the Symbol column. Without this clause, the aggregate function would perform the calculation over all rows in the window.

   The output stream must have a new column to receive the average price.

2. Modify the schema defined in the CREATE OUTPUT STREAM statement by adding a comma after "INTEGER" followed by "AvgPrice FLOAT". The statement should look like this:

   CREATE OUTPUT STREAM FilteredTrades
   SCHEMA (
       Symbol STRING,
       Price  FLOAT,
       Volume INTEGER,
       AvgPrice FLOAT
   );
   

3. Run the project again and examine the viewer for the output stream:

   
   
   

   Notice that Sybase CEP Engine publishes a row to the output stream as soon as a row meeting the filter condition arrives in the window. A row arriving from the input stream triggers both the aggregation calculation and the output row.

4. Stop the project.