此专题为【连续登录专题】,难度均为中等题。
为避免错误,拆分模块较多,书写风格有点变态~
id | date |
101 | 2021/11/1 |
102 | 2021/11/2 |
103 | 2021/11/3 |
104 | 2021/11/1 |
105 | 2021/11/1 |
101 | 2021/11/2 |
101 | 2021/11/3 |
106 | 2021/11/5 |
102 | 2021/11/6 |
103 | 2021/11/4 |
103 | 2021/11/6 |
105 | 2021/11/5 |
106 | 2021/11/6 |
106 | 2021/11/10 |
102 | 2021/11/7 |
1. 求连续登录3天以上的用户数
# 新建列: create temporary table tmp (select *, lag(date,1,0) over(partition by id order by date) as front_1, lead(date,1,0) over(partition by id order by date) as back_1 from login); # 连续登录: select count(distinct id) as cnt from tmp where datediff(date,front_1)=1 and datediff(back_1,date)=1;
2. 求每个用户的最大连续登录天数
思路:在连续登录的基础上做~
# 新建列: create temporary table tmp (select *, if(lag(date,1,0) over(partition by id order by date)<>'0', lag(date,1,0) over(partition by id order by date),'1990-01-01') as front_1, if(lead(date,1,0) over(partition by id order by date)<>'0', lead(date,1,0) over(partition by id order by date),'1990-01-01') as back_1 from login); # 连续登录: create temporary table tmp2 (select distinct id,date from tmp where datediff(date,front_1)=1 or datediff(back_1,date)=1); # 聚合计数: create temporary table tmp3 (select id,count(date) as cnt from tmp2 group by id); # 连续登录天数: create temporary table tmp4 (select id,cnt, rank() over(partition by id order by cnt desc) as rnk from tmp3); # 最大连续登录天数: create temporary table tmp5 (select distinct id,cnt from tmp4 where rnk=1); # 涵盖每个用户: create temporary table tmp6 (select distinct id from login); # 左连接: select distinct tmp6.id,ifnull(cnt,0) as cnt from tmp6 left join tmp5 on tmp6.id=tmp5.id;
3. 求有过连续 n 天以上不登陆的用户(这里取为3)
思路:
例如:3.1,3.5,3.6,3.10
条件为:后面的减去前面的 >= 3
# 新建列: create temporary table tmp (select *, lead(date,1,0) over(partition by id order by date) as back_1 from login); # 连续未登录天数>=3: select distinct id from tmp where datediff(back_1,date)>=3;
4. 求每个用户登录间隔小于 n 天的天数(这里取为3)
思路:
例如:3.1,3.2,3.3,3.4
条件为:前面的减去当天的天数 <=3 ,或者后面的减去当天的天数 <= 3
# 新建列: create table tmp (select *, lag(date,1,0) over(partition by id order by date) as front_1, lead(date,1,0) over(partition by id order by date) as back_1 from login); # 登录间隔<=3天数: select id,count(distinct date) as cnt from tmp where datediff(date,front_1)<=3 or datediff(back_1,date)<=3 group by id;
--------------------------------------------
附数据集代码:
create table login(id int,date date); insert into login values('101','2021-11-01'); insert into login values('102','2021-11-02'); insert into login values('103','2021-11-03'); insert into login values('104','2021-11-01'); insert into login values('105','2021-11-01'); insert into login values('101','2021-11-02'); insert into login values('101','2021-11-03'); insert into login values('106','2021-11-05'); insert into login values('102','2021-11-06'); insert into login values('103','2021-11-04'); insert into login values('103','2021-11-06'); insert into login values('105','2021-11-05'); insert into login values('106','2021-11-06'); insert into login values('106','2021-11-10'); insert into login values('102','2021-11-07');
-END
标签:11,insert,专题,登录,连续,date,login,id,2021 From: https://www.cnblogs.com/peitongshi/p/16986225.htmlhttps://www.xiaohongshu.com/discovery/item/63750ebb000000001f024242?app_platform=android&app_version=7.48.0&share_from_user_hidden=true&type=normal&xhsshare=WeixinSession&appuid=5d0a287200000000160275a9&apptime=1668828851