一、题目
038题目发出后，群里很多朋友发出各种疑问，也给出了很多写法。大家的疑问主要有两个
1.关于每个用户的起止时间问题，如果想要的是每个用户最早到最后登录日期中间断点登录，而不是固定周期；
2.生成全量用户的登录记录，这样数据量会不会太大，在实际应用场景中是否可行；
在我给出038自己的写法后，一直在思考面试出这个题目的落脚点在哪？想要测试应试者具备哪些知识点。始终没有特别清晰的思路，但是当看到群里小伙伴给出的解法后，恍然大悟，还是在考察连续问题+数据生成。这个问题我们在039问题中，也是类似考察点，只不过展现形式不一样。
题目原始链接：
具体题目如下，这里把期望结果的数据做下调整，删除bbb的2023-12-10的登录结果。
现有用户登录记录表，已经按照用户日期进行去重处理。请查询出用户连续登录中出现断点的所有日期
+----------+-------------+| user_id | login_date |+----------+-------------+| aaa | 2023-12-01 || aaa | 2023-12-02 || aaa | 2023-12-04 || aaa | 2023-12-08 || aaa | 2023-12-10 || bbb | 2023-12-01 || bbb | 2023-12-03 || bbb | 2023-12-04 || bbb | 2023-12-07 || bbb | 2023-12-08 || bbb | 2023-12-09 |+----------+-------------+
期望输出结果
+----------+-------------+| user_id | login_date |+----------+-------------+| aaa | 2023-12-03 || aaa | 2023-12-05 || aaa | 2023-12-06 || aaa | 2023-12-07 || aaa | 2023-12-09 || bbb | 2023-12-02 || bbb | 2023-12-05 || bbb | 2023-12-06 |+----------+-------------+
二、分析
连续问题，我们找到用户每次断点后的登录日期，并计算出与上次登录的日期差，然后根据日期和日期差生成断点的日期记录。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️ |
三、SQL
1.查询每个用户非连续登录后的首次登录记录，以及与上次登录的日期差，然后再减1。0代表连续，-1代表为用户首次登录。
select user_id,login_date,lag(login_date,1,login_date) over(partition by user_id order by login_date) as lag_date,datediff(login_date,lag(login_date,1,login_date) over(partition by user_id order by login_date))-1 as diff_days from t_login_38查询结果
2.限制diff_day>0,然后使用生成函数，根据diff_day生成断点日期记录。
select user_id,login_date,lag_date,diff_days,sub_day,date_sub(login_date,sub_day) as un_login_datefrom(select user_id,login_date,lag_date,diff_daysfrom(selectuser_id,login_date,lag(login_date,1,login_date) over(partition by user_id order by login_date) as lag_date,datediff(login_date,lag(login_date,1,login_date) over(partition by user_id order by login_date))-1 as diff_daysfrom t_login_38)t1where diff_days >0) tt1lateral view explode(sequence(1,diff_days)) tt2 as sub_day
查询结果
根据上面记录，只要取user_id 和un_login_date列即可。
3.群里小伙伴sql
selectuser_id,date_sub(login_date,rn) as miss_login_datefrom(select *from(select *,datediff(login_date,lag_date)-1 as diff_daysfrom(select *,lag(login_date,1,login_date) over(partition by user_id order by login_date) as lag_datefrom t_login_38)tmp)tmpwhere diff_days>0 --0是连续)tmplateral view posexplode(split(space(diff_days),'')) ed as rn,valuewhere rn>=1;
查询结果
四、建表语句和数据插入
--建表语句create table t_login_38(user_id string COMMENT '用户ID',login_date string COMMENT '登录日期') COMMENT '用户登录记录表'stored as orc;--插入数据insert into t_login_38(user_id,login_date)values('aaa','2023-12-01'),('aaa','2023-12-02'),('aaa','2023-12-04'),('aaa','2023-12-08'),('aaa','2023-12-10'),('bbb','2023-12-01'),('bbb','2023-12-03'),('bbb','2023-12-04'),('bbb','2023-12-07'),('bbb','2023-12-08'),('bbb','2023-12-09')
社群
为了方便交流和得到大家的反馈，我创建了一个交流群，大家集思广益、共同进步
原文作者：数据仓库技术；如有侵权，请联系管理员删除。
请登录后发表评论
登录
全部评论
