连续登录综训

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

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

表 login：

1. 求连续登录3天以上的用户数

方法一：lead、lag
# 新建列：
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;

方法二：row_number、subdate
# 新建排序列、分组列
create temporary table tmp1
(select *,
    row_number() over(partition by id order by date) as rnk,
    subdate(date,row_number() over(partition by id order by date)) as grp
from login);

# 聚合筛选满足条件的id
create temporary table tmp2
(select distinct id
from tmp1
group by id,grp
having count(*)>=3);

# 计数
select count(id) as cnt
from tmp2;

2. 求每个用户的最大连续登录天数

思路：在连续登录的基础上做~

方法一（有局限性）：lead、lag
# 新建列：
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;

方法二：
# 新建排序列、分组列
create temporary table tmp1
(select *,
    row_number() over(partition by id order by date) as rnk,
    subdate(date,row_number() over(partition by id order by date)) as grp
from login);

# 聚合筛选连续登录
create temporary table tmp2
(select *,count(*) as cnt
from tmp1
group by id,grp
having count(*)>=2);

# 最大连续登录天数
create temporary table tmp3
(select id,max(cnt) as max_cnt
from tmp2
group by id);

# 每个用户
create temporary table tmp4
(select distinct id
from login);

# 左连接
select tmp4.id,ifnull(max_cnt,0)
from tmp4 left join tmp3
on tmp4.id=tmp3.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;

力扣1225：

with tmp1 as
(select *,
    row_number() over(order by fail_date) as rnk,
    subdate(fail_date,row_number() over(order by fail_date)) as grp
from Failed 
where fail_date between '2019-01-01' and '2019-12-31')

,tmp2 as
(select *,
    row_number() over(order by success_date) as rnk,
    subdate(success_date,row_number() over(order by success_date)) as grp
from Succeeded 
where success_date between '2019-01-01' and '2019-12-31')

,tmp3 as
((select distinct 'failed' as period_state,
    min(fail_date) as start_date,
    max(fail_date) as end_date
from tmp1
group by grp)
union all
(select distinct 'succeeded' as period_state,
    min(success_date) as start_date,
    max(success_date) as end_date
from tmp2
group by grp))

select *
from tmp3
order by start_date

--核心是分组的构建，这里采用日期减去 row_number() 创建的序号，即用到 subdate(date,要减去的数) 函数创建该分组。

但分组的前提是，有实际的空缺，不连续。

力扣1811：

# 不连续三场、金牌
with tmp1 as
(select gold_medal as id
from Contests
group by gold_medal
having count(gold_medal)>=3)

# 满足条件1
,tmp2 as
((select contest_id,gold_medal as medal
from Contests)
union all
(select contest_id,silver_medal as medal
from Contests)
union all
(select contest_id,bronze_medal as medal
from Contests))

# 连续、任意奖牌
,tmp3 as
(select *,
    row_number() over(partition by medal order by contest_id) as rnk,
    (contest_id-row_number() over(partition by medal order by contest_id)) as grp
from tmp2)

# 连续三场
,tmp4 as
(select medal as id
from tmp3
group by medal,grp
having count(*)>=3)

# 合并
,tmp5 as
((select *
from tmp1)
union
(select *
from tmp4))

# 左连接
select name,mail
from tmp5 left join Users
on tmp5.id = Users.user_id

 力扣2173：

# 连续（非常规）
with tmp4 as
(select player_id,max(streak) as longest_streak 
from
(select player_id,grp,count(*) as streak
from
(select *,
    row_number() over(partition by player_id order by match_day) as rnk2,
    (rnk-row_number() over(partition by player_id order by match_day)) as grp
from
(select *,
    row_number() over(partition by player_id order by match_day) as rnk
from Matches) as tmp1
where result='Win') as tmp2
group by player_id,grp) as tmp3
group by player_id)

# 名单表
,tmp as
(select distinct player_id
from Matches)

# 左连接
select tmp.player_id,ifnull(longest_streak,0) as longest_streak
from tmp left join tmp4
on tmp.player_id=tmp4.player_id

--这里偷换了概念，不是时间连续，而是事件连续~

力扣601：

select id,visit_date,people
from
    (select *,count(*) over(partition by grp) as cnt
    from 
        (select *,
            abs(id-rnk) as grp
        from
            (select *,
                row_number() over(order by visit_date) as rnk
            from Stadium
            where people >= 100) as tmp1) as tmp2) as tmp3
where cnt >= 3
order by visit_date

 --创建连续序列与不连续序列的差值时，可用 abs() 绝对值规避负数报错。

这里最后结果是 count() over() 分组，不是 group by 聚合；窗口函数后 over() 中 order by 缺省，默认 rows between unbounding preceding and unbounding following。

--------------------------------------------

附数据集代码：

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