Azure Streaming Analytics Lag() Function with example

The Lag() function in Azure Streaming Analytics is documented here.  I have been asked a few times now about Lag() and if it can refer to the projected values of another Lag() function in the previous event.  Let’s see.

 

The Event

My events are really simple and their schema is:

 

{
type: "string",
readingtaken: "datetime",
reading: 0
}

 

The Query

 

My query in ASA is

 

select
type,
readingtaken,
case
when reading IS NULL then LAG(reading,1,0) OVER (PARTITION BY type LIMIT DURATION(hh,24))
else reading
end as reading
into
dest
from
readings
where
type = 'temperature'

 

The Data Flow

 

Here are my events as they flow through the stream (these are the raw events and not what is projected rom the ASA query)

 

 

 

Here is what you as the consumer will see i.e. the projected events.

 

 

 

The Explanation

 

The event that is of interest is E3.

 

Here is an explanation of the projected events

 

E1 has the reading attribute and value as does E1(Projected)

E2 does not have the attribute and value but E2(Projected) uses the value from E1.

E3 again does not have the attribute and value.  E3 looks to E2 and not E2(Projected).  In E2 the value of reading is NULL and as per the documentation this is used.

 

 

Summary

Using Lag() you cannot or rather do not refer to the projected value of the previous event.  You refer to the actual event itself.  In the example above the reading attribute had an explicit value of NULL in E2 so that is what was used in E3