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 赢了一场比赛。 最多连赢了一场比赛。
进阶: 如果我们想计算最长的连续不输的次数(即获胜或平局),你将如何调整?
- # Write your MySQL query statement below
- -- select
- -- player_id,count(1) longest_streak
- -- from
- -- (
- -- select
- -- player_id,match_day ,subdate( match_day,interval row_number() over(partition by player_id order by match_day) day ) sb
- -- from
- -- Matches
- -- where result = 'Win'
- -- ) s1
- --
- -- group by player_id, sb
- with t0 as (
- select
- distinct player_id
- from
- Matches
- )
- , t1 as (select #对player_id 分组 按 日期顺序标号
- player_id,match_day , row_number() over(partition by player_id order by match_day) ro,result
- from
- Matches
- ), t2 as (
- select
- player_id, ro - row_number() over(partition by player_id order by ro) sb
- from
- t1
- where result = 'win'
- )
- ,t3 as (
- select
- player_id,max(c) longest_streak
- from
- (
- select
- player_id,count(1) c
- from
- t2
- group by
- player_id,sb
- ) s1
- group by player_id)
-
-
- select
- t0.player_id,ifnull(longest_streak,0) longest_streak
- from
- t0 left join t3
- using(player_id)
先按照id分组,按照日期排序给出rk1,再按照日期和结果分组,按照日期给出rk2:
- # Write your MySQL query statement below
- #计算连续次数:
- #1、先按照id分组,按照日期排序给出rk1,再按照日期和结果分组,按照日期给出rk2
- #2、按照id和rk1-rk2进行分组,并且统计id连续结果为胜的次数
- #3、再group by求最大的连胜次数
-
- with temp as
- (
- select
- player_id,sum(result='Win') s
- from
- (
- select player_id,match_day,result,
- row_number() over (partition by player_id order by match_day) rk1,
- row_number() over (partition by player_id,result order by match_day) rk2
- from Matches
- )a
- group by player_id,rk1-rk2
- )
-
- select
- player_id,max(s) longest_streak
- from temp
- group by player_id