一、题目
表: t3_user_activity
--------------- ---------
| Column Name | Type |
--------------- ---------
| username | string |
| activity | string |
| start_date | string |
| end_date | string |
--------------- ---------
该表不包含主键 该表包含每个用户在一段时间内进行的活动的信息 名为 username 的用户在 startDate 到 endDate 日内有一次活动
写一条SQL查询展示每一位用户 最近第二次 的活动
如果用户仅有一次活动，返回该活动.
一个用户不能同时进行超过一项活动，以 任意 顺序返回结果
下面是查询结果格式的例子：
t3_user_activity 表:
代码语言：javascript复制 ----------- ----------- ------------- -------------
| username | activity | start_date | end_date |
----------- ----------- ------------- -------------
| Alice | Travel | 2020-02-12 | 2020-02-20 |
| Alice | Dancing | 2020-02-21 | 2020-02-23 |
| Alice | Travel | 2020-02-24 | 2020-02-28 |
| Bob | Travel | 2020-02-11 | 2020-02-18 |
----------- ----------- ------------- -------------
Result:
代码语言：javascript复制 ------------ -------------- ------------- -------------
| username | activity | startDate | endDate |
------------ -------------- ------------- -------------
| Alice | Dancing | 2020-02-21 | 2020-02-23 |
| Bob | Travel | 2020-02-11 | 2020-02-18 |
------------ -------------- ------------- -------------
Alice 最近第二次的活动是从 2020-02-24 到 2020-02-28 的旅行, 在此之前的 2020-02-21 到 2020-02-23 她进行了舞蹈 Bob 只有一条记录，我们就取这条记录
二、分析
本题难点在于：需要根据数据内容，决定取出时第二次还是仅有的一次。
维度 | 评分 |
|---|---|
题目难度 | ⭐️⭐️⭐️ |
题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
业务常见度 | ⭐️⭐️⭐️ |
三、SQL
1.计算参加活动的次序
使用row_numberh函数开窗，计算出按照时间倒叙的排名，由于活动不能同时进行，所以用startDate或者endDate都可以 执行SQL
代码语言：javascript复制select username,
activity,
start_date,
end_date,
row_number() over (partition by username order by start_date desc) as rn
from t3_user_activity；
SQL结果
代码语言：javascript复制 ----------- ----------- ------------- ------------- -----
| username | activity | start_date | end_date | rn |
----------- ----------- ------------- ------------- -----
| Alice | Travel | 2020-02-24 | 2020-02-28 | 1 |
| Alice | Dancing | 2020-02-21 | 2020-02-23 | 2 |
| Alice | Travel | 2020-02-12 | 2020-02-20 | 3 |
| Bob | Travel | 2020-02-11 | 2020-02-18 | 1 |
----------- ----------- ------------- ------------- -----
2.再次使用row_number开窗
先限制rn小于等于2，然后再次使用row_number进行开窗，根据用户分组，rn倒叙排列，得到rn1 执行SQL
代码语言：javascript复制select username,
activity,
start_date,
end_date,
rn,
row_number() over (partition by username order by rn desc) as rn1
from (select username,
activity,
start_date,
end_date,
row_number() over (partition by username order by start_date desc) as rn
from t3_user_activity) t
where rn <= 2
SQL结果
代码语言：javascript复制 ----------- ----------- ------------- ------------- ----- ------
| username | activity | start_date | end_date | rn | rn1 |
----------- ----------- ------------- ------------- ----- ------
| Alice | Dancing | 2020-02-21 | 2020-02-23 | 2 | 1 |
| Alice | Travel | 2020-02-24 | 2020-02-28 | 1 | 2 |
| Bob | Travel | 2020-02-11 | 2020-02-18 | 1 | 1 |
----------- ----------- ------------- ------------- ----- ------
3.限制rn1，取出最终结果
查看上面结果，我们发现rn1=1的数据即为想要的数据结果，限制查询出结果。
执行SQL
代码语言：javascript复制select username,
activity,
start_date,
end_date
from (select username,
activity,
start_date,
end_date,
rn,
row_number() over (partition by username order by rn desc) as rn1
from (select username,
activity,
start_date,
end_date,
row_number() over (partition by username order by start_date desc) as rn
from t3_user_activity) t
where rn <= 2) tt
where rn1 = 1 ;
SQL结果
代码语言：javascript复制 ----------- ----------- ------------- -------------
| username | activity | start_date | end_date |
----------- ----------- ------------- -------------
| Alice | Dancing | 2020-02-21 | 2020-02-23 |
| Bob | Travel | 2020-02-11 | 2020-02-18 |
----------- ----------- ------------- -------------
四、建表语句和数据插入
代码语言：javascript复制--建表语句
CREATE TABLE t3_user_activity(
username string,
activity string,
start_date string,
end_date string
) COMMENT '用户活动表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY 't'
;
-- 插入数据
insert into t3_user_activity(username,activity,start_date,end_date)
values
('Alice','Travel','2020-02-12','2020-02-20'),
('Alice','Dancing','2020-02-21','2020-02-23'),
('Alice','Travel','2020-02-24','2020-02-28'),
('Bob','Travel','2020-02-11','2020-02-18');
