主页 / coding / 问题 / 76930264
Accepted
ALS
Asked: 2023-08-18 22:32:01 +0800 CST

MySQL：获取连续记录的数量（日期）？

  • 772

需要获取用户连续完成一项习惯（连续）的天数。这意味着最近的连续记录（今天倒退），而不一定是最长的连续记录。

插入以下日期后，我预计 4

  • 2023-09-14
  • 2023-09-15
  • 2023-09-16
  • 2023-09-17

这个查询始终返回 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

带有模式和数据的 SQLFiddle

mysql

1 个回答

  1. Best Answer

    您忘记了为每一行进行设置@prevDate，并且您返回的是最大条纹而不是最近的条纹。

    如果您使用 MySQL >= 8.0，您可以使用窗口函数，而不是表达式中已弃用的用户变量设置：

    WITH streaks AS (
    SELECT
        habit_id,
        DATE(created_at) AS dt,
        DATE(created_at) - INTERVAL DENSE_RANK() OVER (PARTITION BY habit_id ORDER BY DATE(created_at) ASC) DAY AS grp
    FROM habit_progress_logs
    WHERE habit_id = 4
),
streaks2 AS (
    SELECT
        habit_id,
        grp,
        COUNT(DISTINCT dt) AS length,
        ROW_NUMBER() OVER (PARTITION BY habit_id ORDER BY grp DESC) AS rn
    FROM streaks
    GROUP BY habit_id, grp
)
SELECT habit_id, length AS currentStreak
FROM streaks2
WHERE rn = 1;

    第一个CTE使用DENSE_RANK()为我们提供了日期序列分组（rnk添加用于说明）：

    习惯ID dt rnk 组
    4 2023-07-14 1 2023-07-13
    4 2023-07-15 2 2023-07-13
    4 2023-07-16 3 2023-07-13
    4 2023-07-17 4 2023-07-13
    4 2023-07-19 5 2023-07-14
    4 2023-08-20 6 2023-08-14
    4 2023-08-21 7 2023-08-14
    4 2023-08-23 8 2023-08-15
    4 2023-08-24 9 2023-08-15
    4 2023-08-25 10 2023-08-15
    4 2023-08-26 11 2023-08-15
    4 2023-08-27 12 2023-08-15
    4 2023-08-28 13 2023-08-15
    4 2023-09-13 14 2023-08-30
    4 2023-09-14 15 2023-08-30
    4 2023-09-15 16 2023-08-30
    4 2023-09-15 16 2023-08-30
    4 2023-09-16 17 号 2023-08-30
    4 2023-09-17 18 2023-08-30

    第二个CTE执行 GROUP BY 并添加ROW_NUMBER()：

    习惯ID 组 长度 rn
    4 2023-08-30 5 1
    4 2023-08-15 6 2
    4 2023-08-14 2 3
    4 2023-07-14 1 4
    4 2023-07-13 4 5

    如果您陷入 MySQL < 8.0 的困境：

    SELECT habit_id, COUNT(*) AS currentStreak
FROM (
  SELECT
    habit_id,
    dt,
    @grp := IF(@prevHabitId = habit_id AND dt = @prevDate + INTERVAL 1 DAY, @grp, @grp + 1) AS grp,
    @prevDate := dt,
    @prevHabitId := habit_id
  FROM (
    SELECT habit_id, DATE(created_at) AS dt
    FROM habit_progress_logs
    JOIN (SELECT @prevHabitId := NULL, @prevDate := NULL, @grp := 0) i
    WHERE habit_id = 4
    GROUP BY habit_id, dt
    ORDER BY habit_id, dt
  ) s
) t1
GROUP BY habit_id, grp
ORDER BY habit_id, grp DESC
LIMIT 1;

    这是一个db<>fiddle

    • 1

相关问题