2173. 最多连胜的次数

SQL架构

表: Matches

+-------------+------+
| Column Name | Type |
+-------------+------+
| player_id   | int  |
| match_day   | date |
| result      | enum |
+-------------+------+
(player_id, match_day) 是该表的主键。
每一行包括了：参赛选手 id、 比赛时间、 比赛结果。
比赛结果（result）的枚举类型为 ('Win', 'Draw', 'Lose')。

选手的 连胜数 是指连续获胜的次数，且没有被平局或输球中断。

写一个SQL 语句来计算每个参赛选手最多的连胜数。

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

结果格式如下例所示：

示例 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 赢了一场比赛。
最多连赢了一场比赛。

进阶: 如果我们想计算最长的连续不输的次数（即获胜或平局），你将如何调整？

# Write your MySQL query statement below
-- select
-- player_id,count(1) longest_streak
-- from
-- (
-- select
-- player_id,match_day ,subdate( match_day,interval  row_number() over(partition by player_id order by match_day) day ) sb
-- from
-- Matches
-- where result = 'Win'
-- ) s1
-- 
-- group by player_id, sb
with  t0  as (
   select
   distinct player_id
   from
   Matches
)
, t1 as (select #对player_id 分组 按 日期顺序标号 
player_id,match_day , row_number() over(partition by player_id order by match_day) ro,result
from
Matches
), t2 as (
select
player_id, ro - row_number() over(partition by player_id order by ro) sb
from
t1
where result = 'win'
)
,t3 as (
select
player_id,max(c) longest_streak 
from
(
select
player_id,count(1) c
from
t2
group by
player_id,sb
) s1
group by player_id)


select
t0.player_id,ifnull(longest_streak,0)  longest_streak
from
t0 left join t3
using(player_id)

先按照id分组，按照日期排序给出rk1，再按照日期和结果分组，按照日期给出rk2:

# Write your MySQL query statement below
#计算连续次数:
#1、先按照id分组，按照日期排序给出rk1，再按照日期和结果分组，按照日期给出rk2
#2、按照id和rk1-rk2进行分组，并且统计id连续结果为胜的次数
#3、再group by求最大的连胜次数

with temp as
(
    select
    player_id,sum(result='Win') s 
    from
    (
        select player_id,match_day,result,
        row_number() over (partition by player_id order by match_day) rk1,
        row_number() over (partition by player_id,result order by match_day) rk2
        from Matches
    )a
    group by player_id,rk1-rk2
)

select 
player_id,max(s) longest_streak
from temp 
group by player_id