Azure stream analyics - Compiling query failed

Question

When I try to use the last function (https://msdn.microsoft.com/en-us/library/azure/mt421186.aspx). I get the following error:

Compiling query failed.

SELECT
    deviceId
    ,System.TimeStamp as timestamp
    ,avg(events.externaltemp) as externaltemp
    ,LAST(System.Timestamp) OVER (PARTITION BY deviceId LIMIT DURATION(second, 1) when [externaltemp] is not null ) as Latest
INTO
    [powerBI]
FROM
    [EventHub] as events timestamp by [timestamp]

GROUP BY deviceId, TumblingWindow(second,1)

My last function looks very similar to the one in the msdn sample, so I'm not sure why there is a problem.

Answer 1

You are using [externaltemp] in your query, but it is not included in group by. That is the reason. And "last" function does not allow aggregates inside it, so below wouldn't work as well

LAST(System.Timestamp) OVER (PARTITION BY deviceId LIMIT DURATION(second, 1) when avg([externaltemp]) is not null ) as Latest

It can be achieved by splitting the query into two steps, like this

with DeviceAggregates
as
(
SELECT
    System.TimeStamp as [Timestamp],
    deviceId,
    avg(events.externaltemp) as [externaltemp]
FROM
    [EventHub] as events timestamp by [timestamp]
GROUP BY 
    deviceId, 
    TumblingWindow(second,1)
),

DeviceAggregatesWithLast as
(
select 
    *,
    last([Timestamp]) over (partition by deviceId limit duration(second,1) when [externaltemp] is not null) [LastTimeThereWasANonNullTemperature] 
from 
    DeviceAggregates
)


select * 
INTO
    [powerBI]
from
    DeviceAggregatesWithLast