MySQL基础练习题18-最多连胜的次数

题目

准备数据

分析数据

 第一步:构建两个等差数列

 第二步:求两个等差数列的差值

 第三步:求连胜的最大值

总结

思考


题目

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

编写解决方案来计算每个参赛选手最多的连胜数。

准备数据

## 创建库
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。

  • 8
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值