SQL implements continuous login days and maximum login days
Title: calculate the number of consecutive login days of users
Problem extension: users who log in for 3 consecutive days, maximum login days, etc
The field user id and login time date given in sheet1 are shown in the following table:
1. Cleaning data
Because it is the calculation of continuous login days, it is accurate to hours, minutes and seconds, and there may be situations in the table, such as multiple continuous logins and multiple logins on the same day. Therefore, data cleaning is carried out.
Here, you only need to use date for cleaning.
SELECT id,date(date) time from sheet1 GROUP BY id ,time
Get:
2. Sort the login days of users
You need to use the window function row here_ Number () over (partition by field 1 order by field 2)
SELECT id,time,row_number() over(partition by id order by time) num from (SELECT id,date(date) time from sheet1 GROUP BY id ,time) a
Get:
3. Subtract the sorting number from the login time (the number is the number of days)
If the user logs in continuously, for example, on January 2, 3 and 4, the order is 1, 2 and 3. January 2 minus the corresponding serial number 1 will get January 1, and January 3 minus the corresponding serial number 2 will also get January 1. Therefore, if the user logs in continuously, the login date minus the number of days as the serial number will get the same date. Count the same date after subtracting the serial number. This number is the number of consecutive login days. Date is required here_ sub(time,interval number day)
SELECT *,DATE_SUB(time,INTERVAL num day) newtime from (SELECT id,time,row_number() over(partition by id order by time) num from (SELECT id,date(date) time from sheet1 GROUP BY id ,time) a) a
Get:
4. Counts the number of days a user logged in
Users may have logged in in different time periods, so the number of consecutive login days may occur several times. The following is the number of consecutive login days of users
SELECT id,newtime,count(newtime) rk from (SELECT *,DATE_SUB(time,INTERVAL num day) newtime from (SELECT id,time,row_number() over(partition by id order by time) num from (SELECT id,date(date) time from sheet1 GROUP BY id ,time) a) a) a GROUP BY id,newtime
Get:
Extension question 1: log in for three consecutive days
For the above code, continue nesting
SELECT DISTINCT id from (SELECT id,newtime,count(newtime) rk from (SELECT *,DATE_SUB(time,INTERVAL num day) newtime from (SELECT id,time,row_number() over(partition by id order by time) num from (SELECT id,date(date) time from sheet1 GROUP BY id ,time) a) a) a GROUP BY id,newtime) a where rk=3
Get:
Or use having to filter directly, but if the user logs in for 3 consecutive days for many times, it needs to be nested again.
SELECT id,newtime,count(newtime) rk from (SELECT *,DATE_SUB(time,INTERVAL num day) newtime from (SELECT id,time,row_number() over(partition by id order by time) num from (SELECT id,date(date) time from sheet1 GROUP BY id ,time) a) a) a GROUP BY id,newtime having rk=3
Get:
Extension question 2: maximum login days
SELECT DISTINCT id,max(rk) from (SELECT id,newtime,count(newtime) rk from (SELECT *,DATE_SUB(time,INTERVAL num day) newtime from (SELECT id,time,row_number() over(partition by id order by time) num from (SELECT id,date(date) time from sheet1 GROUP BY id ,time) a) a) a GROUP BY id,newtime) a GROUP BY id
Get: