MySQL查询用户最新习惯连续完成天数异常：返回1而非预期4

需求：获取用户从当前日期往前的最新连续完成习惯的天数（streak），注意不是最长连续记录。
现有测试数据的完成日期为：2023-09-14、2023-09-15、2023-09-16、2023-09-17，预期查询结果为4，但以下SQL始终返回1，请求排查原因：

SELECT s.habit, MAX(s.streak) AS currentStreak
  FROM ( SELECT IF(@prev = o.habit_id AND o.date = @prevDate + INTERVAL 1 DAY
                  , @streak := @streak + 1
                  , @streak := 1
                ) AS streak
              , @prev := o.habit_id AS habit
           FROM ( SELECT t.habit_id
                       , DATE(t.created_at) AS `date`
                    FROM habit_progress_logs t
                   CROSS
                    JOIN (SELECT @prev := NULL, @prevDate := NULL, @streak := 1) i
                   GROUP BY t.habit_id, DATE(t.created_at)
                   ORDER BY t.habit_id, DATE(t.created_at)
                ) o
       ) s
  WHERE s.habit = 4
 GROUP BY s.habit

• 未更新日期变量：子查询中仅更新了记录habit_id的@prev变量，但完全没更新@prevDate。每次判断o.date = @prevDate + INTERVAL 1 DAY时，@prevDate始终是初始的NULL，导致连续日期的判断条件永远不成立，@streak每次都被重置为1。
• 逻辑不符合需求：原SQL用MAX(s.streak)获取最大连续天数，这会返回历史最长记录，而非需求要求的最新连续记录。

方案1：使用窗口函数（更简洁现代） #

通过计算日期分组标识，筛选出最新的连续日期段并统计天数：

SELECT habit_id, COUNT(*) AS currentStreak
FROM (
    SELECT 
        t.habit_id,
        DATE(t.created_at) AS `date`,
        -- 连续日期会拥有相同的group_id
        DATEDIFF(CURDATE(), DATE(t.created_at)) 
        - ROW_NUMBER() OVER (PARTITION BY t.habit_id ORDER BY DATE(t.created_at) DESC) AS group_id
    FROM habit_progress_logs t
    WHERE t.habit_id = 4
    GROUP BY t.habit_id, DATE(t.created_at)
) AS sub
-- 仅保留最新的连续日期组（group_id最小的分组）
WHERE group_id = (
    SELECT MIN(group_id) 
    FROM (
        SELECT 
            DATEDIFF(CURDATE(), DATE(t.created_at)) 
            - ROW_NUMBER() OVER (PARTITION BY t.habit_id ORDER BY DATE(t.created_at) DESC) AS group_id
        FROM habit_progress_logs t
        WHERE t.habit_id = 4
        GROUP BY t.habit_id, DATE(t.created_at)
    ) AS g
)
GROUP BY habit_id;

方案2：修正原变量逻辑 #

保留原变量思路，补充更新@prevDate变量，并调整排序取最新连续值：

SELECT habit, streak AS currentStreak
FROM (
    SELECT 
        o.habit_id AS habit,
        @streak := IF(@prev_habit = o.habit_id AND o.date = @prev_date + INTERVAL 1 DAY, @streak + 1, 1) AS streak,
        @prev_habit := o.habit_id,
        @prev_date := o.date
    FROM (
        SELECT t.habit_id, DATE(t.created_at) AS `date`
        FROM habit_progress_logs t
        WHERE t.habit_id = 4
        GROUP BY t.habit_id, DATE(t.created_at)
        ORDER BY t.habit_id, DATE(t.created_at) DESC -- 倒序遍历，优先处理最新日期
    ) o
    CROSS JOIN (SELECT @prev_habit := NULL, @prev_date := NULL, @streak := 1) vars
) s
ORDER BY `date` DESC LIMIT 1; -- 取最后一条的streak值，即最新连续天数

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