好题分享、心路历程（力扣2173）——连续登录

标签：player 好题 number 力扣 2173 连续 id select row

【题目介绍】

该题为力扣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