Azure stream analytics query using Tumbling window

Question

In our application, multiple IoT devices publish data to IoT hub. They emits some reading in rooms (for ex: power usage). Now we have a requirement to find out total energy consumed in an area in last hour and log it.

Suppose, there is a light bulb which was switched on 8:00 AM and take 60 watt power, and it was switched off at 8:20 for 10 min. At 8:30 it was switched on in dimmed manner with power usage 40 watt. So energy (Watt per hour) consumed between 8 and 9 AM should be:

60*20/60 (for 8:00 AM to 8:20 AM) + 0 (8:20 to 8:30) + 40*30/60 (8:30 to 9:00) = 40 watt per hour.

How can we write Stream Analytic query (using Tumbling window to achieve this).

Answer 1

You can use HoppingWindow to produce events every minute repeating latest signal from the device and then use TumblingWindow to get hourly aggregates.

-- First query produces event every minute with latest known value up to  1 hour back
WITH MinuteData AS
(
SELECT deviceId, TopOne() OVER (ORDER BY ts DESC) AS lastRecord 
FROM input TIMESTAMP BY ts
GROUP BY deviceId, HoppinWindow(miute, 1, 60)
)

SELECT 
deviceId,
SUM(lastRecord.wat)/60
FROM MinuteData
GROUP BY deviceId, TumblingWindow(hour, 1)