SQL抖音面试题：送你一个万能模板，要吗？(重点、每个用户每月连续登录的最大天数)
source link: https://blog.51cto.com/u_15084039/5558391
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
SQL抖音面试题：送你一个万能模板，要吗？(重点、每个用户每月连续登录的最大天数)
原创【面试题】
有一张“用户登陆记录表”，包含两个字段：用户id、日期。
【问题】查询2021年每个月，每个用户连续登陆的最多天数。
【解题步骤】
1. 连续问题的万能模板
在 《拼多多面试题：如何找出连续出现N次的内容？》里讲过遇到“连续问题”如何解决，并送出了一个万能模板，模板使用的是 窗口函数解决连续问题。
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使用方法：
默认值是指：当向上N行或者向下N行值时，如果已经超出了表行和列的范围时，会将这个默认值作为函数的返回值，若没有指定默认值，则返回Null。
窗口函数lead可以获取每个字段的后面的第n个值，并生成新的一列。
而这道题描述的“用户连续登陆”中的“连续”可以理解为用户当前的登陆日期与本月下一次登陆日期相差一天。
我们可以先用窗口函数lead获取“用户当月下一个登陆日期”：
month(日期) as 月,
日期,
lead(日期,1,'当月最后登陆日期') over(partition by month(日期), 用户id order by 日期) as 用户当月下一个登陆日期
from 用户登陆记录表;
当“日期”是该用户在当月最后一天登陆时，记录为“当月最后登陆日期”，如果不进行设置，将会返回Null，不利于理解。
从结果看，我们可以获得以下信息：
1）当“日期”与“用户当月下一个登陆日期”只相差一天，即用户本次登陆为连续登陆；
2）当“日期”与“用户当月下一个登陆日期”相差大于一天，即用户本次登陆为连续登陆的最后一天（也有可能仅登陆一天）；
3）当“用户当月下一个登陆日期”等于“当月最后登陆日期”，即用户本次登陆为本月最后一天登陆。
这样，可以判断用户连续登陆的情况。
接下来就解决用户每次连续登陆天数的计算。
3. 子查询
用户每次连续登陆天数与用户登陆顺序存在某种必然的关系，此时我们可以先用子查询将用户在本月的阅读顺序查询出来，使用窗口函数row_number:
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）“用户当月下一个登陆日期”等于“当月最后登陆日期”；
两种情况。
将这两种情况过滤出来之后，用户连续登陆天数为：当前登陆顺序减去上一个登陆顺序。
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函数），那么“每个月登陆顺序”减去“上一个登陆顺序”就是本次连续登陆天数。
4. 汇总分析
最后获取“每个月，每个用户连续登陆的最多天数”，使用group by函数。
用户id,
max(连续登陆天数) as 连续登陆的最多天数
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
group by 月,用户id;
【本题考点】
1.考查对窗口函数的了解，要把 《猴子 从零学会SQL》里讲过的窗口函数能解决的4类面试题要记住；
2.考查对子查询的了解；
3.考查对连续问题的了解，可以套用万能模板。
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK