【MySQL】窗口函数 Lead 运用之寻找面试候选人

力扣题

1、题目地址

1811. 寻找面试候选人

2、模拟表

表：Contests

Column Name	Type
contest_id	int
gold_medal	int
silver_medal	int
bronze_medal	int

• contest_id 是该表的主键.
• 该表包含LeetCode竞赛的ID和该场比赛中金牌、银牌、铜牌的用户id。
• 可以保证，所有连续的比赛都有连续的ID，没有ID被跳过。

表：Users

Column Name	Type
user_id	int
mail	varchar
name	varchar

• user_id 是该表的主键.
• 该表包含用户信息。

3、要求

编写 SQL 语句来返回 所有面试候选人 的姓名 name 和邮件 mail 。
当用户满足以下两个要求中的 任意一条 ，其成为 面试候选人：

• 该用户在 连续三场及更多 比赛中赢得 任意 奖牌。
• 该用户在 三场及更多不同的 比赛中赢得 金牌（这些比赛可以不是连续的）

可以以 任何顺序 返回结果。

4、示例

输入：

Contests表：

contest_id	gold_medal	silver_medal	bronze_medal
190	1	5	2
191	2	3	5
192	5	2	3
193	1	3	5
194	4	5	2
195	4	2	1
196	1	5	2

Users表：

user_id	mail	name
1	sarah@leetcode.com	Sarah
2	bob@leetcode.com	Bob
3	alice@leetcode.com	Alice
4	hercy@leetcode.com	Hercy
5	quarz@leetcode.com	Quarz

输出：

name	mail
Sarah	sarah@leetcode.com
Bob	bob@leetcode.com
Alice	alice@leetcode.com
Quarz	quarz@leetcode.com

解释：

Sarah 赢得了3块金牌 (190, 193, and 196)，所以我们将她列入结果表。
Bob在连续3场竞赛中赢得了奖牌 (190, 191, and 192)，所以我们将他列入结果表。
注意他在另外的连续3场竞赛中也赢得了奖牌 (194, 195, and 196)。
Alice在连续3场竞赛中赢得了奖牌 (191, 192, and 193)，所以我们将她列入结果表。
Quarz在连续5场竞赛中赢得了奖牌 (190, 191, 192, 193, and 194)，所以我们将他列入结果表。

5、代码编写

网友代码

with tmp as (
    # 列转行
    select contest_id, gold_medal AS medal from Contests
    union all
    select contest_id, silver_medal from Contests
    union all
    select contest_id, bronze_medal from Contests
)
select name, mail
from Users
where user_id in (
    # 三个金牌组合
    select gold_medal AS medal
    from Contests
    group by gold_medal
    having count(*)>=3
    union
    # 三个连续奖牌
    select distinct medal
    from (
        select medal, contest_id, lead(contest_id, 2) over (partition by medal order by contest_id) AS lead2
        from tmp
    ) AS one
    where contest_id + 2 = lead2
)

代码分析

首先，针对要求该用户在 三场及更多不同的 比赛中赢得 金牌（这些比赛可以不是连续的），下面代码比较好理解，就是根据用户id分组，只要取得大于等于3块金牌即可，因为要求里比赛可以是不连续的

select gold_medal AS medal
from Contests
group by gold_medal
having count(*)>=3

| medal |
| ----- |
| 1     |

第二点是要求该用户在 连续三场及更多 比赛中赢得 任意 奖牌。
1、需要先列转行

select contest_id, gold_medal AS medal from Contests
union all
select contest_id, silver_medal from Contests
union all
select contest_id, bronze_medal from Contests

| contest_id | medal |
| ---------- | ----- |
| 190        | 1     |
| 191        | 2     |
| 192        | 5     |
| 193        | 1     |
| 194        | 4     |
| 195        | 4     |
| 196        | 1     |
| 190        | 5     |
| 191        | 3     |
| 192        | 2     |
| 193        | 3     |
| 194        | 5     |
| 195        | 2     |
| 196        | 5     |
| 190        | 2     |
| 191        | 5     |
| 192        | 3     |
| 193        | 5     |
| 194        | 2     |
| 195        | 1     |
| 196        | 2     |

2、利用 Lead 窗口函数，根据 medal 进行分组，contest_id 进行排序，lead(contest_id, 2) 效果就是下向上推，用来判断是否连续获得奖牌

select medal, contest_id, lead(contest_id, 2) over (partition by medal order by contest_id) AS lead2
from (
	select contest_id, gold_medal AS medal from Contests
	union all
	select contest_id, silver_medal from Contests
	union all
	select contest_id, bronze_medal from Contests
) AS one

| medal | contest_id | lead2 |
| ----- | ---------- | ----- |
| 1     | 190        | 195   |
| 1     | 193        | 196   |
| 1     | 195        | null  |
| 1     | 196        | null  |
| 2     | 190        | 192   |
| 2     | 191        | 194   |
| 2     | 192        | 195   |
| 2     | 194        | 196   |
| 2     | 195        | null  |
| 2     | 196        | null  |
| 3     | 191        | 193   |
| 3     | 192        | null  |
| 3     | 193        | null  |
| 4     | 194        | null  |
| 4     | 195        | null  |
| 5     | 190        | 192   |
| 5     | 191        | 193   |
| 5     | 192        | 194   |
| 5     | 193        | 196   |
| 5     | 194        | null  |
| 5     | 196        | null  |

3、我们只需要判断 contest_id 加二是否等于 lead2 就行，因为题目有说所有连续的比赛都有连续的ID，没有ID被跳过，所以可以这样去判断，里面结果还需要进行去重（从上面查询出的结果就能判断得去重）

select distinct medal
from (
    select medal, contest_id, lead(contest_id, 2) over (partition by medal order by contest_id) AS lead2
	from (
		select contest_id, gold_medal AS medal from Contests
		union all
		select contest_id, silver_medal from Contests
		union all
		select contest_id, bronze_medal from Contests
	) AS one
) AS two
where contest_id + 2 = lead2

| medal |
| ----- |
| 2     |
| 3     |
| 5     |

Lead 用法可参考我之前文章

【MySQL】窗口函数 Lead 和 Lag 的运用（MySQL版本8+）以及 时间差函数 TIMESTAMPDIFF 的运用