天宁常州做网站企点
目录
- 1 题目
- 2 建表语句
- 3 题解
1 题目
找出所有连续未登录5天及以上的用户并提取出这些用户最近一次登录的日期
样例数据
+---------------------+------------------------+--+
| user_login.user_id | user_login.login_date |
+---------------------+------------------------+--+
| 1 | 2022-01-01 |
| 1 | 2022-01-02 |
| 1 | 2022-01-03 |
| 1 | 2022-01-05 |
| 1 | 2022-01-06 |
| 1 | 2022-01-09 |
| 1 | 2023-01-01 |
| 2 | 2022-01-01 |
| 2 | 2022-01-03 |
| 2 | 2022-01-04 |
| 2 | 2022-01-06 |
| 2 | 2022-01-07 |
| 2 | 2022-01-08 |
| 3 | 2022-01-01 |
| 3 | 2022-01-02 |
| 3 | 2022-01-04 |
| 3 | 2022-01-05 |
| 3 | 2022-01-07 |
| 3 | 2022-01-08 |
+---------------------+------------------------+--+
2 建表语句
-- 创建用户登录数据表
CREATE TABLE user_login(user_id INT,login_date DATE
);-- 插入模拟数据
INSERT INTO user_login VALUES
(1, '2022-01-01'),
(1, '2022-01-02'),
(1, '2022-01-03'),
(1, '2022-01-05'),
(1, '2022-01-06'),
(1, '2022-01-09'),
(1, '2023-01-01'),
(2, '2022-01-01'),
(2, '2022-01-03'),
(2, '2022-01-04'),
(2, '2022-01-06'),
(2, '2022-01-07'),
(2, '2022-01-08'),
(3, '2022-01-01'),
(3, '2022-01-02'),
(3, '2022-01-04'),
(3, '2022-01-05'),
(3, '2022-01-07'),
(3, '2022-01-08');
3 题解
- 计算本次登录日期与上一次登录日期差值
select user_id,login_date,datediff(login_date, lag(login_date) over (partition by user_id order by login_date )) as dt
from user_login
执行结果
+----------+-------------+-------+--+
| user_id | login_date | dt |
+----------+-------------+-------+--+
| 1 | 2022-01-01 | NULL |
| 1 | 2022-01-02 | 1 |
| 1 | 2022-01-03 | 1 |
| 1 | 2022-01-05 | 2 |
| 1 | 2022-01-06 | 1 |
| 1 | 2022-01-09 | 3 |
| 1 | 2023-01-01 | 357 |
| 2 | 2022-01-01 | NULL |
| 2 | 2022-01-03 | 2 |
| 2 | 2022-01-04 | 1 |
| 2 | 2022-01-06 | 2 |
| 2 | 2022-01-07 | 1 |
| 2 | 2022-01-08 | 1 |
| 3 | 2022-01-01 | NULL |
| 3 | 2022-01-02 | 1 |
| 3 | 2022-01-04 | 2 |
| 3 | 2022-01-05 | 1 |
| 3 | 2022-01-07 | 2 |
| 3 | 2022-01-08 | 1 |
+----------+-------------+-------+--+
- 计算每个用户最近一次登录日期
select user_id,max(login_date) recent_login_date
from user_login
group by user_id
执行结果
+----------+--------------------+--+
| user_id | recent_login_date |
+----------+--------------------+--+
| 1 | 2023-01-01 |
| 2 | 2022-01-08 |
| 3 | 2022-01-08 |
+----------+--------------------+--+
- 合并上述两张表
select t1.user_id,t1.login_date,t1.dt,t2.user_id,t2.recent_login_date
from (select user_id,login_date,datediff(login_date, lag(login_date) over (partition by user_id order by login_date )) as dtfrom user_login) t1left join(select user_id,max(login_date) recent_login_datefrom user_logingroup by user_id) t2on t1.user_id = t2.user_id
执行结果
+-------------+----------------+--------+-------------+-----------------------+--+
| t1.user_id | t1.login_date | t1.dt | t2.user_id | t2.recent_login_date |
+-------------+----------------+--------+-------------+-----------------------+--+
| 1 | 2022-01-01 | NULL | 1 | 2023-01-01 |
| 1 | 2022-01-02 | 1 | 1 | 2023-01-01 |
| 1 | 2022-01-03 | 1 | 1 | 2023-01-01 |
| 1 | 2022-01-05 | 2 | 1 | 2023-01-01 |
| 1 | 2022-01-06 | 1 | 1 | 2023-01-01 |
| 1 | 2022-01-09 | 3 | 1 | 2023-01-01 |
| 1 | 2023-01-01 | 357 | 1 | 2023-01-01 |
| 2 | 2022-01-01 | NULL | 2 | 2022-01-08 |
| 2 | 2022-01-03 | 2 | 2 | 2022-01-08 |
| 2 | 2022-01-04 | 1 | 2 | 2022-01-08 |
| 2 | 2022-01-06 | 2 | 2 | 2022-01-08 |
| 2 | 2022-01-07 | 1 | 2 | 2022-01-08 |
| 2 | 2022-01-08 | 1 | 2 | 2022-01-08 |
| 3 | 2022-01-01 | NULL | 3 | 2022-01-08 |
| 3 | 2022-01-02 | 1 | 3 | 2022-01-08 |
| 3 | 2022-01-04 | 2 | 3 | 2022-01-08 |
| 3 | 2022-01-05 | 1 | 3 | 2022-01-08 |
| 3 | 2022-01-07 | 2 | 3 | 2022-01-08 |
| 3 | 2022-01-08 | 1 | 3 | 2022-01-08 |
+-------------+----------------+--------+-------------+-----------------------+--+
- 找出所有连续未登录5天及以上的用户
select t1.user_id,t2.recent_login_date
from (select user_id,login_date,datediff(login_date, lag(login_date) over (partition by user_id order by login_date )) as dtfrom user_login) t1left join(select user_id,max(login_date) recent_login_datefrom user_logingroup by user_id) t2on t1.user_id = t2.user_id
where t1.dt >= 5;
执行结果
+-------------+-----------------------+--+
| t1.user_id | t2.recent_login_date |
+-------------+-----------------------+--+
| 1 | 2023-01-01 |
+-------------+-----------------------+--+