0

I need to find clients that appear at least 3 minutes in 5 minutes Tumbling window in Azure Stream Analytics.

Below code can find the number of people appear in 5 minutes window.

SELECT  
        apMac,
        COUNT(Distinct([clientMac])) AS [numberofClientsPerFiveMinutes],
        AVG(rssi) AS [rssiAverage],
        System.TimeStamp AS [EventTimestampUTC],
        UDF.melbournetime(System.TimeStamp) AS [EventTimestampLocalTime]
INTO    [meraki-aggregated-powerbi]
FROM    [ExplodedData]
GROUP BY    apMac,
            TumblingWindow(Minute, 5)

However, I want to count the people that have been in this list for at least 3 minutes. Using their first appearance and last appearance (base on their query time).

The problem is that I'm not aware of a code style in Stream Analytics

Guru Pasupathy
  • 440
  • 1
  • 4
  • 13
shnpsm
  • 11
  • 4

1 Answers1

1

I found the solution with DATEDIFF as below. You need to group by a person and then subtract the maximum from its minimum seen time.

    SELECT
        apmac,
        COUNT(DISTINCT ([clientmac])) AS [SeenPassengerNumberInTimeRange],
        ABS(DATEDIFF(MINUTE, MAX(seenTimeLocal), MIN(seenTimeLocal))) AS RangeTimeMinute,
        EventTimestampLocalTime AS EventTimestampLocalDateTime
    FROM [explodeddata]
    GROUP BY 
        apmac,
        EventTimestampLocalTime,
        Tumblingwindow(minute, 5) -- 5 min window size
    HAVING 
     ABS(DATEDIFF(MINUTE, MAX(seenTimeLocal), MIN(seenTimeLocal))) >= 2 -- at least 2 min

)
shnpsm
  • 11
  • 4