How to create a delayed sliding window in Azure Stream Analytics

Question

I would like to calculate the rate of change between the two following values in my stream:

• AVG(value) in SlidingWindow of 1mn
• AVG(value) in SlidingWindow of 1mn in the previous minute

The only thing I can't find in the documentation is how to create a "delayed" sliding window, meaning that it begins 2mn before and ends 1mn before the actual time so I can make some calculations such as the rate of change.

Answer 1

You can do it two steps.

1. Compute one minute aggregates of AVG.
2. Use LAG over previous stream's AVG

Something like below

WITH OneMinuteWindows AS
(
SELECT
    Avg(Column1) AvgValue
FROM
    InputEventHub
GROUP BY
    TumblingWindow(mi, 1)
)

SELECT
    System.TimeStamp [TimeStamp],
    AvgValue [CurrentValue],
    LAG(AvgValue) OVER (LIMIT DURATION(mi, 2)) [PreviousValue]
FROM 
    OneMinuteWindows