※ 连续登录专题

标签：11 insert 专题 登录 连续 date login id 2021

此专题为【连续登录专题】，难度均为中等题。

为避免错误，拆分模块较多，书写风格有点变态~

login

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

https://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

标签：11,insert,专题,登录,连续,date,login,id,2021
From： https://www.cnblogs.com/peitongshi/p/16986225.html