之后会不定期更新每日一题sql系列。
SQL面试题每日一题系列内容均来自于网络以及实际使用情况收集，如与各大厂面试题有雷同，纯属巧合。
1.题目
问题：以下为多个用户每日登录记录数据，已经按照用户登录日期进行了去重处理，求解出每个用户连续三天登录所有的数据记录。
此题也和求解用户连续登录n天的次数题目求解方式重合。
2.基础数据准备
基于上一题SQL面试题每日一题-求解用户最长连续登录天数数据源做了一定修正，方便理解。
create table if not exists temp.user_login_log (
`id` bigint comment '用户id',
`login_date` string comment '登录日期'
) comment '用户每日登录流水'
数据预览
| id | login_date |
|---|---|
| 1 | 2024-04-25 |
| 1 | 2024-04-26 |
| 1 | 2024-04-27 |
| 1 | 2024-04-28 |
| 1 | 2024-04-30 |
| 1 | 2024-05-01 |
| 1 | 2024-05-02 |
| 1 | 2024-05-04 |
| 1 | 2024-05-05 |
| 2 | 2024-04-25 |
| 2 | 2024-04-28 |
| 2 | 2024-05-02 |
| 2 | 2024-05-03 |
| 2 | 2024-05-04 |
期望结果
| id | login_date |
|---|---|
| 1 | 2024-04-25 |
| 1 | 2024-04-26 |
| 1 | 2024-04-27 |
| 1 | 2024-04-26 |
| 1 | 2024-04-27 |
| 1 | 2024-04-28 |
| 1 | 2024-04-30 |
| 1 | 2024-05-01 |
| 1 | 2024-05-02 |
| 2 | 2024-05-02 |
| 2 | 2024-05-03 |
| 2 | 2024-05-04 |
期望输出结果中会看到用户1有25,26,27日记录，也会有26,27,28日的记录，那么26,27就会出现两次
3.问题分析
求解每个用户连续三天登录的所有数据记录，相较于上一个连续问题，其区别在于需要考虑明细数据的展示，其解决问题的办法就完全不同了。考察的是对日期加减函数的使用以及对偏移量开窗函数的应用。
排序开窗函数详见SQL窗口分析函数使用详解系列三之偏移量类窗口函数
期望输出结果中会看到用户1有25,26,27日记录，也会有26,27,28日的记录，那么26,27就会出现两次，原来数据表中只有一次，所以还是得“生成”数据；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️ |
4.解题SQL
1.原始数据求偏移值
使用lead()函数按照用户分组，日期排序，求出后面第三行的日期offset_day1，使用date_add()求解出第三天的日期offset_day2。
通过两种方式的偏移值是否相等来进行判断是否连续三天登录。
select id,login_date
,lead(login_date,2) over(partition by id order by login_date) as offset_day1
,date_add(login_date,2) as offset_day2
from temp.user_login_log
order by id,login_date
数据结果
| id | login_date | offset_day1 | offset_day2 |
|---|---|---|---|
| 1 | 2024-04-25 | 2024-04-27 | 2024-04-27 |
| 1 | 2024-04-26 | 2024-04-28 | 2024-04-28 |
| 1 | 2024-04-27 | 2024-04-30 | 2024-04-29 |
| 1 | 2024-04-28 | 2024-05-01 | 2024-04-30 |
| 1 | 2024-04-30 | 2024-05-02 | 2024-05-02 |
| 1 | 2024-05-01 | 2024-05-04 | 2024-05-03 |
| 1 | 2024-05-02 | 2024-05-05 | 2024-05-04 |
| 1 | 2024-05-04 | (null) | 2024-05-06 |
| 1 | 2024-05-05 | (null) | 2024-05-07 |
| 2 | 2024-04-25 | 2024-05-02 | 2024-04-27 |
| 2 | 2024-04-28 | 2024-05-03 | 2024-04-30 |
| 2 | 2024-05-02 | 2024-05-04 | 2024-05-04 |
| 2 | 2024-05-03 | (null) | 2024-05-05 |
| 2 | 2024-05-04 | (null) | 2024-05-06 |
清晰的看到偏移窗口函数和日期加减函数的值，之后进行比较。
2.判断是否连续登录
判断当日及之后是否连续三天登录，如果两个偏移值相等则代表为连续登录，否则为非连续登录。
计算出is_cont,然后根据is_cont的标识进行筛选初试连续的日期。比如用户1在25,26,27三天连续登录，筛选出其第一天的日期25。
select id,login_date,offset_day1,offset_day2
,if(offset_day1 = offset_day2,1,0) as is_cont
from (
select id,login_date
,lead(login_date,2) over(partition by id order by login_date) as offset_day1
,date_add(login_date,2) as offset_day2
from temp.user_login_log
) a
order by id,login_date
数据结果
| id | login_date | offset_day1 | offset_day2 | is_cont |
|---|---|---|---|---|
| 1 | 2024-04-25 | 2024-04-27 | 2024-04-27 | 1 |
| 1 | 2024-04-26 | 2024-04-28 | 2024-04-28 | 1 |
| 1 | 2024-04-27 | 2024-04-30 | 2024-04-29 | 0 |
| 1 | 2024-04-28 | 2024-05-01 | 2024-04-30 | 0 |
| 1 | 2024-04-30 | 2024-05-02 | 2024-05-02 | 1 |
| 1 | 2024-05-01 | 2024-05-04 | 2024-05-03 | 0 |
| 1 | 2024-05-02 | 2024-05-05 | 2024-05-04 | 0 |
| 1 | 2024-05-04 | (null) | 2024-05-06 | 0 |
| 1 | 2024-05-05 | (null) | 2024-05-07 | 0 |
| 2 | 2024-04-25 | 2024-05-02 | 2024-04-27 | 0 |
| 2 | 2024-04-28 | 2024-05-03 | 2024-04-30 | 0 |
| 2 | 2024-05-02 | 2024-05-04 | 2024-05-04 | 1 |
| 2 | 2024-05-03 | (null) | 2024-05-05 | 0 |
| 2 | 2024-05-04 | (null) | 2024-05-06 | 0 |
可以看出用户1在25号和26号以及30号存在连续登录3天的记录。用户2在05-02存在连续登录三天的记录。
3.筛选连续登录日期
筛选出开始连续登录的日期
select id,login_date,offset_day1,offset_day2
,if(offset_day1 = offset_day2,1,0) as is_cont
from (
select id,login_date,offset_day1,offset_day2
,if(offset_day1 = offset_day2,1,0) as is_cont
from (
select id,login_date
,lead(login_date,2) over(partition by id order by login_date) as offset_day1
,date_add(login_date,2) as offset_day2
from temp.user_login_log
) a
) b where b.is_cont = 1
order by id,login_date
数据结果
| id | login_date | offset_day1 | offset_day2 | is_cont |
|---|---|---|---|---|
| 1 | 2024-04-25 | 2024-04-27 | 2024-04-27 | 1 |
| 1 | 2024-04-26 | 2024-04-28 | 2024-04-28 | 1 |
| 1 | 2024-04-30 | 2024-05-02 | 2024-05-02 | 1 |
| 2 | 2024-05-02 | 2024-05-04 | 2024-05-04 | 1 |
筛选出来的结果。
至此，这个可以作为另外一个题目：
判断用户连续登录n天的次数。
实际例子，判断用户连续登录三天的次数。用户1为3次，用户2为1次。
4.生成维表
生成一个0,1,2三行记录的数据。
select explode(array(0,1,2)) as date_list;
这里面涉及到了hive的explode爆炸函数。
数据结果
| date_list |
|---|
| 0 |
| 1 |
| 2 |
5.得到预期结果
通过3和4步骤的结果表进行笛卡尔积，得到最终结果。
select c.id,c.login_date
,date_add(c.login_date,d.date_list) as login_date_list
from (
select id,login_date,offset_day1,offset_day2
,if(offset_day1 = offset_day2,1,0) as is_cont
from (
select id,login_date,offset_day1,offset_day2
,if(offset_day1 = offset_day2,1,0) as is_cont
from (
select id,login_date
,lead(login_date,2) over(partition by id order by login_date) as offset_day1
,date_add(login_date,2) as offset_day2
from temp.user_login_log
) a
) b where b.is_cont = 1
) c
,(
select explode(array(0,1,2)) as date_list
) d
order by id,login_date_list
数据结果
| id | login_date | login_date_list |
|---|---|---|
| 1 | 2024-04-25 | 2024-04-25 |
| 1 | 2024-04-25 | 2024-04-26 |
| 1 | 2024-04-25 | 2024-04-27 |
| 1 | 2024-04-26 | 2024-04-26 |
| 1 | 2024-04-26 | 2024-04-27 |
| 1 | 2024-04-26 | 2024-04-28 |
| 1 | 2024-04-30 | 2024-04-30 |
| 1 | 2024-04-30 | 2024-05-01 |
| 1 | 2024-04-30 | 2024-05-02 |
| 2 | 2024-05-02 | 2024-05-02 |
| 2 | 2024-05-02 | 2024-05-03 |
| 2 | 2024-05-02 | 2024-05-04 |
可以看到用户连续登录的记录在login_date_list列完整展现出来了，和预期结果一致。
5.衍生问题解答
如果求解的不是用户连续三天登录所有记录，而是连续登录之外的断点记录呢？
用另一句话说就是用户哪天没有登录的记录？
这个需要我们进行维表数据生成以进行数据求解。
下期进行完整解答。
以上，本期全部内容。
感谢阅读。
按例，欢迎点击此处关注我的个人公众号，交流更多知识。
