#获取最近第二次的活动
写一条SQL查询展示每一位用户 最近第二次 的活动,如果用户仅有一次活动,返回该活动。一个用户不能同时进行超过一项活动,以 任意 顺序返回结果。
展示效果:
+------------+--------------+-------------+-------------+
| username | activity | startDate | endDate |
+------------+--------------+-------------+-------------+
| Alice | Dancing | 2020-02-21 | 2020-02-23 |
| Bob | Travel | 2020-02-11 | 2020-02-18 |
+------------+--------------+-------------+-------------+
建表语句:
Create table If Not Exists 81_UserActivity (username varchar(30), activity varchar(30), startDate date, endDate date);
Truncate table 81_UserActivity;
insert into 81_UserActivity (username, activity, startDate, endDate) values ('Alice', 'Travel', '2020-02-12', '2020-02-20');
insert into 81_UserActivity (username, activity, startDate, endDate) values ('Alice', 'Dancing', '2020-02-21', '2020-02-23');
insert into 81_UserActivity (username, activity, startDate, endDate) values ('Alice', 'Travel', '2020-02-24', '2020-02-28');
insert into 81_UserActivity (username, activity, startDate, endDate) values ('Bob', 'Travel', '2020-02-11', '2020-02-18')
最终SQL:
select
username,
activity,
startDate,
endDate
from
(select
username,
activity,
startDate,
endDate ,
rank() over(partition by username order by startDate desc) rk,
lag(startDate ,1,null) over(partition by username order by startDate ) lg
from 81_UserActivity)t1
where
rk=2 or (rk = 1 and lg is null);