Azure Stream Analytics · Counting people in/out from a room

Question

I'm trying to make a query in Stream Analytics in order to send the output to PowerBI.

I need three things:

• Counting people (location=Entrada - location=Salida). That is the load factor of the room, the people that has entered minus people has left the room.
• Show the % capacity (100 people max in the room)
• Evolution line

I'm new to Stream Analytics queries, I find it quite complicated.

This is my IoT Hub input dataset:

And this is my query:

    SELECT System.Timestamp AS WindowEnd,
           COUNT(*)
    INTO [output-pbi]
    FROM [input-rfid]
        GROUP BY HoppingWindow(second,3600,1)

In this example, I need to show that there is 1 person in the room (1% of total capacity) and a evolution line similar as this (always in real time):

I don't know if I'm using the correct Window function.

Thank you very much.

Answer 1

You can use the query below for getting the results you expected.

WITH CountPeople AS (
    SELECT
        System.Timestamp AS WindowEnd,
        COUNT(*) AS PeopleCount, location
    FROM [input1]
    WHERE location = 'Entrada' OR location = 'Salida'
    GROUP BY HoppingWindow(minute, 1, 1), location
)

SELECT
    CP.WindowEnd,
    SUM(CASE WHEN CP.location = 'Entrada' THEN CP.PeopleCount ELSE 0 END) -
    SUM(CASE WHEN CP.location = 'Salida' THEN CP.PeopleCount ELSE 0 END) AS LoadFactor,
    (SUM(CASE WHEN CP.location = 'Entrada' THEN CP.PeopleCount ELSE 0 END) -
    SUM(CASE WHEN CP.location = 'Salida' THEN CP.PeopleCount ELSE 0 END))*100/100 AS CapacityPercentage
INTO [output1]
FROM CountPeople CP
GROUP BY CP.WindowEnd, TumblingWindow(second, 1)

Here, I am getting CountPeople every second for the past 1 minute, grouped by the locations.

Then, I am taking the difference between the records with location Entrada and Salida.

Output:

Again, you need to alter the query according to your data, like UID.