如何从log_event表匹配应用开闭事件计算用户平均使用时长

我们可以通过窗口函数匹配相邻开闭事件的方式实现需求，核心逻辑是为每个用户的打开事件匹配同用户下后续最近的关闭事件，排除未正常关闭的会话后统计平均时长。

具体SQL实现 #

WITH user_event_ranked AS (
    SELECT
        user_id,
        event_date_time,
        LOWER(event) AS event,
        -- 匹配同用户下、当前事件之后第一条关闭事件的时间戳
        MIN(CASE WHEN LOWER(event) = 'closed app' THEN event_date_time END) 
            OVER (PARTITION BY user_id ORDER BY event_date_time 
                  ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS next_close_time
    FROM log_event
)
SELECT
    user_id,
    COUNT(*) AS total_valid_sessions,
    AVG(next_close_time - event_date_time) AS avg_usage_duration_seconds
FROM user_event_ranked
WHERE event = 'opened app' -- 仅以打开事件作为会话统计起点
  AND next_close_time IS NOT NULL -- 过滤无对应关闭事件的无效会话
GROUP BY user_id;

逻辑解释 #

• 首先通过CTE处理事件名称大小写兼容问题，同时用带范围限制的窗口聚合，为每条记录匹配同用户后续最近的关闭事件时间戳
• 过滤得到所有有效的打开事件记录，排除没有对应关闭事件的异常会话（例如未上报关闭事件的会话）
• 计算单会话时长后按用户分组，得到每个用户的平均使用时长
• 如果需要按天/按月维度统计，只需新增时间戳转日期的逻辑，将日期字段加入GROUP BY子句即可

示例数据运行结果 #

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