力扣-Mysql-1369-获取最近第二次的活动（困难）

一、题目来源
 

1369. 获取最近第二次的活动 - 力扣（LeetCode）

二、数据表结构

表: UserActivity

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

三、需求
 

编写解决方案展示每一位用户 最近第二次 的活动

如果用户仅有一次活动，返回该活动

一个用户不能同时进行超过一项活动，以 任意 顺序返回结果

四、示例数据

输入：
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 只有一条记录，我们就取这条记录

五、分析

1.文字分析
 

本题需求为获取最近第二次的活动；

第一步：首先我们根据用户名进行分组，因为是最近的活动，所以按照活动开始时间的降序进行排序获得每个用户参与活动的降序排名，一个人用户不能同时参加超过一项活动，所以不用考虑是否存在排名并列问题，使用row_number()排名函数即可；再统计每个人参与了几次活动

第二步：筛选出排名为2的用户，还有活动次数为1的用户，使用union进行结果连接，得到最终结果。 

2.图解

六、代码实现

with t1 AS (
    SELECT
        username,activity,startDate,endDate,
        count(*) OVER (PARTITION BY username) as cnt,
        row_number() OVER (PARTITION BY username ORDER BY startDate) as rn
    from useractivity
 )
SELECT
    username,activity,startDate,endDate
from t1
where rn = 2
UNION
SELECT
    username,
    activity,startDate,endDate
from t1
where cnt <= 1;

七、总结
 

本次题目的实现使用了CTE表达式，窗口函数等。

窗口函数是mysql8.0之后才有一种函数，窗口函数ROW_NUMBER()是一种分析函数，它为结果集中的每一行分配一个唯一的连续整数，主要使用场景为 排名场景（不存在并列，排名连续）。