题46:
根据下表写一条SQL查询展示每一位用户最近第二次的活动,如果用户仅有一次活动,就返回该活动,注意一个用户不能同时进行超过一项活动。
其中:此表没有主键,其中包含每个用户在一段时间内进行的活动的信息
名为 username 的用户在 startDate 到 endDate 日内有一次活动。
解题思路:利用窗口函数
(1)用rank(),以username为分割点,给这个username参与的活动按时间倒序排列编号;
(2)用count()统计每个人做了多少个活动,为后续筛选打辅助;
(3)根据(1)(2)增加的辅助列筛选出符合条件的信息
select a.username ,a.activity,a.startDate,a.endDate
from (
select *,
rank() over(partition by username order by startDate desc) as rank_,
count(*) over (partition by username) as count_
from UserActivity
) as a
where rank_ = 2 or count_ = 1;