连续登录天数的SQL查询
数据库版本:MySQL 8.0.16
建表以及插入数据
CREATE TABLE tmp_last_3_days (
user_id int(11) ,
login_date date
) COMMENT='连续登录表' ;
insert into tmp_last_3_days values(123,'2020-05-25');
insert into tmp_last_3_days values(123,'2020-05-26');
insert into tmp_last_3_days values(123,'2020-05-27');
insert into tmp_last_3_days values(456,'2020-05-23');
insert into tmp_last_3_days values(456,'2020-05-25');
insert into tmp_last_3_days values(789,'2020-05-21');
insert into tmp_last_3_days values(789,'2020-05-25');
insert into tmp_last_3_days values(789,'2020-05-26');
insert into tmp_last_3_days values(789,'2020-05-27');
insert into tmp_last_3_days values(789,'2020-05-22');
insert into tmp_last_3_days values(789,'2020-05-23');
1-连续三天登录
select distinct user_id,login_date
from (select user_id,login_date,
lead(login_date,2,-1)over(partition by user_id order by login_date desc) as lead_date
from tmp_last_3_days )T
where date_sub(login_date,interval 2 day)=lead_date
order by login_date;
+---------+------------+
| user_id | login_date |
+---------+------------+
| 123 | 2020-05-27 |
| 789 | 2020-05-27 |
| 789 | 2020-05-23 |
+---------+------------+
3 rows in set, 6 warnings (0.00 sec)
2-最近连续登录几天
insert into tmp_last_3_days values(789,'2020-05-28');
insert into tmp_last_3_days values(789,'2020-05-24');
select user_id,min_login_date,last_day_num
from (
select user_id,min_login_date,last_day_num,
row_number()over(partition by user_id order by min_login_date desc) as rn
from (
select user_id,min(login_date) as min_login_date,count(first_day) as last_day_num
from (
select user_id,login_date,
row_number()over(partition by user_id order by login_date) as day_num,
date_sub(login_date,interval row_number()over(partition by user_id order by login_date) day) as first_day
from tmp_last_3_days ) T
group by user_id,first_day having count(1)>1
)A
)B
where B.rn=1;
+---------+----------------+--------------+----+
| user_id | min_login_date | last_day_num | rn |
+---------+----------------+--------------+----+
| 123 | 2020-05-25 | 3 | 1 |
| 789 | 2020-05-21 | 8 | 1 |
+---------+----------------+--------------+----+
2 rows in set (0.01 sec)