Solutions

Solutions to maintaining state exercise.

Parent topic: Maintaining State

Aggregating Values

remove sub-heading

  1. You should also change the schema of FilteredTrades, replacing AvgPrice with a column named something like TotalVolume of type Integer.
  2. Change the SELECT clause in the third code block to this:
    SELECT *, SUM(Volume * Price) / SUM(Volume)
  3. Replace 2 with 10 in the KEEP statement of the first code block, and change the SELECT clause in the third code block to this:
    SELECT *, MAX(Price)

Exploring Window Types

Exercise

  • A window that maintains rows until a specific number is reached and then empties is called a jumping count-based window.

Maintaining Rows Based on Value

remove sub-heading

  1. Code similar to the following clears the window at the specified time:
    INSERT INTO FilteredTrades
SELECT *, SUM(Volume)
FROM StockTrades
KEEP UNTIL '13:08'
WHERE Symbol = 'EBAY'
GROUP BY Symbol;

    KEEP UNTIL retains rows in the window until the specified time, and then empties the window. In this example, the value in the AvgPrice column increases for each row until the specified time, then drops to the value of the column in the first row arriving after the specified time and begins increasing again. If you specify a general time, the window empties at the same time every day. If you identify the day of week, it empties at the same time of the same day every week, and so on. You can also specify a list of times. See the "KEEP Clause" section of the Sybase CEP CCL Reference Guide for more information.

  2. The following CCL code performs the requested task, only producing output for rows with "EBAY" in the Symbol column:
    INSERT INTO FilteredTrades
SELECT *, AVG(Price)
FROM StockTrades
KEEP 3 SMALLEST ROWS BY Price
WHERE Symbol = 'EBAY'
GROUP BY Symbol;