MySQL查询所有用户当前连续天数与最长连续天数的实现方法

问题说明 #

需要从user_bible_trackings表中查询所有用户的两个指标：

• current_streak：当前最长连续阅读天数
• longest_streak：历史最长连续阅读天数
  注意：同一用户同一天有多条阅读记录时，仅算作1天有效阅读。

表结构与样例数据 #

user_bible_trackings 表结构及样例数据如下：

id    user_id    date_read
1        1       2021-08-21
2        1       2021-08-22
3        1       2021-08-23
4        1       2021-08-26
5        1       2021-08-27
6        3       2021-08-21
7        3       2021-08-24
8        3       2021-08-25
9        3       2021-08-26 
10       3       2021-08-26

预期输出 #

user_id   current_streak   longest_streak
 1             2                 3
 3             3                 3

现有实现问题 #

原有仅支持单用户查询的SQL存在两个问题：一是仅支持单个用户查询，二是同一天有多条记录时统计结果会出错，原有代码如下：

SELECT *
            FROM (
               SELECT t.*, IF(@prev + INTERVAL 1 DAY = t.d, @c := @c + 1, @c := 1) AS streak, @prev := t.d as streak_date
               FROM (
                   SELECT date_read AS d, COUNT(*) AS n
                   FROM user_bible_trackings
                   where user_id = 1
                   group by date_read
               ) AS t
               INNER JOIN (SELECT @prev := NULL, @c := 1) AS vars
            ) AS t
            ORDER BY streak_date DESC LIMIT 1

补充测试场景 #

当user_id=3在2021-08-26存在4条阅读记录时，预期输出保持不变，但现有SQL返回current_streak为4，不符合要求：

INSERT INTO `test` (`id`, `user_id`, `date_read`) VALUES
   (1, 1, '2021-08-21'),
   (2, 1, '2021-08-22'),
   (3, 1, '2021-08-23'),
   (4, 1, '2021-08-26'),
  (5, 1, '2021-08-27'),
  (6, 3, '2021-08-21'),
  (7, 3, '2021-08-24'),
  (8, 3, '2021-08-25'),
  (9, 3, '2021-08-26'),
  (11, 3, '2021-08-26'),
  (12, 3, '2021-08-26'),
  (13, 3, '2021-08-26');

正确实现方案 #

方案1：MySQL 8.0+ 窗口函数版本（推荐） #

WITH user_daily_read AS (
    -- 先按用户+日期去重，同一天多次阅读仅算1天
    SELECT DISTINCT user_id, date_read 
    FROM user_bible_trackings
),
user_streak_group AS (
    -- 给连续的日期分配同一个分组编号
    SELECT 
        user_id,
        date_read,
        DATE_SUB(date_read, INTERVAL ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date_read) DAY) AS group_id
    FROM user_daily_read
),
streak_calculate AS (
    -- 计算每个连续分组的天数
    SELECT 
        user_id,
        group_id,
        COUNT(*) AS streak_days,
        -- 判断该分组是不是当前最新的连续分组（即包含最大阅读日期）
        MAX(date_read) = (SELECT MAX(date_read) FROM user_daily_read udr WHERE udr.user_id = user_streak_group.user_id) AS is_current_streak
    FROM user_streak_group
    GROUP BY user_id, group_id
)
SELECT 
    user_id,
    -- 取最新连续分组的天数作为current_streak
    MAX(IF(is_current_streak = 1, streak_days, 0)) AS current_streak,
    -- 取最大的分组天数作为longest_streak
    MAX(streak_days) AS longest_streak
FROM streak_calculate
GROUP BY user_id;

方案2：兼容MySQL 5.x 变量版本 #

SELECT 
    user_id,
    MAX(IF(is_latest_group = 1, streak_days, 0)) AS current_streak,
    MAX(streak_days) AS longest_streak
FROM (
    SELECT 
        user_id,
        group_id,
        COUNT(*) AS streak_days,
        MAX(date_read) = user_max_date AS is_latest_group
    FROM (
        SELECT 
            t.*,
            @group_id := IF(@prev_user = user_id AND @prev_date + INTERVAL 1 DAY = date_read, @group_id, @group_id + 1) AS group_id,
            @prev_user := user_id,
            @prev_date := date_read
        FROM (
            -- 先去重+获取每个用户的最大阅读日期
            SELECT DISTINCT 
                ubt.user_id, 
                ubt.date_read,
                um.max_date AS user_max_date
            FROM user_bible_trackings ubt
            INNER JOIN (
                SELECT user_id, MAX(date_read) AS max_date 
                FROM user_bible_trackings 
                GROUP BY user_id
            ) um ON ubt.user_id = um.user_id
            ORDER BY ubt.user_id, ubt.date_read
        ) t
        CROSS JOIN (SELECT @prev_user := NULL, @prev_date := NULL, @group_id := 0) vars
    ) t
    GROUP BY user_id, group_id
) t
GROUP BY user_id;

实现逻辑说明 #

1. 第一步先对用户+阅读日期做去重处理，从根源上解决同一天多条记录导致统计错误的问题
2. 通过分组编号识别连续日期段：连续的日期减去按顺序递增的行号，结果会是同一个固定值，用这个值作为分组ID即可把连续的日期分到同一组
3. 统计每个分组的天数，包含用户最大阅读日期的分组对应的天数就是当前连续天数，所有分组的最大天数就是历史最长连续天数

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