sql----连续性问题终极万能模板（2021-07-08）

CODE君这几天遇到个烦人的问题，连续性问题！！在这里看到别人的帖子备受触动，写一篇文章分享给大家，一定要收藏好，面试必备，个人能力的体现~~~~~~~~

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

【问题】查询2021年每个月，每个用户连续登陆的最多天数。

【解题步骤】

1. 连续问题的万能模板

1 select distinct 列1
2 from (
3 select 列1,
4 lead(列1,1) over(order by 序号) as 列2,
5 lead(列1,2) over(order by 序号) as 列3,
6 ...
7 lead(列1,n-1) over(order by 列) as 列n,
8 from 表名
9 ) as a
10 where (a.列1 = a.列2 and ... and a.列1 = a.列n);

2. 窗口函数

窗口函数lead使用方法：

1 lead/lag(字段名,N,默认值) over(partion by …order by …)
默认值是指：当向上N行或者向下N行值时，如果已经超出了表行和列的范围时，会将这个默认值作为函数的返回值，若没有指定默认值，则返回Null。

窗口函数lead可以获取每个字段的后面的第n个值，并生成新的一列。

而这道题描述的“用户连续登陆”中的“连续”可以理解为用户当前的登陆日期与本月下一次登陆日期相差一天。

我们可以先用窗口函数lead获取“用户当月下一个登陆日期”：

1 select 用户id,
2        month(日期) as 月,
3        日期,
4        lead(日期,1,'当月最后登陆日期') over(partition by month(日期), 用户id order by 日期) as 用户当月下一个登陆日期
5 from 用户登陆记录表;

当“日期”是该用户在当月最后一天登陆时，记录为“当月最后登陆日期”，如果不进行设置，将会返回Null，不利于理解。

从结果看，我们可以获得以下信息：

1）当“日期”与“用户当月下一个登陆日期”只相差一天，即用户本次登陆为连续登陆；

2）当“日期”与“用户当月下一个登陆日期”相差大于一天，即用户本次登陆为连续登陆的最后一天（也有可能仅登陆一天）；

3）当“用户当月下一个登陆日期”等于“当月最后登陆日期”，即用户本次登陆为本月最后一天登陆。

这样，可以判断用户连续登陆的情况。

接下来就解决用户每次连续登陆天数的计算。

3. 子查询

用户每次连续登陆天数与用户登陆顺序存在某种必然的关系，此时我们可以先用子查询将用户在本月的阅读顺序查询出来，使用窗口函数row_number:

 
1 select *,
2        lead(日期,1,'当月最后登陆日期') over(partition by 月, 用户id order by 每个月登陆顺序) as 用户当月下一个登陆日期
3 from (
4 select 用户id,
5        month(日期) as 月,
6        日期,
7        row_number() over (partition by month(日期), 用户id order by 日期) as 每个月登陆顺序
8 from 用户登陆记录表
9 ) as t1;

可以看出，当连续终止时，即：

1）“日期”与“用户当月下一个登陆日期”相差大于一天；

2）“用户当月下一个登陆日期”等于“当月最后登陆日期”；

两种情况。

将这两种情况过滤出来之后，用户连续登陆天数为：当前登陆顺序减去上一个登陆顺序。

1 select *,
2        lag(每个月登陆顺序,1) over(partition by 月, 用户id order by 每个月登陆顺序) as 上一个登陆顺序
3 from (
4 select *,
5        lead(日期,1,'当月最后登陆日期') over(partition by 月, 用户id order by 每个月登陆顺序) as 用户当月下一个登陆日期
6 from (
7 select 用户id,
8        month(日期) as 月,
9        日期,
10        row_number() over (partition by month(日期), 用户id order by 日期) as 每个月登陆顺序
11 from 用户登陆记录表
12 ) as t1
13 ) as t2
14  where date_sub(用户当月下一个登陆日期,interval 1 day) <> 日期 or 用户当月下一个登陆日期 = '当月最后登陆日期';

“上一个登陆顺序”为Null时，用0代替（使用coalesce函数），那么“每个月登陆顺序”减去“上一个登陆顺序”就是本次连续登陆天数。

【重点】：这里介绍一下coalesce函数，我也是第一次接触，很神奇！

具体怎么用呢？

上面的函数说明通俗来说coalesce()的作用是：返回传入的参数中第一个非null的值。expre1不为空值则返回expre1；否则判断expre2是否是空值，如果expre2不为空值则返回expre2；否则判断expre3是否是空值，如果expre3不为空值则返回expre3；……以此类推，如果所有的表达式都为空值，则返回NULL。

为简单起见，举例如下：

1、 input：select coalesce(NULL,NULL,1)

    output：1

2、 如果传入的参数所有都是NULL，则返回NULL，比如
input：select coalesce(NULL,NULL,NULL,NULL,NULL)
output：NULL

3、 最常用的一种用法是假设某个字段除了有实例的记录之外都是默认值NULL，但后续计算需要用到这个字段，如果为NULL的话无法计算或展示，现在想将该字段中的NULL值全部替换成0，则可以使用coalesce()函数。以下的sql能够很好的展示其具体是怎么进行操作的：

CREATE TABLE
IF NOT EXISTS test (
    id INT (11) PRIMARY KEY auto_increment COMMENT '主键id',
    score INT (11) COMMENT '分数',
    score_avg INT (11) COMMENT '平均分数'
);

INSERT INTO test(id, score,score_avg)
VALUES
    (1, 90, 80),
    (2, 56, 80),
    (3, NULL, 80),
    (4, 69, 80),
    (5, 89, 80),
    (6, 99, 80),
    (7, 100, 80),
    (8, NULL, 80),
    (9, 47, 80),
    (10, NULL, 80),
    (11, NULL, 80),
    (12, 78, 80);

以上是建表语句以及插入数据的Mysql语句。现在要将score中的NULL值替换成0。

select id,score,coalesce(score, 0) as score_act from test;

从以上结果清楚的看出，coalesce(score, 0) as score_act新的一列当score不是NULL值时取原值，当score是NULL值时取0。

4、在例子3的基础上再举一个例子。

update test set score_avg = NULL where id = 3;

则原始数据如下所示：

select id, score, score_avg, coalesce(score, score_avg, 0) as score_act from test;
结果如下：

可以看出，coalesce(score,score_avg,0) as score_act表示：

当score = 非空值时，score_act = score 例如id = 1、4

当score = NULL值时，看score_avg的值，若score_avg = 非空值，score_act = score_avg 例如id = 8、10、11

4. 汇总分析

最后获取“每个月，每个用户连续登陆的最多天数”，使用group by函数。

1 select 月,
2        用户id,
3        max(连续登陆天数) as 连续登陆的最多天数
4 from (
5 select *,
6        每个月登陆顺序 - coalesce(lag(每个月登陆顺序,1) over(partition by 月, 用户id order by 每个月登陆顺序),0) as 连续登陆天数
7 from (
8 select *,
9        lead(日期,1,'当月最后登陆日期') over(partition by 月, 用户id order by 每个月登陆顺序) as 用户当月下一个登陆日期
10 from (
11 select 用户id,
12        month(日期) as 月,
13        日期,
14        row_number() over (partition by month(日期), 用户id order by 日期) as 每个月登陆顺序
15 from 用户登陆记录表
16 ) as t1
17 ) as t2
18 where date_sub(用户当月下一个登陆日期,interval 1 day) <> 日期 or 用户当月下一个登陆日期 = '当月最后登陆日期'
19 ) as t3
20 group by 月,用户id;