0

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.

Yanis26
  • 247
  • 2
  • 13

1 Answers1

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
Vignesh Chandramohan
  • 1,306
  • 10
  • 15
  • Thanks that's what I just figured out also ! Do you know how can I use LAG to get a previous value by time and not the last n event ? For example when using a sliding window instead of tumbling window, if I use LAG, it will get me the previous value which is maybe 1s before. I know I can just create 2 computations, one with tumbling and one with sliding window, but it would still be great to know if we can lookup into the past by unit of time and not unit of events. – Yanis26 Apr 28 '18 at 17:10