GROUP BY Clause with last() Function

The example creates a schema named TradeSchema.

Create Schema TradeSchema 
	(Ts bigdatetime, Symbol STRING, Price MONEY(4), Volume INTEGER); 

The example creates the schema TradesWidthDelaySchema, and uses the INHERITS syntax to apply the structure of TradeSchema to TradesWidthDelaySchema with row delay.

CREATE SCHEMA TradesWidthDelaySchema  INHERITS TradeSchema 
   (RowDelay long);

The example creates an input window named TradeWindow, to which it attaches the File CSV Input adapter.

The example then creates an output window named TradesWithDelay that uses the structure defined in TradesWidthDelaySchema. The SELECT clause places a row delay on timestamp, symbol, price, and volume data rows. The HAVING clause references the RowDelay column in the results of the query by not specifying a window name. The HAVING clause limits the output window to rows in which the delay is greater than 10 milliseconds.

SELECT 
    TradeWindow.Ts Ts, 
    TradeWindow.Symbol Symbol, 
    TradeWindow.Price Price, 
    TradeWindow.Volume Volume,
    timeToMsec (TradeWindow.Ts) - timeToMsec(last(TradeWindow.Ts,1))
       as RowDelay
 FROM 
    TradeWindow
 GROUP BY 
    TradeWindow.Symbol
 Having .RowDelay > 10
;

The example creates an output window named OutTrades that uses the structure defined in TradeSchema. The GROUP BY statement processes the selected rows by Symbol when the trade price is greater than the last trade price processed. Based on the previous arguments, the project server recognizes when the trade price has increased and the time between trades is greater than 10 milliseconds.

GROUP BY
    TradeWindow.Symbol
    having 
    TradeWindow.Price > last(TradeWindow.Price,1)
;