力扣2173题

最新推荐文章于 2024-09-06 09:22:51 发布

骑着居居追飞机

最新推荐文章于 2024-09-06 09:22:51 发布

阅读量479

点赞数 10

文章标签： leetcode 数据库 linux

本文链接：https://blog.csdn.net/weixin_54514072/article/details/140854269

版权

力扣之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
;