Search code examples
azure-stream-analytics

In Azure Stream Analytics grouping by sliding window grouping from multiple windows

I have the following queries in azure stream analytics...DataInput returns only 1 row (I output to a blob and can see it)...but looks like the CalcData is processing a lot more rows...It looks like it is taking rows from multiple sliding windows. When I have events spaced out, then I get the right output, but when events occur next to each other, the sliding window doesn't seem to be right

WITH DataInput AS 1 AS (SELECT 
    CONCAT(fqn, '_HealthIndex') AS fqn, 
    value as value,
    count(value) as cntvalue
FROM DataInput
GROUP BY fqn,value,SlidingWindow(Duration( hour, 8 ))
),
CalcData AS
(SELECT 
fqn,
count(*) as records,
sum(value) as alm,
100 - sum(case when cast(value as bigint)=19 and cast(cntvalue as bigint)    >    1 then 5  
  when cast(value as bigint)=23 and cast(cntvalue as bigint) > 1 then 5 
 when cast(value as bigint)=64 and cast(cntvalue as bigint) > 1 then 10 
 when cast(value as bigint)=72 and cast(cntvalue as bigint) > 1 then 10 
 when cast(value as bigint)=77 and cast(cntvalue as bigint) > 0 then 5 
 when cast(value as bigint)=78 and cast(cntvalue as bigint) > 0 then 5 
 when cast(value as bigint)=83 and cast(cntvalue as bigint) > 16 then 5 
 when cast(value as bigint)=84 and cast(cntvalue as bigint) > 16 then 5 
 when cast(value as bigint)=91 and cast(cntvalue as bigint) > 0 then 30 
 when cast(value as bigint)=92 and cast(cntvalue as bigint) > 1 then 5 
 when cast(value as bigint)=101 and cast(cntvalue as bigint) > 1 then 15    else 0 end ) as value 
,System.TimeStamp as t 
from DataInput1 group by fqn,SlidingWindow(Duration( hour, 8 ))
 )

Any insight on why the CalcData is not taking only the output from DataInput would be greatly appreciated

Solution

  • The CalcData step is only taking in data from the output of the DataInput1 step, however you are grouping your events via sliding window in DataInput1. A sliding window creates an output every time an event enters or leaves the window. Thus, you can have an event included in multiple sliding windows. In order to make sure that an event is included in at most one window, consider using grouping by tumbling window instead.