经典SQL：连续登录问题

经典SQL问题：查询连续登录 n 天及以上的用户

这类问题更一般的情况是连续出现 n 次的数据，如下：

mysql> select name from stu;
+--------+
| name   |
+--------+
| 张三   |
| 李四   |
| 王五   |
| 李四   |
| 张三   |
| 赵六   |
| 张三   |
| 张三   |
| 张三   |
| 赵六   |
| 张三   |
| 赵六   |
| 赵六   |
| 赵六   |
+--------+

如何查询连续出现三次及以上的姓名呢？思路如下：

第一步：既然要求，那首先就要思考如何给这类数据一个严格的顺序来表示这些数据出现的次序，显然需要额外增加一列保证第一个出现的序号是1，第二个出现的序号是2，以此类推...实现这个很简单开窗即可(Oracle直接ROWNUM)

mysql> select name,row_number() over() as `rank` from stu;
+--------+------+
| name   | rank |
+--------+------+
| 张三   |    1 |
| 李四   |    2 |
| 王五   |    3 |
| 李四   |    4 |
| 张三   |    5 |
| 赵六   |    6 |
| 张三   |    7 |
| 张三   |    8 |
| 张三   |    9 |
| 赵六   |   10 |
| 张三   |   11 |
| 赵六   |   12 |
| 赵六   |   13 |
| 赵六   |   14 |
+--------+------+

第二步：接下来考虑如何实现连续，既然有了顺序，我们只需要保证相同名字的 rank 按顺序递增 1 即可和上面的做法一样，只不过按 name 分组按 rank 排序

mysql> select name, num , row_number() over (partition by name order by num ) p
    ->          from (
    ->                   select name, row_number() over () num from stu
    ->               ) t;
+--------+-----+---+
| name   | num | p |
+--------+-----+---+
| 张三   |   1 | 1 |
| 张三   |   5 | 2 |
| 张三   |   7 | 3 |
| 张三   |   8 | 4 |
| 张三   |   9 | 5 |
| 张三   |  11 | 6 |
| 李四   |   2 | 1 |
| 李四   |   4 | 2 |
| 王五   |   3 | 1 |
| 赵六   |   6 | 1 |
| 赵六   |  10 | 2 |
| 赵六   |  12 | 3 |
| 赵六   |  13 | 4 |
| 赵六   |  14 | 5 |
+--------+-----+---+

可以看出此时的 p 一定是连续的，如果 num 是也是连续的，那么 num - p 一定是同一个值

mysql> select name, num - row_number() over (partition by name order by num ) p
    ->          from (
    ->                   select name, row_number() over () num from stu
    ->               ) t;
+--------+---+
| name   | p |
+--------+---+
| 张三   | 0 |
| 张三   | 3 |
| 张三   | 4 |
| 张三   | 4 |
| 张三   | 4 |
| 张三   | 5 |
| 李四   | 1 |
| 李四   | 2 |
| 王五   | 2 |
| 赵六   | 5 |
| 赵六   | 8 |
| 赵六   | 9 |
| 赵六   | 9 |
| 赵六   | 9 |
+--------+---+

第三步：这个时候只要相同 name 下 p 一样的个数是多少，那么 name 就连续出现过几次，比如张三：0出现1次，3出现1次，4出现3次；那么张三属于连续出现1次和连续出现3次，得出这个结果只需要按 name 分组取 count 即可，最终sql如下：

mysql> select name
    -> from (
    ->          select name, num - row_number() over (partition by name order by num ) p
    ->          from (
    ->                   select name, row_number() over () num from stu
    ->               ) t
    ->      ) t
    -> group by name, p
    -> having count(1) >= 3;
+--------+
| name   |
+--------+
| 张三   |
| 赵六   |
+--------+

这个时候我们再回到连续登录的问题，套用上面的思路(注意变通和考虑实际业务)

考虑一个用户一天内可能多次登录，因此第一步按天进行去重，得到用户唯一id和登录的日期；下一步还需要生成连续的序号吗？其实不需要了日期就可以起到需要的作用！相当于我们第一步天然被完成，接下来我们只需要按用户唯一id，生成一个组内连续字段 p 即可，只要登录日期连续，日期减 p 的值就一定一样，最后按用户唯一id和减的结果分区求count即可。

数据如下：

mysql> select * from user;
+------+---------------------+
| id   | loginTime           |
+------+---------------------+
|    1 | 2021-11-09 18:53:34 |
|    1 | 2021-11-09 18:47:34 |
|    1 | 2021-11-08 18:53:34 |
|    1 | 2021-11-07 18:53:34 |
|    1 | 2021-11-06 18:53:34 |
|    1 | 2021-11-06 21:53:34 |
|    1 | 2021-11-06 19:53:34 |
|    2 | 2021-11-09 18:53:34 |
|    2 | 2021-11-08 18:53:34 |
|    2 | 2021-11-07 18:53:34 |
|    3 | 2021-11-09 17:53:34 |
|    3 | 2021-11-09 18:53:34 |
|    3 | 2021-11-09 19:53:34 |
|    3 | 2021-11-09 20:53:34 |
+------+---------------------+

sql如下：

mysql> select id
    -> from (
    ->          select id, str_to_date(dt, '%Y-%m-%d') - row_number() over (partition by id order by dt) p
    ->          from (
    ->                   select distinct id, substr(loginTime, 1, 10) dt
    ->                   from user
    ->               ) t
    ->      ) t
    -> group by id, p
    -> having count(1) >= 3;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+