Joining Multiple Windows

Assume that you wanted to compare the average total sale (volume times price) for Oracle with the average sale for Microsoft over the past ten trades every time a trade occurs for either stock.

Here is one way to do this:

INSERT INTO OrclAvgSale 
SELECT AVG(volume*price) 
FROM StockTrades 
KEEP 10 ROWS 
WHERE symbol = 'ORCL'; 

INSERT INTO MsftAvgSale 
SELECT AVG(volume*price) 
FROM StockTrades 
KEEP 10 ROWS 
WHERE symbol = 'MSFT'; 

INSERT INTO AvgSaleComparison 
SELECT ORCL.avgsale, MSFT.avgsale 
FROM
  OrclAvgSale AS ORCL KEEP LAST,
  MsftAvgSale AS MSFT KEEP LAST;

The first query calculates the average sale amount over the previous 10 rows for Oracle stock and publishes the results to the OrclAvgSale stream, while the second query does exactly the same thing for all Microsoft stock and publishes the results to the MsftAvgSale stream. Both of these streams have an identical schema which contains the average sale value:

The third query joins the contents of MsftAvgSale and OrclAvgSale and publishes the results to the combined AvgSaleComparison stream, which has the following schema:

This query publishes a row whenever a row arrives either from MsftAvgSale, or from OrclAvgSale. Here is an illustration of how this join query works:

Notice that the StockTrades window in the first two queries are illustrated as being shared. If two or more windows in a project are based on the same stream and have identical retention policies, Sybase CEP does not need to store two copies of the window. Sybase CEP Engine optimizes this situation by sharing the common window data between two or more queries.

Notice that the join in this query doesn't have a join condition (in fact, it doesn't have a WHERE clause at all). Whenever there is no WHERE clause with a join condition, the result of executing the query will be a Cartesian product of the two data sources.

However, also note that in this query, both data sources are KEEP LAST windows. This means that at any moment in time, the windows for each of the data sources will only have the most recent average sale value for the particular stock (a KEEP LAST window is also the same as a KEEP 1 ROW window, which means to keep only the most recent row that has arrived).

The Cartesian product of two one-row windows is a single row so there is no need for a join condition at all in this example.