如何使用KQL按时间偏移聚合获取页面打开事件对应关闭事件的timeOnPageMs值

核心思路 #

不需要自定义UDF，直接通过内置的join算子结合事件属性匹配即可实现需求，核心逻辑是拆分打开/关闭两类事件后关联匹配：

• 先拆分出两类事件子集：page_open（timeOnPageMs=0的页面打开事件）、page_close（timeOnPageMs>0的页面关闭事件）
• 按pageUrl关联两类事件，同时关联用户唯一标识（如果你的表有userId/sessionId这类会话字段必须加上，避免不同用户的页面事件错误匹配）
• 对同一个页面的打开/关闭事件，通过时间序匹配，取打开事件之后最近的关闭事件作为对应项

完整查询代码 #

pageEvent
// 拆分出页面打开事件
| where timeOnPageMs == 0
| project open_time = timestamp, pageUrl, userId, eventType // 若没有userId可以去掉该字段
| join kind=leftouter (
    // 子查询拆分出页面关闭事件
    pageEvent
    | where timeOnPageMs > 0
    | project close_time = timestamp, pageUrl, userId, timeOnPageMs
) on pageUrl, userId
// 只保留关闭事件发生在打开事件之后的匹配项
| where close_time > open_time
// 对同一个打开事件，取最近的关闭事件的停留时长
| summarize arg_min(close_time, timeOnPageMs) by open_time, pageUrl, userId
// 按需重命名/保留字段
| project pageUrl, open_timestamp = open_time, timeOnPageMs

特殊场景适配 #

如果需要保留没有对应关闭事件的打开记录（比如用户未关闭页面就退出的情况），可以调整查询逻辑避免过滤无效匹配：

pageEvent
| where timeOnPageMs == 0
| project open_time = timestamp, pageUrl, userId
| join kind=leftouter (
    pageEvent
    | where timeOnPageMs > 0
    | project close_time = timestamp, pageUrl, userId, timeOnPageMs
) on pageUrl, userId
| where isempty(close_time) or close_time > open_time
| summarize arg_min(close_time, timeOnPageMs) by open_time, pageUrl, userId
| project pageUrl, open_timestamp = open_time, timeOnPageMs = coalesce(timeOnPageMs, 0)

如果你的采集数据没有用户/会话标识，去掉查询中所有userId相关的字段，仅按pageUrl和时间序匹配即可。

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