KEEP PER Clause

Use the KEEP PER clause with an input window, derived window, or an unnamed window.

The example creates a schema named TradeSchema.

CREATE SCHEMA TradeSchema (
    Id         long,
    Symbol     STRING,
    Price      MONEY (4),
    Volume     INTEGER,
    TradeTime  DATE
);

The example then creates an input window named Trades which keeps the last 10,000 rows per Symbol.

CREATE INPUT WINDOW Trades
SCHEMA TradeSchema
PRIMARY KEY (Id)
KEEP 10000 ROWS PER (Symbol);

The example creates an output window named Last5TradesPerHour that retains only the last five rows per Symbol per hour.

CREATE OUTPUT WINDOW Last5TradesPerHour
        PRIMARY KEY DEDUCED
    KEEP 5 ROWS PER (Symbol, TradeHour)
    As SELECT trw.*, (trw.TradeTime/3600)*3600 TradeHour
    FROM Trades trw;

The example creates an output window named Last50TradesStats that retains data for the last 50 trades per Symbol.

CREATE OUTPUT WINDOW Last50TradesStats
        PRIMARY KEY DEDUCED
    AS SELECT trw.Symbol, MAX(trw.Price) MaxPrice, 
MIN(trw.Price) MinPrice, SUM(trw.Volume) Volume
    FROM Trades trw KEEP 50 ROWS PER (Symbol)
    GROUP BY trw.Symbol;

The example concludes by attaching the XML Input Adapter to the input window named Trades to process the incoming stream data.

ATTACH INPUT ADAPTER xmlInConn1
    TYPE xml_in
    TO Trades
    PROPERTIES
        blockSize=1,
        dir='../exampledata',
        file='Trades.xml',
        filePattern='*.xml',
        safeOps=false,
        skipDels=false;