SQL查询2021年每个月，连续2天都有登陆的用户名单（偏移函数、子查询、连续问题万能模板）

【面试题】

有一张“用户登陆记录表”，包含两个字段：用户id、日期。

【问题】查询2021年每个月，连续2天都有登陆的用户名单。

【解题步骤】

1. 连续问题的万能模板

我在《拼多多面试题：如何找出连续出现N次的内容？》里讲过遇到“连续问题”如何解决，并送出了一个万能模板，模板使用的是窗口函数解决连续问题。

select distinct 列1
from(
select 列1,
lead(列1,1) over(order by 序号) as 列2,
lead(列1,2) over(order by 序号) as 列3,
...
lead(列1,n-1) over(order by 列) as 列n,
from 表名
) as a
where (a.列1 = a.列2 and ... and a.列1 = a.列n);

2. 窗口函数

窗口函数lead使用方法：

lead(字段名,N,默认值) over(partion by …order by …)

默认值是指：当向上N行或者向下N行值时，如果已经超出了表行和列的范围时，会将这个默认值作为函数的返回值，若没有指定默认值，则返回Null。

窗口函数lead可以获取每个字段的后面的第n个值，并生成新的一列。

而这道题描述的“用户连续登陆”中的“连续”可以理解为用户当前的登陆日期与本月下一次登陆日期相差一天。

我们可以先用窗口函数lead获取“用户当月下一个登陆日期”：

select 用户id,
       month(日期) as 月,
       日期,
       lead(日期,1,'当月最后登陆日期') over(partition by month(日期), 用户id order by 日期) as 用户当月下一个登陆日期
from 用户登陆记录表;

当“日期”是该用户在当月最后一天登陆时，记录为“当月最后登陆日期”，如果不进行设置，将会返回Null，不利于理解。

从结果看，我们可以获得以下信息：

1）当“日期”与“用户当月下一个登陆日期”只相差一天，即用户本次登陆为连续登陆；

2）当“日期”与“用户当月下一个登陆日期”相差大于一天，即用户本次登陆为连续登陆的最后一天（也有可能仅登陆一天）；

3）当“用户当月下一个登陆日期”等于“当月最后登陆日期”，即用户本次登陆为本月最后一天登陆。

这样，可以判断用户连续登陆的情况。

接下来就解决用户每次连续登陆天数的计算。

用户每次连续登陆天数与用户登陆顺序存在某种必然的关系，此时我们可以先用子查询将用户在本月的阅读顺序查询出来，使用窗口函数row_number: 

select *,
       lead(日期,1,'当月最后登陆日期') over(partition by 月, 用户id order by 每个月登陆顺序) as 用户当月下一个登陆日期
from (
select 用户id,
       month(日期) as 月,
       日期,
       row_number() over (partition by month(日期), 用户id order by 日期) as 每个月登陆顺序
from 用户登陆记录表
) as t1;

 

可以看出，当连续终止时，即：

1）“日期”与“用户当月下一个登陆日期”相差大于一天；

2）“用户当月下一个登陆日期”等于“当月最后登陆日期”；

两种情况。

将这两种情况过滤出来之后，用户连续登陆天数为：当前登陆顺序减去上一个登陆顺序。

select *,
       lag(每个月登陆顺序,1) over(partition by 月, 用户id order by 每个月登陆顺序) as 上一个登陆顺序
from (
select *,
       lead(日期,1,'当月最后登陆日期') over(partition by 月, 用户id order by 每个月登陆顺序) as 用户当月下一个登陆日期
from (
select 用户id,
       month(日期) as 月,
       日期,
       row_number() over (partition by month(日期), 用户id order by 日期) as 每个月登陆顺序
from 用户登陆记录表
) as t1
) as t2
where date_sub(用户当月下一个登陆日期,interval 1 day) <> 日期 or 用户当月下一个登陆日期 = '当月最后登陆日期';

 

“上一个登陆顺序”为Null时，用0代替（使用coalesce函数），那么“每个月登陆顺序”减去“上一个登陆顺序”就是本次连续登陆天数。

过滤出最终结果：

select distinct 月,用户id
from (
select *,
       每个月登陆顺序 - coalesce(lag(每个月登陆顺序,1) over(partition by 月, 用户id order by 每个月登陆顺序),0) as 连续登陆天数
from (
select *,
       lead(日期,1,'当月最后登陆日期') over(partition by 月, 用户id order by 每个月登陆顺序) as 用户当月下一个登陆日期
from (
select 用户id,
       month(日期) as 月,
       日期,
       row_number() over (partition by month(日期), 用户id order by 日期) as 每个月登陆顺序
from 用户登陆记录表
) as t1
) as t2
where date_sub(用户当月下一个登陆日期,interval 1 day) <> 日期 or 用户当月下一个登陆日期 = '当月最后登陆日期'
) as t3
where 连续登陆天数 >= 2;

【本题考点】

1.考查对窗口函数的了解

2.考查对子查询的了解

3.考查对连续问题的了解，可以套用万能模板

【举一反三】

【问题】查询2021年每个月，连续5天都有登陆的用户数。

与原题的区别在于：

1）“连续2天”变成了“连续5天”：对最后的where条件进行修改；

2）查询“用户名单”变成了“用户数”：用户group by和count(distinct 用户id)计算用户数。

select 月,
       count(distinct 用户id) as 连续5天登陆的用户数
from (
select *,
       每个月登陆顺序 - coalesce(lag(每个月登陆顺序,1) over(partition by 月, 用户id order by 每个月登陆顺序),0) as 连续登陆天数
from (
select *,
       lead(日期,1,'当月最后登陆日期') over(partition by 月, 用户id order by 每个月登陆顺序) as 用户当月下一个登陆日期
from (
select 用户id,
       month(日期) as 月,
       日期,
       row_number() over (partition by month(日期), 用户id order by 日期) as 每个月登陆顺序
from 用户登陆记录表
) as t1
) as t2
where date_sub(用户当月下一个登陆日期,interval 1 day) <> 日期 or 用户当月下一个登陆日期 = '当月最后登陆日期'
) as t3
where 连续登陆天数 >= 5
group by 月;