【题目介绍】
该题为力扣2173,名为最多连胜的次数。
【题型分类】
属于连续专题。
官网标为困难题。
【思路分享】
这里的连续不属于时间连续,属于事件连续,采用两次 row_number() 技巧解题。
关键点:
1. 连续序列:针对事件,缺少连续序列,用 row_number() 构造连续序列,记为 rnk。
2. 不连续序列:针对事件,缺少不连续序列,用 row_number() 构造不连续序列,记为 rnk2。
3. 连续序列与不连续序列的差值:即为连续状态的分组结果,记为 grp。
题解:
# 连续(非常规) with tmp4 as (select player_id,max(streak) as longest_streak from (select player_id,grp,count(*) as streak from (select *, row_number() over(partition by player_id order by match_day) as rnk2, (rnk-row_number() over(partition by player_id order by match_day)) as grp from (select *, row_number() over(partition by player_id order by match_day) as rnk from Matches) as tmp1 where result='Win') as tmp2 group by player_id,grp) as tmp3 group by player_id) # 名单表 ,tmp as (select distinct player_id from Matches) # 左连接 select tmp.player_id,ifnull(longest_streak,0) as longest_streak from tmp left join tmp4 on tmp.player_id=tmp4.player_id
-END
标签:player,好题,number,力扣,2173,连续,id,select,row From: https://www.cnblogs.com/peitongshi/p/17018412.html