Блог

Я сталкиваюсь с одной проблемой для извлечения текущей полосы всех пользователей и самой длинной полосы всех пользователей. У меня есть ниже таблица 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. Для user_id 3 читает Библию 26 августа 2021 года два раза, поэтому текущая полоса должна быть 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
 

Пожалуйста, помогите мне, как решить эту проблему.

Не оптимальное, не компактное, но понятное решение:

 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
 

подробная скрипка