抖音面试题:遇到连续问题怎么办?
【面试题】
有一张“用户登陆记录表”,包含两个字段:用户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)当“用户当月下一个登陆日期”等于“当月最后登陆日期”,即用户本次登陆为本月最后一天登陆。
这样,可以判断用户连续登陆的情况。
接下来就解决用户每次连续登陆天数的计算。
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 月;
相关文章
- 爆红十年后,谁“切”了苹果?
- 为了能让智能手机用十年 他们给旧手机做了个系统
- 诠释5G“新内涵” GMIC开启2019年“超序之美”
- iPhone成弃子 惶恐的苹果正在砸烂封闭的围墙
- 腾讯乐享发布三大新功能 助力客户打造新商业闭环
- 苹果要加入重磅新功能!iOS 13来了
- 移动网民习惯变迁报告:从泛社交到深度服务背后的机会何在?
- Google 移除了 Fuchsia 中代号 Armadillo 的系统 UI
- 谷歌删掉 Android One 两年系统更新保证
- 美国最严技术出口管制 中国芯片大军转向欧罗巴
- 9 个你应该知道的支付系统开源项目
- 复制粘贴发明者去世,50 年前她就奠定 Word 的基本功能
- Android Q将支持Vulkan原生渲染引擎:流畅度大提升
- Cydia商店或将关闭,iPhone越狱的时代结束了
- iOS六年开发经验被实习生替代,所谓的“经验”一文不值
- 钱和老师齐备 智能小程序开发者宝典等你开启
- 2018百度智能小程序公开课开课!千亿流量惠及开发者
- 百度智能小程序携布道师计划“力挺”开发者
- 苹果发布iOS 12.1.2系统第一个开发者测试版
- 指纹、面部、虹膜,谁才是AI手机身份识别强者?