移动端用户会话总时长计算SQL实现及逻辑咨询

我们需要计算移动端用户会话的总参与时长，系统会记录open_the_app（打开应用）、application_foreground（回到前台）、application_background（进入后台）以及其他事件（如screen_view）。计算规则如下：

• 首次活跃段：从open_the_app时间到第一个application_background时间
• 后续活跃段：每次application_foreground之后对应的下一个application_background时间，计算两者差值
• 总时长为所有活跃段的时长之和

示例表结构与数据 #

期望输出 #

通用SQL实现 #

WITH session_events AS (
    SELECT
        session_id,
        event_name,
        collector_tstamp,
        -- 标记每个后台事件对应的前置启动/前台事件
        LAG(CASE WHEN event_name IN ('open_the_app', 'application_foreground') THEN collector_tstamp END) 
            OVER (PARTITION BY session_id ORDER BY collector_tstamp) AS active_start_time
    FROM Session
    WHERE event_name IN ('open_the_app', 'application_foreground', 'application_background')
),
active_intervals AS (
    SELECT
        session_id,
        active_start_time,
        collector_tstamp AS active_end_time,
        -- 计算每个活跃段的秒数（标准SQL写法，可根据方言调整）
        EXTRACT(EPOCH FROM (collector_tstamp - active_start_time)) AS interval_seconds
    FROM session_events
    WHERE event_name = 'application_background' AND active_start_time IS NOT NULL
)
SELECT
    session_id,
    CONCAT(SUM(interval_seconds), ' seconds') AS Duration
FROM active_intervals
GROUP BY session_id;

逻辑解释 #

1. 筛选关键事件：先过滤出和会话活跃相关的核心事件，排除浏览页面等无关事件，减少计算量。
2. 匹配活跃时段：用LAG()窗口函数按时间顺序，为每个后台事件匹配最近的前置启动/前台事件时间，作为该活跃段的起始点。
3. 计算单段时长：对每个后台事件，计算其与对应起始事件的时间差并转换为秒。
4. 求和总时长：按会话ID分组，将所有活跃段的时长相加，格式化输出为指定的文本格式。

方言适配说明 #

• MySQL环境：将时间差计算替换为TIMESTAMPDIFF(SECOND, active_start_time, collector_tstamp)
• SQL Server环境：将时间差计算替换为DATEDIFF(SECOND, active_start_time, collector_tstamp)

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