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

    进阶: 如果我们想计算最长的连续不输的次数(即获胜或平局),你将如何调整?

    1. # Write your MySQL query statement below
    2. -- select
    3. -- player_id,count(1) longest_streak
    4. -- from
    5. -- (
    6. -- select
    7. -- player_id,match_day ,subdate( match_day,interval row_number() over(partition by player_id order by match_day) day ) sb
    8. -- from
    9. -- Matches
    10. -- where result = 'Win'
    11. -- ) s1
    12. --
    13. -- group by player_id, sb
    14. with t0 as (
    15. select
    16. distinct player_id
    17. from
    18. Matches
    19. )
    20. , t1 as (select #对player_id 分组 按 日期顺序标号
    21. player_id,match_day , row_number() over(partition by player_id order by match_day) ro,result
    22. from
    23. Matches
    24. ), t2 as (
    25. select
    26. player_id, ro - row_number() over(partition by player_id order by ro) sb
    27. from
    28. t1
    29. where result = 'win'
    30. )
    31. ,t3 as (
    32. select
    33. player_id,max(c) longest_streak
    34. from
    35. (
    36. select
    37. player_id,count(1) c
    38. from
    39. t2
    40. group by
    41. player_id,sb
    42. ) s1
    43. group by player_id)
    44. select
    45. t0.player_id,ifnull(longest_streak,0) longest_streak
    46. from
    47. t0 left join t3
    48. using(player_id)

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

    1. # Write your MySQL query statement below
    2. #计算连续次数:
    3. #1、先按照id分组,按照日期排序给出rk1,再按照日期和结果分组,按照日期给出rk2
    4. #2、按照id和rk1-rk2进行分组,并且统计id连续结果为胜的次数
    5. #3、再group by求最大的连胜次数
    6. with temp as
    7. (
    8. select
    9. player_id,sum(result='Win') s
    10. from
    11. (
    12. select player_id,match_day,result,
    13. row_number() over (partition by player_id order by match_day) rk1,
    14. row_number() over (partition by player_id,result order by match_day) rk2
    15. from Matches
    16. )a
    17. group by player_id,rk1-rk2
    18. )
    19. select
    20. player_id,max(s) longest_streak
    21. from temp
    22. group by player_id

  • 相关阅读:
    Flink RoaringBitmap去重
    JVM之方法区
    高精度数字电容传感芯片-MDC04
    IEEE-754标准float类型在内存中的存储原理
    MySQL第七讲·怎么利用聚合函数实现高效地分组统计?
    Python变量
    从北京到南京:偶数在能源行业的数据迁移实践
    说说React的事件机制?
    SpringBoot第三方登录JustAuth
    mysql连接池DataSource,DruidDataSource的理解及其使用
  • 原文地址:https://blog.csdn.net/m0_69157845/article/details/125633321