In the previous section, we discovered how Stream Analytics works. In this section, we are going to discuss some of the more advanced features of this service; in particular, the query language. The Query tab allowed us to define a simple, SQL-like query to process data and store it in data lake. This mechanism can also be applied to different storage mechanisms such as SQL, Event Hub, BLOB, Power BI, and Azure Functions. We can also link different input data sources, such as other data streams or reference data. Reference data is data that is statically stored, normally in a BLOB store, that can be joined with the input data stream. An example of reference data would be the asset information.

The Advanced  Stream Analytics query language also allows you to group by time using a method called windowing, which means that you can define a relative time interval, such as five seconds, to aggregate measures:

SELECT
    deviceId AS Device,
    max(ts),
    Avg(temperature) AS temperature, 
    Avg(flow) AS flow, 
    Avg(udf.efficiency(temperature,flow)) AS efficiency
INTO
    efficiencyoutput
FROM
    iiothub 
TIMESTAMP BY ts
GROUP BY TumblingWindow(second,5), deviceId

To test this simple query, we can stop our analytics. From the Query tab, acquire three minutes of samples by clicking on Sample data from input. We can then test our query. The following screenshot shows the expected output when the data is sampled every five seconds. It is also important to group by deviceId to avoid to mixing data between devices:

Azure supports three types of windowed grouping, 14 aggregate functions, and a very powerful time management system for skipping out of order data. Out of order data is data that arrived too late to be processed. 

A complete explanation can be found at https://msdn.microsoft.com/en-us/azure/stream-analytics/reference/stream-analytics-query-language-reference.