Azure stream analysis query moving average

Question

While using Azure Stream Analysis, I can create a kind of moving average by using AVG and group them by the HoppingWindow like the code shown below.

However, this will create a moving average of the points in the last 5 seconds. Is there a way to create a moving average of the last n datapoint? I understand that I can adjust the windows size to make n points coming into the window, but is there a way to measure the exact last n points like the one in MySQL and PostgresSQL?

 SELECT System.TimeStamp AS OutTime, AVG (value)
 INTO
 [output]
 FROM  [input]
 GROUP BY HoppingWindow(second,5,1) 

Answer 1

Today ASA windows are only based on time. However you can use the LAG function to get previous events. For your query, if you want to make the average in the 3 latest events, it will be something like this:

SELECT System.TimeStamp AS OutTime,  
       AvgValue= ( LAG(value,1) OVER (LIMIT DURATION(minute, 5))  
                 + LAG(value,2) OVER (LIMIT DURATION(minute, 5))
                 + LAG(value,3) OVER (LIMIT DURATION(minute, 5))
                 )/3
FROM input  

Sorry for the inconvenience.

Thanks,

JS