LeetCode MySQL 1369. 擷取最近第二次的活動（over視窗函數）

文章目錄

- 1. 題目
- 2. 解題

1. 題目

表: UserActivity

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| username      | varchar |
| activity      | varchar |
| startDate     | Date    |
| endDate       | Date    |
+---------------+---------+
該表不包含主鍵
該表包含每個使用者在一段時間内進行的活動的資訊
名為 username 的使用者在 startDate 到 endDate 日内有一次活動

複制

寫一條SQL查詢展示每一位使用者最近第二次的活動（倒數第二次）

如果使用者僅有一次活動，傳回該活動

一個使用者不能同時進行超過一項活動，以任意順序傳回結果

下面是查詢結果格式的例子：

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  |
+------------+--------------+-------------+-------------+

Result 表:
+------------+--------------+-------------+-------------+
| 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.com/problems/get-the-second-most-recent-activity

著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。

2. 解題

先選出隻有一次活動的人的記錄

select *
from UserActivity
group by username
having count(*)=1

複制

{"headers": ["username", "activity", "startDate", "endDate"], 
"values": [["Bob", "Travel", "2020-02-11", "2020-02-18"]]}

複制

視窗函數選出每個人倒數第二次的活動

select username, activity, startDate, endDate
from
(
    select *, rank() over(partition by username order by startDate desc) rnk
    from UserActivity
) t
where rnk = 2

複制

{"headers": ["username", "activity", "startDate", "endDate"], 
"values": [["Alice", "Dancing", "2020-02-21", "2020-02-23"]]}

複制

最後合并

# Write your MySQL query statement below
select *
from
(
    select *
    from UserActivity
    group by username
    having count(*)=1

    union all
    
    select username, activity, startDate, endDate
    from
    (
        select *, rank() over(partition by username order by startDate desc) rnk
        from UserActivity
    ) t
    where rnk = 2
) t

複制

更簡潔的寫法

# Write your MySQL query statement below
select username, activity, startDate, endDate
from
(
    select *,
        rank() over (partition by username order by startDate desc) rnk,
        count(*) over (partition by username) cnt
    from UserActivity
) t
where cnt = 1 or rnk = 2

複制