电信专线可以做网站吗,贵阳建站推广公司,移动优化课主讲:夫唯老师,0资本建设网站目录 1 题目2 建表语句3 题解 1 题目 找出所有连续未登录5天及以上的用户并提取出这些用户最近一次登录的日期
样例数据
-----------------------------------------------
| user_login.user_id | user_login.login_date |
---------------------------------------------… 目录 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 |
--------------------------------------
