BUILT‐IN FUNCTIONS – Data Sources and Ingestion

As shown in Table 3.20, the built‐in functions are further categorized into groups such as aggregate, analytic, conversion, date, mathematical, and windowing.

TABLE 3.20 Azure Stream Analytics built‐in functions

Type	Functions
Aggregate	AVG, COUNT, MIN, MAX, STDEV, SUM, VAR, TopOne
Analytic	ISFIRST, LAG, LAST
Conversion	CAST, GetType, TRY_CAST
Date	DATEADD, DATEDIFF, DATEPART
Mathematical	CEILING, FLOOR, POWER, ROUND, SIGN, SQUARE, SQRT
Windowing	Hopping, session, sliding, snapshot, tumbling

The most interesting, powerful, and complex function group in Table 3.20 is the windowing category. Chapter 2 introduced windows functions in the context of the OVER clause. Do not confuse a windows function with a windowing function. A windows function is used to execute aggregate functions on a complete dataset and return the computation as part of the result, whereas a windowing function is focused on the analysis of a data stream that arrived during a defined period of time, i.e. the time window. It is more efficient to perform queries on data that streamed over a span of, say, the past 10 seconds, and perform some computation or transformation on that set of data, compared to performing the same analysis on each and every streamed message.

HOPPING WINDOW

A hopping window is illustrated in Figure 3.80 and implemented using the following SQL snippet:

SELECT READINGTYPE, COUNT(*) as Count
FROM brainwaves TIMESTAMP BY CreatedAt
GROUP BY READINGTYPE, HoppingWindow(second, 10, 5)

The second parameter represents the timeunit that the windowsize and hopsize are applied to, where the windowsize is 10 seconds and the hopsize is 5 seconds.

FIGUER 3.80 An Azure Stream Analytics hopping window

The query results in a count of the READINGTYPE every 5 seconds over the last 10 seconds.

SESSION WINDOW

A session window is illustrated in Figure 3.81 and implemented using the following SQL snippet:

SELECT READINGTYPE, COUNT(*) as Count
FROM brainwaves TIMESTAMP BY CreatedAt
GROUP BY READINGTYPE, SessionWindow(minute, 5, 10)

The minute parameter represents the timeunit that the windowsize and maxdurationsize are applied to, where the windowsize is 5 minutes and the maxduration is 10 minutes.

FIGUER 3.81 An Azure Stream Analytics session window

The query results in a count of READINGTYPE every 5 minutes when data exists in the 10‐minute timeframe.