如何在Presto SQL中基于心跳数据计算用户会话活跃时长

实现思路 #

• 这是典型的连续区间合并计算场景，核心是通过窗口函数给每个连续活跃的分钟段打分组标签，再按标签聚合计算每个段的时长后求和

完整Presto SQL语句 #

WITH step1 AS (
    -- 取每个用户上一次上报的分钟
    SELECT 
        user_id,
        minute_of_day,
        LAG(minute_of_day, 1) OVER (PARTITION BY user_id ORDER BY minute_of_day) AS prev_minute
    FROM user_heartbeat -- 替换为你的实际表名
),
step2 AS (
    -- 标记新活跃区间的起始行
    SELECT
        user_id,
        minute_of_day,
        CASE WHEN prev_minute IS NULL OR minute_of_day - prev_minute > 1 THEN 1 ELSE 0 END AS is_new_session
    FROM step1
),
step3 AS (
    -- 累加生成活跃区间的分组ID
    SELECT
        user_id,
        minute_of_day,
        SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY minute_of_day) AS session_id
    FROM step2
),
session_duration AS (
    -- 计算每个活跃区间的时长
    SELECT
        user_id,
        MAX(minute_of_day) - MIN(minute_of_day) AS duration
    FROM step3
    GROUP BY user_id, session_id
)
-- 聚合每个用户的总活跃时长
SELECT
    user_id AS "User ID",
    SUM(duration) AS "Total Minutes"
FROM session_duration
GROUP BY user_id
ORDER BY user_id;

若你的表字段名不是user_id、minute_of_day，替换为实际字段名即可

结果验证 #

运行后输出结果完全匹配需求：

内容的提问来源于stack exchange，提问作者reindeer.eleventy