CODEWITHSUNDEEP
azureazure-stream-analytics
silent
silent

Reputation: 16128

Azure Stream Analytics - Get field from last event in group?

I have the following query which combines events from two inputs.

WITH combined AS (
SELECT a.deviceId, temperature, 'a' as source FROM [inputA] a timestamp by a.devicetime
UNION
SELECT b.deviceId, temperature, 'b' as source  FROM [inputB] b timestamp by b.devicetime
)

SELECT c.deviceId, system.Timestamp as 'windowend', avg(c.temperature) as 'avg_temperature' 
INTO [ehA-output] 
FROM combined c
GROUP BY c.deviceId, TumblingWindow(Duration(second, 10), Offset(second, 1))

What I have not figured out yet: How could I add the field "source" to the output in the second part whereas the value should be taken from the last event in the window group?

So I imagine something like pseudo-code (note: LAST() is actually an existing function but not for this purpose as far as I can tell).

SELECT c.deviceId, ..., LAST(source) as sourceOfLastEvent 
...

Upvotes: 1

Views: 416

Answers (1)

kgalic
kgalic

Reputation: 2654

Try the following query(please adjust your time window, as I changed it for the testing purpose):

WITH combined AS (
    SELECT a.deviceId, temperature, 'a' as source, a.deviceTime FROM [input] a timestamp by a.devicetime
    UNION
    SELECT b.deviceId, temperature, 'b' as source, b.deviceTime  FROM [input1] b timestamp by b.devicetime
),
    result AS (
    SELECT c.deviceId, system.Timestamp as 'windowend', avg(c.temperature) as 'avg_temperature', topone() over (order by deviceTime desc) 
    FROM combined c
    GROUP BY c.deviceId, TumblingWindow(Duration(minute, 1), Offset(second, 1))
)

select result.topone.source, result. *
into output
from result

Here you can see that in the first subquery, deviceTime is propagated and in the second subquery, we are taking topone element sorted by the device time descending.

This should get the last event from the time window, but in a way that does not change the GROUP BY clause, because topone() function is an aggregate expression.

And finally in the result, from the topone object, we are just propagating source property to the output.

Side note: Based on my tests, it seems like UNION clause here requires source 'a' and source 'b' to have events in order to produce the output, which might be critical if you have a realtime requirement for these events, and for example only input source 'a' is getting an events.

Upvotes: 1

Related Questions