SQL抖音面试题：遇到连续问题怎么办？（按月、日分组汇总）

【抖音面试题】

有一张“用户登陆记录表”，包含两个字段：用户id、日期。

【问题】查询2021年每个月，连续2天都有登陆的用户名单。

【解题步骤】

1.思路

怎么能知道连续登陆用户呢？

首先对用户连续登陆进行标记，也就是日期相同的打赏同一个标记（如下图）。

然后，用登陆日期的“天”和“每个月登陆顺序”的差值来做标记（如下图）。这样就可以知道，当登陆日期连续时，差值就是相同的，代表这些天用户是连续登陆。

根据上图的标记，怎么查询出每个用户每个月连续登陆的天数呢？

可以用分组汇总，也就是分组（group by 月,用户id），统计（对分组后每个组计数就是连续登陆的天数 count）

2. 子查询

1）获取登陆日期的天，需要用到day()函数；

2）获取登录日期的月，需要用到month()函数；

3）获取每个月登陆顺序，这类问题属于“每个+排序”，要想到用《猴子 从零学会SQL》里讲过的知识，用到窗口函数row_number()；

4）筛选出2021年的数据。

把上面内容写成SQL就是：

select 用户id,日期,
       month(日期) as 月,
       day(日期) as 日,
       row_number() over (partition by month(日期), 用户id 
                          order by 日期) as 每个月登陆顺序
from 用户登陆记录表
where 日期 between '2021-01-01' and '2021-12-31';

 查询结果（把这个SQL记为子查询t1）：

用“天”和“每个月登陆顺序”的差值来做标记，也就是上面我们说过的这个图：

 

select 月,日期,用户id,
       (日 - 每个月登陆顺序) as 标记
from t1;

 查询结果（把这个SQL记为子查询t2）：

3. 汇总分析

1）分组汇总：查询每个月，每个用户，每一次连续登陆的天数。

也就是分组（group by 月,用户id,标记），统计（对分组后每个组计数就是连续登陆的天数 count）

select 月,用户id,标记,
       count(*) as 连续登陆天数
from t2
group by 月,用户id,标记;

 查询结果（把这个SQL记为子查询t3）：

2）在上一步的基础上，用where子句筛选出连续2天都有登陆的用户：

select distinct 月,用户id
from t3
where 连续登陆天数 >= 2;

 到这里我们已经得到了题目要求的结果，可以把前面的子查询t1、t2、t3代入上面的SQL语句，就得到了最终的SQL：

select distinct 月,用户id
from
(select 月,用户id,标记,
       count(*) as 连续登陆天数
from
(select 月,日期,用户id,
       (日 - 每个月登陆顺序) as 标记
from
(select 用户id,日期,
       month(日期) as 月,
       day(日期) as 日,
       row_number() over (partition by month(日期), 用户id 
                          order by 日期) as 每个月登陆顺序
from 用户登陆记录表
where 日期 between '2021-01-01' and '2021-12-31'
) as t1
) as t2
group by 月,用户id,标记
) as t3
where 连续登陆天数 >= 2;

第二种方法：表自连

详情可参考往期文章 ：SQL面试题——找出连续出现N次的内容（扩展连续登陆用户）_Begin to change的博客-CSDN博客

第三种办法：偏移函数

SQL拼多多面试题：如何找出连续出现N次的内容？（偏移窗口函数）_Begin to change的博客-CSDN博客

总结：

不管是上面的哪种方法，其核心的思想都是添加辅助列，然后再根据连续的天数进行判断