How Mysql queries the maximum number of consecutive logon days between two dates _mysql

Objective

The recent work encountered a demand, based on the number of consecutive days of users to calculate, to find the user in a period of time the largest continuous recording time, for example, between 2016-01-01 and 2016-01-28, if the user in the 3rd and 4th are recorded, then the number of consecutive records of 2, If the user records at number 6th-10th daily, the maximum number of consecutive records is 5.

Get this demand, tell the truth a little Meng, the first thought is to statistics in the code, will use the loop, think of so many users, and the time span is also a bit large, such as 15 to 16, two years time, think about a bit scary.

Solution

Then the need to talk to friends, friends also feel a bit difficult to do, and later through an online article has some small ideas. But the view is also a smattering, although often write SQL statements, but also commonly used in those additions and deletions, such as the use of the way is not used, after a meeting, friends threw me a SQL statement, on the basis of the article was modified to meet the requirements of my project statement.

SELECT * FROM (select UID, max (days) lianxu_days, Min (login_day) star
           T_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 FRO  M (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 has lianxu_days > 10 The TMP ORDER by Lianxu_days DESC) ntmp the GROUP by UID;

The results of the query are shown in the following illustration:

If you want to view a single person, then change the SQL statement to a uid !=0 specific value.

Summarize

The above is the entire content of this article, I hope the content of this article for everyone to learn or use SQL statements can help, if you have questions you can message exchange.