Azure Stream Analytics实现房间人员统计及容量占比查询需求

窗口选择说明 #

你当前使用的HoppingWindow适合统计重叠时间段内的人员进出趋势，但如果需要精准显示实时当前房间人数，更推荐用基于事件时间的累计聚合；如果侧重时间段内的人数变化趋势，HoppingWindow调整参数后也能满足需求，以下分两种场景给出实现方案。

功能实现完整查询 #

场景1：实时累计当前人数（精准显示当前房间内人数） #

该方案通过累计聚合计算从启动以来的进出差值，输出实时更新的人数和容量占比：

SELECT
    EventTime,
    -- 计算当前房间人数：Entrada加1，Salida减1，累计所有历史事件
    SUM(CASE 
        WHEN location = 'Entrada' THEN 1 
        WHEN location = 'Salida' THEN -1 
        ELSE 0 
    END) OVER (ORDER BY EventTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CurrentPeopleCount,
    -- 计算容量占比（转FLOAT避免整数除法）
    CAST(
        SUM(CASE 
            WHEN location = 'Entrada' THEN 1 
            WHEN location = 'Salida' THEN -1 
            ELSE 0 
        END) OVER (ORDER BY EventTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS FLOAT
    ) / 100 * 100 AS CapacityUsagePercentage
INTO PowerBIOutput
FROM IoTHubInput
WHERE location IN ('Entrada', 'Salida') -- 过滤无效位置数据

场景2：HoppingWindow趋势统计（查看时间段内人数变化） #

如果需要观察周期性的人数变化趋势，调整HoppingWindow的窗口大小和步长（示例为10分钟窗口，每5分钟输出一次统计结果）：

SELECT
    System.Timestamp() AS WindowEndTime, -- 窗口结束时间，用于PowerBI时间轴
    SUM(CASE WHEN location = 'Entrada' THEN 1 ELSE 0 END) AS TotalEntrances,
    SUM(CASE WHEN location = 'Salida' THEN 1 ELSE 0 END) AS TotalExits,
    -- 该窗口内的人数净变化（若要累计总人数，需结合历史数据存储）
    (SUM(CASE WHEN location = 'Entrada' THEN 1 ELSE 0 END) - SUM(CASE WHEN location = 'Salida' THEN 1 ELSE 0 END)) AS CurrentPeopleCount,
    -- 容量占比计算
    CAST(
        (SUM(CASE WHEN location = 'Entrada' THEN 1 ELSE 0 END) - SUM(CASE WHEN location = 'Salida' THEN 1 ELSE 0 END)) AS FLOAT
    ) / 100 * 100 AS CapacityUsagePercentage
INTO PowerBIOutput
FROM IoTHubInput
WHERE location IN ('Entrada', 'Salida')
GROUP BY HoppingWindow(minute, 10, 5) -- 窗口大小10分钟，步长5分钟

PowerBI实时折线图配置 #

1. 在Stream Analytics中创建PowerBI输出，指定目标工作区和数据集名称
2. 在PowerBI中打开对应数据集，添加折线图可视化：
   • X轴选择时间字段（EventTime或WindowEndTime）
   • Y轴选择CurrentPeopleCount或CapacityUsagePercentage
3. 可额外添加卡片组件，直接显示当前人数和容量占比，实现实时监控

注意事项 #

• 确保IoT Hub输入的EventTime为事件发生的真实时间，避免数据乱序导致统计偏差
• 场景1的累计聚合会在Stream Analytics重启后重置，若需持久化历史累计值，需结合SQL Database等外部存储保存累计基数，再与实时数据合并
• HoppingWindow的窗口大小和步长可根据需求调整：步长越小，更新频率越高；窗口大小越大，统计的时间范围越广

内容的提问来源于stack exchange，提问作者user3868641