how to find consecutive day for login

Question

there is table in my database (MySQL 5.7.36),I try to find consecutive day with condition

if consecutive day > 7

consecutive day will be set zero

DATE_SERV
2022-01-01
2022-01-02
2022-01-03
2022-01-05
2022-01-06
2022-01-09
2022-01-10
2022-01-11

my actually expect table is

DATE_SERV	day_consecutive
2022-01-01	1
2022-01-02	2
2022-01-03	3
2022-01-05	1
2022-01-06	2
2022-01-09	1
2022-01-10	2
2022-01-11	3
2022-01-12	4
2022-01-13	5
2022-01-14	6
2022-01-15	7
2022-01-16	1
2022-01-17	2

What version of MySQL? 5.x has different capabilities than 8.x — SOS, Mar 27 '22 at 15:50
thank you so much. I just try on local MySQL.But, I have to develop on my server — Panida Yimsanga, Mar 27 '22 at 16:15
I don't follow. Are you saying you're running MySQL 8.x locally, but your server is running 5.7.x? — SOS, Mar 27 '22 at 16:18
I running MySQL 8.0.20 locally, but my server is running 5.7.36 — Panida Yimsanga, Mar 27 '22 at 16:28
Any chance of upgrading? It'd be much easier with the window function support in 8.x. Unfortunately I'm not sure how with MySQL 5.x... — SOS, Mar 27 '22 at 17:04
The only thing I've found is using the old @ variables hack https://stackoverflow.com/questions/65847509/grouping-islands-in-mysql-5-7 but be aware of the behavior risks and limitations (https://stackoverflow.com/a/54997037/8895292) — SOS, Mar 27 '22 at 17:18

score 0 · Accepted Answer · answered Mar 27 '22 at 16:57

I wrote this up before, thinking you were using MySQL 8.x (which supports window functions, unfortunately 5.x does not). Anyway, just posting it in case it's useful to someone else ...

You can adapt the approach from this blog Gaps and Islands Across Date Ranges. First identify the "islands" or groups of consecutive dates

SELECT
        DATE_SERV
        , SUM( IF( DATEDIFF(DATE_SERV, Prev_Date) = 1, 0, 1) ) OVER(
                   ORDER BY DATE_SERV 
        )  AS DateGroup_Num
FROM
      (
         SELECT DATE_SERV
                , LAG(DATE_SERV,1) OVER (
                       ORDER BY DATE_SERV
                ) AS Prev_Date
         FROM   YourTable
      ) grp

Which produces this result:

DATE_SERV	DateGroup_Num
2022-01-01	1
2022-01-02	1
2022-01-03	1
2022-01-05	2
2022-01-06	2
2022-01-09	3
2022-01-10	3
2022-01-11	3

Then use a conditional SUM(...) to find the earliest date per group, and display the number of consecutive days since that date:

SELECT 
       t.DATE_SERV
       , DATEDIFF( 
            t.DATE_SERV
            , MIN(t.DATE_SERV) OVER( 
                  PARTITION BY t.DateGroup_Num 
                  ORDER BY t.DATE_SERV 
             )
        ) +1 AS Consecutive_Days
FROM ( 
        SELECT
               DATE_SERV
              , SUM( IF( DATEDIFF(DATE_SERV, Prev_Date) = 1, 0, 1) ) OVER(
                    ORDER BY DATE_SERV 
                )  AS DateGroup_Num
        FROM
        (
                SELECT DATE_SERV
                       , LAG(DATE_SERV,1) OVER (
                            ORDER BY DATE_SERV
                         ) AS Prev_Date
                FROM   YourTable
        ) grp
    ) t

Results:

DATE_SERV	Consecutive_Days
2022-01-01	1
2022-01-02	2
2022-01-03	3
2022-01-05	1
2022-01-06	2
2022-01-09	1
2022-01-10	2
2022-01-11	3

db<>fiddle here

how to find consecutive day for login

1 Answers1