1 最多连胜次数
题目：获取player_id最多连胜天数，如果中间有draw失败连胜重置计算
首先对player_id分区排序，再对player_id,result分区排序，如图上右侧
两者相减的结果如果相同则算为同一个组，然后sum每个组获胜的1值即可获得结果
select
player_id,
max(cnt) as cnt
from
(
select
player_id,
num1-num2,
sum(if_win) as cnt
from
(
select
player_id,
if(result='win','1','0') as if_win,
row_number () over (partition by player_id order by match_day) as num1,
row_number () over (partition by player_id, result order by match_day) as num2
from
matches
) t
group by
player_id,
num1-num2,
) t
group by
player_id;
2 如上SQL依然有问题，因为没有考虑日期的连贯性，需要加入datediff来判断
select
player_id,
max(cnt) as cnt
from
(
select
player_id,
dt_num-num2,
sum(if_win) as cnt
from
(
select
player_id,
if(result='win','1','0') as if_win,
datediff(match_day,'1997-01-01') as dt_num,
row_number () over (partition by player_id, result order by match_day) as num2
from
matches
) t
group by
player_id,
dt_num-num2,
) t
group by
player_id;
2 直播间访问人数峰值
问题：
解题过程：
SQL代码
select
max(num) as num,
from
(
select
id,
dt,
live,
sum(live) over (partition by id order by dt) as num
from
(
select
id,
stt as dt,
1 as live
from live
union all
select
id,
edt as dt,
-1 as live
from live
) t
group by dt
) t
3 相关关注用户
问题：user1和user2两张表，如图上，输出互相关联的结果即a b/b a，排除c d
select
t1.user1,
t1.user2
from
(
select
user1,
user2
from guanzhu
group by
user1,
user2
) t1
join
(
select
user1,
user2
from guanzhu
group by
user1,
user2
) t2
on t1.user1 = t2.user2
and t2.user1 = t1.user2
