如何编写SQL查询筛选优质用户并计算其平均会话时长

原查询存在的问题 #

• 语法错误：BETWEEN 操作符需要指定起始和结束两个时间边界，原查询仅传入了一个参数，无法执行
• 逻辑偏差：没有按周维度分组统计会话数，统计的是用户全周期的总会话数，不符合「每周平均会话数不少于4次」的优质用户定义
• 需求匹配度不足：未计算用户的平均会话时长，也没有加过滤条件筛选符合要求的优质用户

正确查询语句 #

WITH user_weekly_sessions AS (
    -- 统计每个用户每自然周的会话数量
    SELECT 
        userId,
        YEARWEEK(data_timestamp) AS week_tag,
        COUNT(DISTINCT Id) AS weekly_session_cnt
    FROM sessions
    GROUP BY userId, YEARWEEK(data_timestamp)
),
high_quality_users AS (
    -- 计算用户周平均会话数，筛选优质用户
    SELECT userId
    FROM user_weekly_sessions
    GROUP BY userId
    HAVING AVG(weekly_session_cnt) >= 4
)
-- 输出优质用户ID和对应平均会话时长
SELECT 
    s.userId,
    AVG(s.duration) AS avg_session_duration
FROM sessions s
INNER JOIN high_quality_users hqu ON s.userId = hqu.userId
GROUP BY s.userId;

逻辑说明 #

• 先通过user_weekly_sessions CTE按用户+自然周维度聚合，统计每个用户每周的会话数，YEARWEEK函数会自动将时间转换为「年+周」的唯一标识，避免跨年周统计错误
• 再通过high_quality_users CTE基于周维度统计结果，计算每个用户的周平均会话数，过滤出周均≥4次的优质用户
• 最后关联原sessions表，聚合计算得到优质用户的平均会话时长

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