An application or user can use SQLstream to create a relational view over 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:
- SELECT STREAM *
- FROM Orders OVER sla
- JOIN Shipments OVER sla
- ON Orders.id = Shipments.orderid
- WHERE city = 'New York'
- WINDOW sla AS (RANGE INTERVAL '1' HOUR PRECEDING)
This SQLStream SQL 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:
- CREATE VIEW tenMinuteMovingAverage AS
- SELECT STREAM ticker, price, AVG(price) OVER last10mins AS ma10mins
- FROM AllTrades
- WINDOW last10mins AS
- ( PARTITION BY ticker RANGE INTERVAL '10' MINUTE PRECEDING );
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.
- CREATE VIEW dualMovingAverages AS
- SELECT STREAM ticker, price,
- AVG(price) OVER last10mins AS ma10mins,
- AVG(price) OVER last50mins AS ma50mins
- FROM AllTrades
- WINDOW
- last10mins AS (PARTITION BY ticker RANGE INTERVAL '10' MINUTE PRECEDING ),
- last50mins AS (PARTITION BY ticker
- RANGE INTERVAL '50' MINUTE PRECEDING );
Finding Cross-over Points
Let's now look at how we can capture the stream of moving average cross-over points, that is, identifying events that could signify important transitions in stock prices.
The method we use here is to create a window (twoRows) comprising the last two messages (the current message and the preceding message), and use the operator FIRST to 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.
- SELECT STREAM ticker, price, ma10mins, ma50mins
- FROM dualMovingAverages AS dma
- WHERE FIRST(SIGN(dma.ma10mins - dma.ma50mins))
- OVER twoRows != SIGN(dma.ma10mins - dma.ma50mins)
- WINDOW twoRows AS (PARTITION BY ticker RANGE 1 ROWS PRECEDING);
Have questions about your current project? Click here to ask a SQLstream expert.