今天，我们来继续数据库面试题的话题，这次给大家整理的是连续登陆的问题，题目如下：
我们先根据题目中的字段自己创建了表，填充一下相关的数据
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有连续登陆过
更多关于python培训的问题，欢迎咨询千锋教育在线名师。千锋教育拥有多年IT培训服务经验，采用全程面授高品质、高体验培养模式，拥有国内一体化教学管理及学员服务，助力更多学员实现高薪梦想。
京公网安备 11010802030320号