Matching Associated Events

It is possible that two sources of events may contain rows that need to be related by an associated key value. For instance, if you want to detect stock trades in which the price of the stock has gone up by more than $1 within the past 5 seconds, you might consider writing the following matching query:

INSERT INTO StockGains 
SELECT First.tradeid, Second.tradeid 
FROM StockTrades as First,
   StockTrades as Second 
MATCHING [5 seconds: First, Second] 
WHERE (Second.price - First.price) > 1.0;

This uses the stream alias and self-join techniques to use a single stream as if it were two unique streams. This allows you to compare the events in a stream with itself. The above query will generate results, but it will match every row in StockTrades. A more likely desired result is to only compare trades that have the same symbol. This can be done using the ON clause to specify the matching condition between the two streams:

INSERT INTO StockGains 
SELECT First.tradeid, Second.tradeid 
FROM StockTrades as First,
   StockTrades as Second 
MATCHING [5 seconds: First, Second] 
ON First.symbol = Second.symbol 
WHERE (Second.price - First.price) > 1.0;

This looks very similar to earlier join conditions using the WHERE clause. However, note that in an ON clause, only the equal sign may be used to compare columns. Because the ON clause is specifically limited to providing join conditions, it can be optimized in ways that a regular WHERE clause can't.

If more than two events need to be matched, the ON clause permits a shorthand notation for associating columns in the data sources:

INSERT INTO MultipleTrades 
SELECT First.tradeid, Second.tradeid, Third.tradeid 
FROM StockTrades as First,
   StockTrades as Second,
   StockTrades as Third 
MATCHING [5 seconds: First, Second, Third] 
ON First.symbol = Second.symbol = Third.symbol;

This query detects three consecutive trades for any given stock within the interval.