大数据面试SQL038 用户连续登录所有断点日期(二)
一、题目
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_date
from(
select user_id,login_date,lag_date,diff_days
from
(
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
)t1
where diff_days >0) tt1
lateral view explode(sequence(1,diff_days)) tt2 as sub_day
查询结果
根据上面记录,只要取user_id 和un_login_date列即可。
3.群里小伙伴sql
select
user_id
,date_sub(login_date,rn) as miss_login_date
from
(
select *
from
(
select *
,datediff(login_date,lag_date)-1 as diff_days
from
(
select *
,lag(login_date,1,login_date) over(partition by user_id order by login_date) as lag_date
from t_login_38
)tmp
)tmp
where diff_days>0 --0是连续
)tmp
lateral view posexplode(split(space(diff_days),'')) ed as rn,value
where 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')