1369. 获取最近第二次的活动（难度：困难）

题目描述

（通过次数5,180 | 提交次数8,334，通过率62.16%）

表: UserActivity

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| username      | varchar |
| activity      | varchar |
| startDate     | Date    |
| endDate       | Date    |
+---------------+---------+
该表不包含主键
该表包含每个用户在一段时间内进行的活动的信息
名为 username 的用户在 startDate 到 endDate 日内有一次活动

写一条SQL查询展示每一位用户 最近第二次 的活动
如果用户仅有一次活动，返回该活动
一个用户不能同时进行超过一项活动，以 任意 顺序返回结果
下面是查询结果格式的例子。

示例 1：
输入：
UserActivity 表:
+------------+--------------+-------------+-------------+
| username   | activity     | startDate   | endDate     |
+------------+--------------+-------------+-------------+
| 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  |
+------------+--------------+-------------+-------------+
输出：
+------------+--------------+-------------+-------------+
| 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 只有一条记录，我们就取这条记录

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/get-the-second-most-recent-activity

//测试数据
Create table If Not Exists UserActivity (username varchar(30), activity varchar(30), startDate date, endDate date);

insert into UserActivity (username, activity, startDate, endDate) values ('Alice', 'Travel', '2020-02-12', '2020-02-20');
insert into UserActivity (username, activity, startDate, endDate) values ('Alice', 'Dancing', '2020-02-21', '2020-02-23');
insert into UserActivity (username, activity, startDate, endDate) values ('Alice', 'Travel', '2020-02-24', '2020-02-28');
insert into UserActivity (username, activity, startDate, endDate) values ('Bob', 'Travel', '2020-02-11', '2020-02-18');

解题思路

力扣官方标记这是一道困难题。其实有点名过其实了。

从使用的技术上来看，主要还是对开窗函数的考查。

这个在之前的题目中，我提过多次了。

只是，这道题仅仅使用一个开窗运算，是不够的。需要通过两次开窗的运算，来得出最终结果。

对于参与2项及以上活动的用户来说，使用row_number、rank、dense_rank等开窗函数，分组排序，取出第二名即可。题目中已经明确说明，同一时间，用户只能参与一个活动。也就是说，用户参与活动的时间是不会有交叉的。那么，使用这3个开窗函数中的任意一个，都是可行的。

select
    a.username,
    a.activity,
    a.startDate,
    a.endDate,
    rank() over(partition by a.username order by a.startDate desc) rk
from UserActivity a;

对于只参与1项活动的用户来说，只需要根据用户开窗，取出数据条数，然后过滤即可。

select
    a.username,
    a.activity,
    a.startDate,
    a.endDate,
    count(1) over(partition by a.username) cnt
from UserActivity a;

参考SQL

未特别说明的情况下，参考SQL为基于MySQL8.0实现。

select
    b.username,
    b.activity,
    b.startDate,
    b.endDate
from
(
    select
        a.username,
        a.activity,
        a.startDate,
        a.endDate,
        count(1) over(partition by a.username) cnt,
        rank() over(partition by a.username order by a.startDate desc) rk
    from UserActivity a
)b
where b.cnt = 1 or b.rk = 2;