如何在Azure Stream Analytics中基于用户与事件创建时间窗口？

需求 #

用户可执行两类操作：进入房间、离开房间。需要通过Azure Stream Analytics生成数据表，记录进入房间后30分钟内未离开的用户信息。

数据结构 #

事件流的数据格式如下：

{
  "user_id": "user_id",
  "event_name": "Enter the room" | "Leave the room",
  "event_timestamp": "yyyy-mm-dd hh:mi:ss"
}

示例输入数据 #

期望输出数据 #

遇到的问题 #

尝试过使用窗口函数，但它只能按FROM子句中定义的时间戳分组，生成的时间窗口无法关联具体事件及触发用户，无法满足需求。

解决方案 #

可以通过LEFT JOIN关联进入事件与对应的离开事件，限定时间范围为进入事件后30分钟内，筛选出符合条件的记录。

具体查询语句 #

SELECT
  e.user_id,
  e.event_name,
  e.event_timestamp
INTO
  [你的输出目标]
FROM
  [你的输入流名称] e
LEFT JOIN
  [你的输入流名称] l
ON
  e.user_id = l.user_id
  AND l.event_name = 'Leave the room'
  AND l.event_timestamp BETWEEN e.event_timestamp AND DATEADD(minute, 30, e.event_timestamp)
WHERE
  e.event_name = 'Enter the room'
  AND (l.user_id IS NULL OR DATEDIFF(minute, e.event_timestamp, l.event_timestamp) > 30)

逻辑说明 #

1. 将输入流分别作为进入事件表e和离开事件表l，按用户ID进行关联
2. 限定离开事件必须发生在进入事件之后，且不晚于进入事件的30分钟后
3. 筛选条件：
   • 没有找到对应离开事件的进入记录（用户一直未离开）
   • 找到的离开事件距离进入时间超过30分钟（进入后30分钟内未离开）

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