题目
选手的 连胜数 是指连续获胜的次数,且没有被平局或输球中断。
编写解决方案来计算每个参赛选手最多的连胜数。
准备数据
## 创建库
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');
matches表
分析数据
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 赢了一场比赛。
最多连赢了一场比赛。
第一步:构建两个等差数列
-- 第一步: 构建两个等差数列
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;
第二步:求两个等差数列的差值
-- 第二步: 求两个等差数列的差值
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
;
说明: 只有win才是连胜,因此将不是win的设置为0。
第三步:求连胜的最大值
-- 第三步: 求连胜的最大值
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
;
总结
- 遇到求连续的天数,可以构建两个等差数列(即求排名),两者之间的差值中连续相同的行,就是连续的天数。
- 如果只有两种情况,可以使用if()函数区分开。
思考
如果我们想计算最长的连续不输的次数(即获胜或平局),你将如何调整?
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',1,0) order by match_day) as rn2
from matches
)
, t2 as (
select
player_id,
(rn1 - rn2) as diff,
sum(if(result='Lose', 0, 1)) as cnt
from t1
group by player_id, (rn1 - rn2)
)
select
player_id,
max(cnt) as longest_streak
from t2
group by player_id
;
说明:只需要把if条件变成只要不是lose,就是1。