天寧常州做網(wǎng)站長沙市seo百度關(guān)鍵詞
目錄
- 1 題目
- 2 建表語句
- 3 題解
1 題目
找出所有連續(xù)未登錄5天及以上的用戶并提取出這些用戶最近一次登錄的日期
樣例數(shù)據(jù)
+---------------------+------------------------+--+
| 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 建表語句
-- 創(chuàng)建用戶登錄數(shù)據(jù)表
CREATE TABLE user_login(user_id INT,login_date DATE
);-- 插入模擬數(shù)據(jù)
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
執(zhí)行結(jié)果
+----------+-------------+-------+--+
| 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
執(zhí)行結(jié)果
+----------+--------------------+--+
| 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
執(zhí)行結(jié)果
+-------------+----------------+--------+-------------+-----------------------+--+
| 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 |
+-------------+----------------+--------+-------------+-----------------------+--+
- 找出所有連續(xù)未登錄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;
執(zhí)行結(jié)果
+-------------+-----------------------+--+
| t1.user_id | t2.recent_login_date |
+-------------+-----------------------+--+
| 1 | 2023-01-01 |
+-------------+-----------------------+--+