如何在ClickHouse中统计每个用户的周度连续会话最大streak值

核心思路 #

这是典型的连续区间统计问题，按以下步骤实现即可：

• 周粒度去重：规则要求每周只要有1次会话就算活跃，所以先按用户+周维度聚合，去掉同一周内的多余会话记录，用toMonday()函数将会话时间转换为当周周一的日期，作为周的唯一标识。
• 连续段标记：对每个用户的活跃周按时间升序排序，用窗口函数row_number()生成序号，连续的周满足「当周周一日期减去（序号-1）周的时间值相同」，这个相同的值就作为同一连续活跃段的分组标记。
• 计算单段长度：按用户和连续段标记分组，统计每组的记录数就是该段的连续活跃周数。
• 取最大streak：按用户分组，取最大的单段长度即为该用户的最大连续活跃streak。

完整SQL代码 #

SELECT
    userid,
    max(streak) AS max_streak
FROM (
    SELECT
        userid,
        group_id,
        count(*) AS streak
    FROM (
        SELECT
            userid,
            week_start,
            week_start - INTERVAL (row_number() OVER (PARTITION BY userid ORDER BY week_start) - 1) WEEK AS group_id
        FROM (
            SELECT
                userid,
                toMonday(session_ts) AS week_start
            FROM sessions
            GROUP BY userid, week_start
        ) AS t1
    ) AS t2
    GROUP BY userid, group_id
) AS t3
GROUP BY userid

结果说明 #

针对你提供的测试数据，运行上述SQL后输出结果为：

┌─userid─┬─max_streak─┐
│ user1  │          3 │
└────────┴────────────┘

和你预期的输出结果一致。

注意事项 #

• 如果业务中周的起始日不是周一，可以把toMonday(session_ts)替换为对应的周截断逻辑，比如要以周日为周起始，可使用date_trunc('week', session_ts, 'Sunday')。
• 该逻辑天然支持多用户批量计算，无需修改即可直接对全量用户数据执行。

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