连续登陆的数据库面试题-千锋教育

连续登陆的数据库面试题

发布时间:2022-06-07 10:43:00

发布人:wjy

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

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

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有连续登陆过