遇到连续问题怎么办？

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

 

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

【解题步骤】

1.思路

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

首先对用户连续登陆进行标记

 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）

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

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

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

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

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

1 select distinct 月,用户id
2 from
3 (select 月,用户id,标记,
4        count(*) as 连续登陆天数
5 from
6 (select 月,日期,用户id,
7        (日 - 每个月登陆顺序) as 标记
8 from
9 (select 用户id,日期,
10        month(日期) as 月,
11        day(日期) as 日,
12        row_number() over (partition by month(日期), 用户id 
13                           order by 日期) as 每个月登陆顺序
14 from 用户登陆记录表
15 where 日期 between '2021-01-01' and '2021-12-31'
16 ) as t1
17 ) as t2
18 group by 月,用户id,标记
19 ) as t3
20 where 连续登陆天数 >= 2;

 

查询结果：

【本题考点】

1.考查对复杂问题的拆解能力，可以使用逻辑树分析方法，将一个复杂问题拆解为可以解决的子问题，然后逐步解决

2.对子查询的应用，当一个复杂问题需要多个子问题来解决时候，可以把每个子问题写成一个子查询

3.考查对常见函数的了解：month()、day()、count()；

4.考查对窗口函数的应用，窗口函数能解决的几类典型问题要能牢记；

【举一反三】

查询2021年每个月，连续5天都有登陆的用户数。

与原题的区别在于：

1）“连续2天”变成了“连续5天”：对最后的where条件进行修改；

2）查询“用户名单”变成了“用户数”：用group by按月分组，然后统计用户数和count(distinct 用户id)。

 
1 select 月,
2        count(distinct 用户id) as 连续5天登陆的用户数
3 from
4 (select 月,用户id,标记,
5        count(*) as 连续登陆天数
6 from
7 (select 月,日期,用户id,
8        (日 - 每个月登陆顺序) as 标记
9 from
10 (select 用户id,日期,
11        month(日期) as 月,
12        day(日期) as 日,
13        row_number() over (partition by month(日期), 用户id 
14                           order by 日期) as 每个月登陆顺序
15 from 用户登陆记录表
16 where 日期 between '2021-01-01' and '2021-12-31'
17 ) as t1
18 ) as t2
19 group by 月,用户id,标记
20 ) as t3
21 where 连续登陆天数 >= 5
22 group by 月;