An application or user can use SQLstream to create a relational view over various message streams, transforming the data by applying relational operations such as aggregation, correlation, and filtering.

Here is a simple example involving a service-level agreement (sla) time window:

This SQLStream RAM query delivers all orders from New York that are shipped within the time window of their service-level agreement (in this case, one hour). It is only necessary to execute this query once. The query operates continuously, automatically delivering all orders that meet their service-level agreement to all interested subscribers.

Applications or users subscribe to a relational query view, causing all messages published that satisfy such views to be automatically delivered as soon as they are available. Each row is time-stamped, and each query or view uses those time-stamps in preserving the order of transactions and determining the contents of defined time-windows.

Windowed Aggregation and Moving Averages

Here are some more sophisticated SQLstream examples that demonstrate the power of windowed aggregation. One of the classical operations performed on stock market trade data is calculating moving averages. Let's see how we can do that in SQLstream. Let's first calculate the moving average for a stock over say a rolling ten minute window:

We can extend this definition to two separate moving averages. We shall add one for the fifty minute moving average, and then compute them at the same time continuously.

Finding Cross-over Points

Let's now look at how we can capture the stream of moving average cross-over points. Such types of cross-over points are often believed to signify important transitions in stock prices.

The method we use here to do that is to first create a window (twoRows) comprising the last two messages (the current message and the preceding message) and then to use the operator FIRST to effectively access the message arriving immediately before the current message and to see whether there has been a cross-over of the moving averages with the current message. A ‘cross-over' point is indicated where the sign of the difference between the two moving averages has flipped. If one moving average were bigger than the other in the previous message, then the other moving average has in fact become the larger value in the current message's moving average figures).