如何用MySQL查询用户最近连续签到天数？

Question

如何用MySQL查询用户最近连续签到天数？一个常见问题是：当用户签到记录存在断签情况时，如何准确识别“最近连续签到”的起始点并排除历史不连续片段？例如，用户过去有连续7天签到，中断1天后又连续签到5天，应返回5而非7。难点在于需按时间倒序判断连续性，结合变量或窗口函数标记断点，仅统计最后一次连续段。实际实现中易出现逻辑错误或性能瓶颈，尤其在大数据量下缺乏索引优化时查询变慢。

Answer 1

一、问题背景与业务场景分析

在用户成长体系或积分系统中，签到功能是常见设计。为了激励用户每日活跃，系统常需统计“最近连续签到天数”。例如：某用户历史签到记录为连续7天，中断1天后再次连续签到5天，则当前应返回5而非7。

该需求的核心难点在于：

• 需按时间倒序判断连续性；
• 识别最后一次连续段的起始点；
• 排除历史不连续片段的影响；
• 避免因数据量大导致性能下降。

二、基础数据结构设计

假设我们有一个签到表 user_sign_in，其结构如下：

字段名	类型	说明
user_id	INT	用户ID
sign_date	DATE	签到日期
created_at	DATETIME	创建时间

示例数据（按 user_id = 1）：

user_id	sign_date
1	2024-03-01
1	2024-03-02
1	2024-03-03
1	2024-03-04
1	2024-03-05
1	2024-03-06
1	2024-03-07
1	2024-03-09
1	2024-03-10
1	2024-03-11
1	2024-03-12
1	2024-03-13

注意：3月8日断签，之后从9号开始新一轮连续签到，共5天。

三、解题思路演进：从变量法到窗口函数

解决此问题的关键是“分组连续区间”，然后取最新一组的长度。

步骤分解如下：

1. 将签到记录按日期降序排列；
2. 使用变量或窗口函数标记“是否断签”；
3. 通过差值生成“连续块标识”；
4. 对每个块计数，取第一个块（即最近）的天数。

四、MySQL实现方案对比

以下提供两种主流实现方式：

方案一：用户变量法（适用于 MySQL < 8.0）

SET @prev_date := NULL;
SET @group_id := 0;

SELECT 
    user_id,
    COUNT(*) AS consecutive_days
FROM (
    SELECT 
        user_id,
        sign_date,
        @group_id := IF(
            @prev_date IS NULL OR DATEDIFF(@prev_date, sign_date) = 1,
            @group_id,
            @group_id + 1
        ) AS grp,
        @prev_date := sign_date
    FROM user_sign_in
    WHERE user_id = 1
    ORDER BY sign_date DESC
) t
GROUP BY user_id, grp
ORDER BY sign_date DESC
LIMIT 1;
    

方案二：窗口函数法（MySQL 8.0+ 推荐）

WITH RankedSigns AS (
    SELECT 
        user_id,
        sign_date,
        LAG(sign_date) OVER (PARTITION BY user_id ORDER BY sign_date DESC) AS prev_date
    FROM user_sign_in
),
GroupedBlocks AS (
    SELECT 
        user_id,
        sign_date,
        SUM(CASE WHEN prev_date IS NULL OR DATEDIFF(prev_date, sign_date) != 1 THEN 1 ELSE 0 END)
        OVER (PARTITION BY user_id ORDER BY sign_date DESC ROWS UNBOUNDED PRECEDING) AS block_id
    FROM RankedSigns
)
SELECT 
    user_id,
    COUNT(*) AS consecutive_days
FROM GroupedBlocks
GROUP BY user_id, block_id
ORDER BY MAX(sign_date) DESC
LIMIT 1;
    

五、性能优化建议

当用户量大、签到记录多时，查询性能至关重要。以下是关键优化点：

• 索引策略：在 (user_id, sign_date) 上建立复合索引，确保排序和过滤高效；
• 分区表：按月或年对签到表进行分区，减少扫描范围；
• 缓存层：将最近连续签到结果缓存在 Redis 中，定时更新；
• 物化视图：定期计算并存储每个用户的当前连续天数。

六、流程图：逻辑处理过程

graph TD
            A[获取用户签到记录] --> B[按日期降序排序]
            B --> C{是否为第一条记录?}
            C -->|是| D[初始化 group_id=0]
            C -->|否| E[比较与上一条日期差]
            E --> F{相差是否为1天?}
            F -->|是| G[保持当前 group_id]
            F -->|否| H[递增 group_id]
            G --> I[标记所属连续块]
            H --> I
            I --> J[按 group_id 分组统计]
            J --> K[取最大日期组的计数]
            K --> L[返回最近连续签到天数]

七、边界情况与测试用例

为验证算法鲁棒性，考虑以下测试场景：

场景	输入数据	预期输出
完全连续	连续10天	10
最近无签到	最后签到在3天前	0
仅一天签到	今天签到	1
昨日断签	昨天未签，前天有签	0
跨月连续	2月28日~3月3日	4
多用户并发	多个 user_id 混合数据	按 user_id 独立计算