时间戳

首页 > 解决方案 > 如何获取mysql中所有用户的当前连续和最长连续

mysql - 如何获取mysql中所有用户的当前连续和最长连续

问题描述

我在获取所有用户的当前连续记录和所有用户的最长连续记录时面临一个问题。我有下表 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 // current_streak 2 and longest streak is 3
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 // current_streak 3 and longest streak is 3

我的预期输出如下：-

user_id   current_streak   longest_streak
 1             2                 3
 3             3                 3

我能够获取特定用户的当前连续数据，但我想要所有用户的数据。我在下面尝试过：-

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

@Akina 感谢您的询问，我已经更新了我的问题。现在看上面我的 user_bible_trackings 表。2021年user_id8 月 26 日 3 次阅读圣经两次所以当前的连胜必须是 3 和最长的连胜。如果你能在这方面帮助我，我会很高兴

@Akina test for this rows
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')

我对上面的预期输出

user_id   current_streak   longest_streak
 1             2                 3
 3             3                 3

但是您的查询返回低于输出

user_id   current_streak   longest_streak
 1             2                 3
 3             4                 4

请帮助我如何解决这个问题。

标签： mysql

解决方案

不是最佳的，不是紧凑的，但清晰的解决方案：

WITH 
cte1 AS (
SELECT *,
       CASE date_read 
           WHEN LAG(date_read) OVER (PARTITION BY user_id ORDER BY date_read) + INTERVAL 1 DAY
           THEN 0
           ELSE 1 END group_start
FROM test
),
cte2 AS (
SELECT *,
       SUM(group_start) OVER (PARTITION BY user_id ORDER BY date_read) group_num
FROM cte1
),
cte3 AS (
SELECT user_id, group_num,
       COUNT(*) group_count
FROM cte2
GROUP BY user_id, group_num
)
SELECT DISTINCT 
       user_id,
       FIRST_VALUE(group_count) OVER (PARTITION BY user_id ORDER BY group_num DESC) current_streak,
       MAX(group_count) OVER (PARTITION BY user_id) longest_streak
FROM cte3

详细的小提琴

推荐阅读