力扣之2173. 最多连胜的次数
说明
准备工作
drop database if exists db_1;
create database db_1;
use db_1;
Create table If Not Exists Matches (player_id int, match_day date, result ENUM('Win', 'Draw', 'Lose'));
Truncate table Matches;
insert into Matches (player_id, match_day, result) values ('1', '2022-01-17', 'Win');
insert into Matches (player_id, match_day, result) values ('1', '2022-01-18', 'Win');
insert into Matches (player_id, match_day, result) values ('1', '2022-01-25', 'Win');
insert into Matches (player_id, match_day, result) values ('1', '2022-01-31', 'Draw');
insert into Matches (player_id, match_day, result) values ('1', '2022-02-08', 'Win');
insert into Matches (player_id, match_day, result) values ('2', '2022-02-06', 'Lose');
insert into Matches (player_id, match_day, result) values ('2', '2022-02-08', 'Lose');
insert into Matches (player_id, match_day, result) values ('3', '2022-03-30', 'Win');
分析一
实现一
drop database if exists db_1;
create database db_1;
use db_1;
Create table If Not Exists Matches (player_id int, match_day date, result ENUM('Win', 'Draw', 'Lose'));
Truncate table Matches;
insert into Matches (player_id, match_day, result) values ('1', '2022-01-17', 'Win');
insert into Matches (player_id, match_day, result) values ('1', '2022-01-18', 'Win');
insert into Matches (player_id, match_day, result) values ('1', '2022-01-25', 'Win');
insert into Matches (player_id, match_day, result) values ('1', '2022-01-31', 'Draw');
insert into Matches (player_id, match_day, result) values ('1', '2022-02-08', 'Win');
insert into Matches (player_id, match_day, result) values ('2', '2022-02-06', 'Lose');
insert into Matches (player_id, match_day, result) values ('2', '2022-02-08', 'Lose');
insert into Matches (player_id, match_day, result) values ('3', '2022-03-30', 'Win');
todo 题目要求: 选手的 连胜数 是指连续获胜的次数，且没有被平局或输球中断。
todo 编写解决方案来计算每个参赛选手最多的连胜数。
todo 结果可以以 任何顺序 返回。
– 第一步: 构建两个等差数据
with t1 as (
select
player_id, match_day, result,
row_number() over (partition by player_id order by match_day) as rn1,
row_number() over (partition by player_id, result order by match_day) as rn2
from matches
)
select
player_id, match_day, result, rn1, rn2,
(rn1 - rn2) as diff,
if(result='Win', 1, 0) result2
from t1
;
– 第一步: 构建两个等差数据
with t1 as (
select
player_id, match_day, result,
row_number() over (partition by player_id order by match_day) as rn1,
row_number() over (partition by player_id, result order by match_day) as rn2
from matches
)
, t2 as (
select
player_id,
(rn1 - rn2) as diff,
sum(if(result='Win', 1, 0)) as cnt
from t1
group by player_id, (rn1 - rn2)
)
select
player_id,
max(cnt) as longest_streak
from t2
group by player_id
;
实现二
进阶: 如果我们想计算最长的连续不输的次数（即获胜或平局），你将如何调整？
with t1 as (
select
player_id, match_day, result,
row_number() over (partition by player_id order by match_day) as rn1,
row_number() over (partition by player_id, if(result!='Lose', 'Not_Lose', 'Lose') order by match_day) as rn2
from matches
)
, t2 as (
select
player_id,
(rn1 - rn2) as diff,
sum(if(result!='Lose', 1, 0)) cnt
from t1
group by player_id, (rn1 - rn2)
)
select
player_id,
max(cnt) as 最大不输的次数
from t2
group by player_id
;
小结
- 连续
- 公式: 构建两个公差为1的等差数列
- 注意同一组差值相等的, 是连续的