MySQL 8.1.0中如何基于Timestamp计算用户特定房间连续活跃天数

针对你的需求，利用MySQL 8.1支持的窗口函数可以实现高效的连续活跃天数计算，避免了传统自连接的低效问题，具体方案如下：

核心SQL语句 #

WITH daily_active AS (
    -- 去重：获取用户在目标房间的所有活跃日期（单日多次活跃仅保留1条）
    SELECT DATE(timestamp) AS active_date
    FROM milestone
    WHERE user_id = 2 AND room_id = 552
    GROUP BY user_id, room_id, DATE(timestamp)
),
streak_groups AS (
    -- 给活跃日期分组：连续日期会被归为同一组
    SELECT
        active_date,
        DATE_SUB(active_date, INTERVAL ROW_NUMBER() OVER (ORDER BY active_date) DAY) AS group_id
    FROM daily_active
),
current_streak AS (
    -- 统计包含当前日期的连续组的天数
    SELECT COUNT(*) AS streak_days
    FROM streak_groups
    WHERE group_id = (
        SELECT group_id
        FROM streak_groups
        WHERE active_date = CURDATE()
    )
)
-- 处理今日未活跃的情况，返回0而非空值
SELECT COALESCE(streak_days, 0) AS current_continuous_streak
FROM current_streak;

方案说明 #

1. daily_active 阶段：通过GROUP BY DATE(timestamp)直接去重用户单日的多次活跃记录，仅保留活跃日期，比你之前取MIN(timestamp)的方式更简洁（若无需具体活跃时间，仅日期即可满足需求）。
2. streak_groups 阶段：利用ROW_NUMBER()窗口函数给按时间排序的活跃日期编号，再用DATE_SUB将每个日期减去其编号对应的天数。连续的日期会得到相同的group_id（比如2024-05-20、2024-05-21、2024-05-22这三个连续日期，计算后group_id均为2024-05-19），中断的日期则会生成新的group_id。
3. current_streak 阶段：找到包含当前日期（CURDATE()）的分组，统计该分组内的记录数，即为当前的连续活跃天数。若用户今日未活跃，current_streak会返回空，通过COALESCE将结果转为0。

性能优化建议 #

为避免全表扫描、提升查询效率，给milestone表创建复合索引：

CREATE INDEX idx_user_room_timestamp ON milestone(user_id, room_id, timestamp);

该索引可覆盖WHERE条件和GROUP BY的字段，大幅缩短查询耗时。

边界情况处理 #

• 用户今日首次活跃：返回1
• 用户连续多日活跃且今日仍活跃：返回连续天数总和
• 用户今日未活跃：返回0
• 用户之前存在活跃中断：仅统计最近一次中断后的连续天数

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