Finding a Sequence of Events

Run a pattern-matching query to isolate a sequence of events in your data.

Here is a simple pattern-matching query:

INSERT INTO MsftThenAapl 
SELECT MSFT.tradeid, AAPL.tradeid 
FROM
   (SELECT * from StockTrades WHERE symbol = 'MSFT') as MSFT,
   (SELECT * from StockTrades WHERE symbol = 'AAPL') as AAPL 
MATCHING [5 seconds: MSFT, AAPL];

Note the following important points:

The FROM clause must contain all the data sources mentioned in the matching clause. Note that in this query, the subquery and alias technique ( subquery AS alias) is applied to provide two different streams from the StockTrades stream, one with the trades for MSFT and one with the trades for AAPL.
None of the data sources mentioned in the FROM clause may be windows or window expressions.
The matching condition starts with the word MATCHING and is enclosed in square brackets.
The interval in the MATCHING clause specifies a sliding time period within which the desired pattern must be detected. The time period is triggered by the arrival of the first event in the sequence.
The desired pattern is expressed by giving the names of the data sources after the interval, in the order desired and possibly using special pattern matching operators to modify the type of pattern search that will be performed.
The comma operator, also called the sequence operator, means that an event from the first data source must be followed by an event from the second data source within the specified interval.

The query finds any trade for MSFT which is then followed by a trade for AAPL within five seconds. Here is an example of the output from this query:

Note that the timestamp associated with the output from the pattern match is the timestamp of the final event. Also notice that it is possible to get more than one match from an event, in which case the event will appear in the output stream multiple times. If two events in the first stream occur before two matching events in the second stream (and all four events occur within the interval), you will get four outputs: MSFT¹-AAPL¹, MSFT¹-AAPL², MSFT² -AAPL¹, and MSFT² -AAPL².