How does mysql query the maximum number of consecutive logon days between two dates and the number of mysql days?

How does mysql query the maximum number of consecutive logon days between two dates and the number of mysql days?

Preface

A requirement encountered in recent work is calculated based on the number of consecutive days of user record, and the maximum continuous record time of the user in a period of time is obtained, for example, between and, if you have recorded records on both 3 and 4, the number of consecutive days is 2. If you have recorded records on the 6-10 day, the maximum number of consecutive days is 5.

When I got this requirement, to be honest, it was a bit confusing. The first thing I thought was to collect statistics in the Code and use a loop. I thought of so many users and the time span was a little large, for example, 15 to 16 years, two years, it is a bit scary to think about.

Solution

Then I spoke to my friends about this demand. My friends also thought it was a bit difficult. Later, I had some small ideas through the previous article on the Internet. However, it seems that SQL statements are often written, but they are also commonly used for addition, deletion, modification, and query. It is never used in such a way, A friend threw me an SQL statement and modified it based on the article to meet my project requirements.

SELECT *FROM (SELECT *   FROM (       SELECT        uid,        max(days)   lianxu_days,        min(login_day) start_date,        max(login_day) end_date       FROM (SELECT           uid,           @cont_day :=           (CASE           WHEN (@last_uid = uid AND DATEDIFF(created_ts, @last_dt) = 1)            THEN             (@cont_day + 1)           WHEN (@last_uid = uid AND DATEDIFF(created_ts, @last_dt) < 1)            THEN             (@cont_day + 0)           ELSE            1           END)                       AS days,           (@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,           @last_uid := uid,           @last_dt := created_ts                login_day          FROM (SELECT              uid,              DATE(created_ts) created_ts             FROM plan_stage             WHERE uid != 0             ORDER BY uid, created_ts) AS t,           (SELECT            @last_uid := '',            @last_dt := '',            @cont_ix := 0,            @cont_day := 0) AS t1         ) AS t2       GROUP BY uid, cont_ix       HAVING lianxu_days > 10      ) tmp   ORDER BY lianxu_days DESC) ntmpGROUP BY uid;

Shows the query result:

If you want to view a single useruid !=0Change it to a specific value.

Summary

The above is all the content of this article. I hope this article will help you learn or use SQL statements. If you have any questions, please leave a message.