Aggregators in a Stream/Window Join

An example of a join query that publishes the symbol and the volume weighted average price (VWAP) for a stock over a limited period. Describes the InquiryPrice stream and schema.

INSERT INTO InquiryPrice 
SELECT StockInquiry.symbol, SUM(volume*price)/SUM(volume) 
FROM StockInquiry, StockTrades 
KEEP 10 SECONDS 
WHERE StockTrades.symbol = StockInquiry.symbol 
GROUP BY StockTrades.symbol;

The output of the query goes to the InquiryPrice stream which has the following schema:

Column	Datatype	Description
symbol	String	The symbol for this set of trades.
vwaprice	Float	The volume-weighted average price for the most recent set of trades.

In this example, the stock trades are accumulated in a sliding time-based window for ten seconds and grouped by symbol using the GROUP BY clause.

Note:

Obviously, ten seconds is an artificially small window period which is chosen to match the small amount of data in the illustrative data stream. Typically, the window period will be much greater for most kinds of data, and can be any reasonable value, from a few minutes to many hours or even days, depending on the volume of data that will be retained and the available memory in your system.

Here is an illustration of how this join query works: