Azure Streaming Analytics Calculate OHLC

Question

I am trying to calculate Open, Low, High and Close values for stock prices using Azure Stream Analytics SQL.

I can get Min and Max fairly easily, but I am having difficulty figuring out how to calculate Open (FIRST) and Close (LAST) of a TumblingWindow.

I have found the documentation here (https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-stream-analytics-query-patterns) to do first and last but I cannot combine them into a single query.

Here's what I have:

SELECT
    DateAdd(second,-5,System.TimeStamp) as WinStartTime,
    system.TimeStamp as WinEndTime,
    exchange,
    Max(price) as H,
    Min(price) as L,
    Count(*) as EventCount
FROM [input1]
GROUP BY TumblingWindow(second, 5), exchange

I am not terribly advanced in SQL, but is there a way to combine this into a single query? Even with the use of subqueries.

Note: 5 seconds is just an example window I chose for testing.

Answer 1

According to your scenario, I assumed that you could leverage the Collect aggregate function and user-defined functions from Azure Stream Analytics to achieve your purpose. Here are the details, you could refer to them:

Assuming your input looks as follows:

[
 {"price":1.1,"exchange":10,"Time":"2017-7-24T13:00:00Z"},
 {"price":1.2,"exchange":20,"Time":"2017-7-24T13:04:00Z"},
 {"price":1.4,"exchange":40,"Time":"2017-7-24T13:03:00Z"},
 {"price":1.3,"exchange":30,"Time":"2017-7-24T13:02:00Z"},
 {"price":1.5,"exchange":50,"Time":"2017-7-24T13:06:00Z"}
] 

UDF

// udf.getLast
function main(objs) {
    if(objs==null||objs==undefined||objs.length==0)return null;
    return objs[objs.length-1];
}

// udf.getFirst
function main(objs) {
    if(objs==null||objs==undefined||objs.length==0)return;
    return objs[0];
}

QUERY

SELECT
    DateAdd(minute,-5,System.TIMESTAMP) as WinStartTime,
    System.TIMESTAMP as WinEndTime,
    UDF.getFirst(Collect()) AS FIRST,
    UDF.getLast(Collect()) AS LAST,
    Max(price) as H,
    Min(price) as L,
    Count(*) as EventCount
FROM [input1] TIMESTAMP By Time
GROUP BY TumblingWindow(minute, 5)

RESULT: