You have an Azure Stream Analytics solution that receives data from multiple thermostats in a building. You need to write a query that returns the average temperature per device every five minutes for readings within that same five minute period. Which two windowing functions could you use?

Category : Microsoft Azure Data Engineering | Sub Category : Practice Assessment for Exam DP-203 - Data Engineering on Microsoft Azure | By Prasad Bonam Last updated: 2023-09-10 02:57:52 Viewed : 289

Tumbling windows have a defined period and can aggregate all events for that same time period. A tumbling window is essentially a specific case of a hopping window where the time period and the event aggregation period are the same.

Hopping windows have a defined period and can aggregate the events for a potentially different time period

Sliding windows are used to create aggregations for so many events, not at identical timelapses.

Snapshot windows aggregate all events with the same timestamp.

Introduction to Azure Stream Analytics windowing functions | Microsoft Learn

Implement a Data Streaming Solution with Azure Stream Analytics - Training | Microsoft Learn

In Azure Stream Analytics, you can use two windowing functions to calculate the average temperature per device every five minutes for readings within that same five-minute period. The two windowing functions you can use are:

1. Tumbling Window: A tumbling window is a fixed-size, non-overlapping window that moves forward in time at regular intervals. In your case, you can use a tumbling window of 5 minutes to calculate the average temperature for each device over a 5-minute period. The result will be a new average calculated every 5 minutes.

   Example query using a tumbling window:

   sqlCopy code
   SELECT
       DeviceId,
       System.Timestamp() AS WindowEnd,
       AVG(Temperature) AS AvgTemperature
   FROM
       InputStream
   TIMESTAMP BY TimestampField -- Replace with your timestamp field
   GROUP BY
       DeviceId,
       TumblingWindow(second, 300) -- 300 seconds (5 minutes)
   

2. Hopping Window: A hopping window is a fixed-size window that can overlap with other windows. You can use a hopping window to calculate averages over overlapping 5-minute periods if needed. This can be useful if you want more frequent updates than every 5 minutes.

   Example query using a hopping window:

   sqlCopy code
   SELECT
       DeviceId,
       System.Timestamp() AS WindowEnd,
       AVG(Temperature) AS AvgTemperature
   FROM
       InputStream
   TIMESTAMP BY TimestampField -- Replace with your timestamp field
   GROUP BY
       DeviceId,
       HoppingWindow(second, 300, 300) -- 300 seconds (5 minutes) size and advance
   

The choice between tumbling and hopping windows depends on your specific use case. If you want non-overlapping 5-minute averages, use a tumbling window. If you want overlapping averages for more frequent updates, use a hopping window.