# Write your MySQL query statement below
2173. 最多连胜的次数
表: Matches
+-------------+------+
| Column Name | Type |
+-------------+------+
| player_id | int |
| match_day | date |
| result | enum |
+-------------+------+
(player_id, match_day) 是该表的主键（具有唯一值的列的组合）。
每一行包括了：参赛选手 id、 比赛时间、 比赛结果。
比赛结果（result）的枚举类型为 ('Win', 'Draw', 'Lose')。
选手的 连胜数 是指连续获胜的次数，且没有被平局或输球中断。
编写解决方案来计算每个参赛选手最多的连胜数。
结果可以以 任何顺序 返回。
结果格式如下例所示：
示例 1:
输入: Matches 表: +-----------+------------+--------+ | player_id | match_day | result | +-----------+------------+--------+ | 1 | 2022-01-17 | Win | | 1 | 2022-01-18 | Win | | 1 | 2022-01-25 | Win | | 1 | 2022-01-31 | Draw | | 1 | 2022-02-08 | Win | | 2 | 2022-02-06 | Lose | | 2 | 2022-02-08 | Lose | | 3 | 2022-03-30 | Win | +-----------+------------+--------+ 输出: +-----------+----------------+ | player_id | longest_streak | +-----------+----------------+ | 1 | 3 | | 2 | 0 | | 3 | 1 | +-----------+----------------+ 解释: Player 1: 从 2022-01-17 到 2022-01-25, player 1连续赢了三场比赛。 2022-01-31, player 1 平局. 2022-02-08, player 1 赢了一场比赛。 最多连胜了三场比赛。 Player 2: 从 2022-02-06 到 2022-02-08, player 2 输了两场比赛。 最多连赢了0场比赛。 Player 3: 2022-03-30, player 3 赢了一场比赛。 最多连赢了一场比赛。
进阶: 如果我们想计算最长的连续不输的次数（即获胜或平局），你将如何调整？
原站题解
mysql 解法, 执行用时: 494 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 23:04:35
select t3.player_id, max(if(result = 'Win', longest_streak, 0)) as longest_streak
from (select player_id, logic_group, result, count(1) as longest_streak
from (select *, sum(is_continuity) over (partition by player_id order by match_day) as logic_group
from (select *, if(result != lag(result) over (partition by player_id order by match_day), 1, 0) as is_continuity from Matches) t1) t2
group by player_id, logic_group, result) t3
group by t3.player_id
order by t3.player_id
mysql 解法, 执行用时: 478 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 23:04:13
with t as(
select player_id, match_day,
row_number() over(partition by player_id order by match_day) as r
from Matches
), t1 as(
select player_id, match_day,
row_number() over(partition by player_id order by match_day) as r1
from Matches
where result = 'win'
), t2 as(
select t.player_id, r-r1 as gap
from t left join t1 on t.player_id=t1.player_id and t.match_day=t1.match_day
), t3 as(
select player_id, count(gap) as consecutive
from t2
group by player_id, gap
)
select player_id, max(consecutive) as longest_streak
from t3
group by player_id