连续登陆的数据库面试题

今天，我们来继续数据库面试题的话题，这次给大家整理的是连续登陆的问题，题目如下：

日志表-连续7天登陆

我们先根据题目中的字段自己创建了表，填充一下相关的数据

create table login(
user_id int comment '用户id',
access_time datetime comment '访问时间',
page_id int comment '页面id',
dt date comment '登陆日期'
);

insert into login values
(1, '2021-06-01 11:13:15', 10, '2021-06-01'),
(1, '2021-06-02 11:13:15', 10, '2021-06-02'),
(1, '2021-06-03 11:13:15', 10, '2021-06-03'),
(1, '2021-06-04 11:13:15', 10, '2021-06-04'),
(1, '2021-06-05 11:13:15', 10, '2021-06-05'),
(1, '2021-06-06 11:13:15', 10, '2021-06-06'),
(1, '2021-06-07 11:13:15', 10, '2021-06-07'),
(2, '2021-06-01 11:13:15', 10, '2021-06-01'),
(2, '2021-06-03 11:13:15', 10, '2021-06-03'),
(2, '2021-06-04 11:13:15', 10, '2021-06-04'),
(2, '2021-06-05 11:13:15', 10, '2021-06-05'),
(3, '2021-06-01 11:13:15', 10, '2021-06-01'),
(3, '2021-06-07 11:13:15', 10, '2021-06-07'),
(3, '2021-06-08 11:13:15', 10, '2021-06-08'),
(3, '2021-06-09 11:13:15', 10, '2021-06-09'),
(3, '2021-06-10 11:13:15', 10, '2021-06-10'),
(3, '2021-06-11 11:13:15', 10, '2021-06-11'),
(3, '2021-06-12 11:13:15', 10, '2021-06-12'),
(3, '2021-06-13 11:13:15', 10, '2021-06-13'),
(4, '2021-06-01 11:13:15', 10, '2021-06-01'),
(4, '2021-06-03 11:13:15', 10, '2021-06-03'),
(4, '2021-06-05 11:13:15', 10, '2021-06-05'),
(4, '2021-06-07 11:13:15', 10, '2021-06-07'),
(4, '2021-06-09 11:13:15', 10, '2021-06-09'),
(4, '2021-06-11 11:13:15', 10, '2021-06-11'),
(5, '2021-06-01 11:13:15', 10, '2021-06-01'),
(5, '2021-06-07 11:13:15', 10, '2021-06-07'),
(5, '2021-06-08 11:13:15', 10, '2021-06-08'),
(5, '2021-06-09 11:13:15', 10, '2021-06-09'),
(5, '2021-06-11 11:13:15', 10, '2021-06-11'),
(5, '2021-06-12 11:13:15', 10, '2021-06-12'),
(5, '2021-06-13 11:13:15', 10, '2021-06-13');

接下来我们根据需求来分析完成，整理思路顺序如下：

/*
思路：
将同一用户的登陆时间归纳在一起  设置个排名  如果时间是连续的  那么 时间和排名的差值就是相等的
*/
-- 先看一下设置排名
select *, row_number() over(partition by user_id order by dt) ranking from login where month(dt) = 6;
/*
结果展示：
user_id access_time    page_id  dt  ranking
1  2021-06-01 11:13:15  10  2021-06-01  1
1  2021-06-02 11:13:15  10  2021-06-02  2
1  2021-06-03 11:13:15  10  2021-06-03  3
1  2021-06-04 11:13:15  10  2021-06-04  4
1  2021-06-05 11:13:15  10  2021-06-05  5
1  2021-06-06 11:13:15  10  2021-06-06  6
1  2021-06-07 11:13:15  10  2021-06-07  7
2  2021-06-01 11:13:15  10  2021-06-01  1
2  2021-06-03 11:13:15  10  2021-06-03  2
2  2021-06-04 11:13:15  10  2021-06-04  3  
2  2021-06-05 11:13:15  10  2021-06-05  4
3  2021-06-01 11:13:15  10  2021-06-01  1
3  2021-06-07 11:13:15  10  2021-06-07  2
3  2021-06-08 11:13:15  10  2021-06-08  3
3  2021-06-09 11:13:15  10  2021-06-09  4
3  2021-06-10 11:13:15  10  2021-06-10  5
3  2021-06-11 11:13:15  10  2021-06-11  6
3  2021-06-12 11:13:15  10  2021-06-12  7
3  2021-06-13 11:13:15  10  2021-06-13  8
4  2021-06-01 11:13:15  10  2021-06-01  1
4  2021-06-03 11:13:15  10  2021-06-03  2
4  2021-06-05 11:13:15  10  2021-06-05  3
4  2021-06-07 11:13:15  10  2021-06-07  4
4  2021-06-09 11:13:15  10  2021-06-09  5
4  2021-06-11 11:13:15  10  2021-06-11  6
5  2021-06-01 11:13:15  10  2021-06-01  1
5  2021-06-07 11:13:15  10  2021-06-07  2
5  2021-06-08 11:13:15  10  2021-06-08  3
5  2021-06-09 11:13:15  10  2021-06-09  4
5  2021-06-11 11:13:15  10  2021-06-11  5
5  2021-06-12 11:13:15  10  2021-06-12  6
5  2021-06-13 11:13:15  10  2021-06-13  7

*/

-- 将日期与排名做差
select *, date_sub(dt, interval ranking day) diff from
(select *, row_number() over(partition by user_id order by dt) ranking from login where month(dt) = 6) as t;
/*
结果展示
user_id  access_time  page_id  dt   ranking  diff
1  2021-06-01 11:13:15  10  2021-06-01  1 2021-05-31
1  2021-06-02 11:13:15  10  2021-06-02  2 2021-05-31
1  2021-06-03 11:13:15  10  2021-06-03  3 2021-05-31
1  2021-06-04 11:13:15  10  2021-06-04  4 2021-05-31
1  2021-06-05 11:13:15  10  2021-06-05  5 2021-05-31
1  2021-06-06 11:13:15  10  2021-06-06  6 2021-05-31
1  2021-06-07 11:13:15  10  2021-06-07  7 2021-05-31
2  2021-06-01 11:13:15  10  2021-06-01  1 2021-05-31
2  2021-06-03 11:13:15  10  2021-06-03  2 2021-06-01
2  2021-06-04 11:13:15  10  2021-06-04  3 2021-06-01
2  2021-06-05 11:13:15  10  2021-06-05  4 2021-06-01
3  2021-06-01 11:13:15  10  2021-06-01  1 2021-05-31
3  2021-06-07 11:13:15  10  2021-06-07  2 2021-06-05
3  2021-06-08 11:13:15  10  2021-06-08  3 2021-06-05
3  2021-06-09 11:13:15  10  2021-06-09  4 2021-06-05
3  2021-06-10 11:13:15  10  2021-06-10  5 2021-06-05
3  2021-06-11 11:13:15  10  2021-06-11  6 2021-06-05
3  2021-06-12 11:13:15  10  2021-06-12  7 2021-06-05
3  2021-06-13 11:13:15  10  2021-06-13  8 2021-06-05
4  2021-06-01 11:13:15  10  2021-06-01  1 2021-05-31
4  2021-06-03 11:13:15  10  2021-06-03  2 2021-06-01
4  2021-06-05 11:13:15  10  2021-06-05  3 2021-06-02
4  2021-06-07 11:13:15  10  2021-06-07  4 2021-06-03
4  2021-06-09 11:13:15  10  2021-06-09  5 2021-06-04
4  2021-06-11 11:13:15  10  2021-06-11  6 2021-06-05
5  2021-06-01 11:13:15  10  2021-06-01  1 2021-05-31
5  2021-06-07 11:13:15  10  2021-06-07  2 2021-06-05
5  2021-06-08 11:13:15  10  2021-06-08  3 2021-06-05
5  2021-06-09 11:13:15  10  2021-06-09  4 2021-06-05
5  2021-06-11 11:13:15  10  2021-06-11  5 2021-06-06
5  2021-06-12 11:13:15  10  2021-06-12  6 2021-06-06
5  2021-06-13 11:13:15  10  2021-06-13  7 2021-06-06
*/
-- 从数据中我们看出 如果用户是连续登陆的，那么差值的日期结果是一样的
-- 然后根据用户 与 时间差 分类 统计每个出现的次数 如果次数在7以上 表示连续7天登陆
select user_id ,count(*) from
(select *, date_sub(dt, interval ranking day) diff from
(select *, row_number() over(partition by user_id order by dt) ranking from login where month(dt)=6) as t) as t1
group by user_id, diff having count(*) >= 7;
/*
user_id count(*)
1   7
3   7
*/
-- 因此我们数据中只有1和3有连续登陆过

-END-