{首页主词},&

今天，我們來繼續數據庫面試題的話題，這次給大家整理的是連續登陸的問題，題目如下：

我們先根據題目中的字段自己創建了表，填充一下相關的數據

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培訓服務經驗，采用全程面授高品質、高體驗培養模式，擁有國內一體化教學管理及學員服務，助力更多學員實現高薪夢想。

連續登陸的數據庫面試題