Windowing Operations in Azure Stream Analytics

Windowing is a very common operation in stream analytics.

Beneath the surface, there is a whole bunch of complex data structuring that’s going on to support the windowing operations. I would love to dig deeper into these someday.

Example:

Here is an example of a query I wrote recently using windowing operators in azure stream analytics. It shows 3 interesting things :
1. Windowing
2. CTEs
3. Aggregation over string columns (using TopOne)

WITH ContextReward AS (
    SELECT 
        eventid,
        TopOne() OVER (ORDER BY [EventEnqueuedUtcTime] ASC) CR,
        MAX (reward) AS reward
    FROM Input
    GROUP BY eventid, HoppingWindow(Duration(hour, 2), Hop(hour, 1))
)

SELECT 
    reward,
    eventid, 
    CR.actionname AS actionname,
    CR.age AS age,
    CR.gender AS gender,
    CR.weight AS weight,
    CR.actionprobability
INTO OutputWindow
FROM ContextReward

SELECT * INTO Output FROM Input 
SELECT * INTO OutputCSV FROM Input

 

References:

• https://msdn.microsoft.com/en-us/library/azure/dn835019.aspx
• https://azure.microsoft.com/en-us/documentation/articles/stream-analytics-stream-analytics-query-patterns/

Common Table Expressions in SQL

Tips:

• sql column with spaces
• group by aggregate over string column

References:

• http://stackoverflow.com/questions/5375634/can-i-use-multiple-with
• http://stackoverflow.com/questions/4740748/when-to-use-common-table-expression-cte
• https://msdn.microsoft.com/en-us/library/ms175972.aspx
• https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx