关于mysql同一时刻内的最大计数解题思路

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

目录

前言

一、牛客网sql163每篇文章同一时刻最大的观看人数

1.数据源（牛客网sql163） 

2.解题思路

1.对原表编码+连立

2.滑动窗口函数累计求和

 3.求累计列的最大值

总结

---

前言

刷到牛客网sql163题可以说是完全没有思路。看了讲解发现确实是有没有学习到的东西。所以在这片文章里做一个汇总和学习。

---

一、牛客网sql163每篇文章同一时刻最大的观看人数

1.数据源（牛客网sql163） 

2.解题思路

1.对原表编码+连立

在此对原表in_time和out_time进行编码

select artical_id,in_time dt,1 diff from tb_user_log
where artical_id != 0
union all 
select artical_id,out_time dt,-1 diff from tb_user_log
where artical_id != 0 
order by artical_id,dt

知识点：

编码：直接在select中添加编码字段（如：-1 num，-1 diff，1 num，1 diff）

连立：用union,只要in_time,out_time都别名dt，这样上下链接就可以连上

下表是连立后，order by artical_id,dt后的结果，会更加清楚

2.滑动窗口函数累计求和

select artical_id,dt,diff
sum(diff) over(partition by artical_id order by dt, diff desc)
from
(select artical_id,in_time dt,1 diff from tb_user_log
where artical_id != 0
union all 
select artical_id,out_time dt,-1 diff from tb_user_log
where artical_id != 0 
order by artical_id,dt) t1

累计求和逻辑：

partition by：先将不同的文章划分为一个区域

order by：先按照时间排序累计，如果时间相同在按照diff降序累计

因为题目规定，同一时间存在进出的话，先算进在算出，虽然根据已有的表格不存在相同时间内的进出，就只要根据时间一条条累计就ok

 3.求累计列的最大值

select artical_id,max(cnt) max_uv
from
(
select artical_id,dt,diff,
sum(diff) over(partition by artical_id order by dt, diff desc) cnt
from
(
select artical_id,in_time dt,1 diff from tb_user_log
where artical_id != 0
union all 
select artical_id,out_time dt,-1 diff from tb_user_log
where artical_id != 0 
order by artical_id,dt
) t1
)t2
group by artical_id

---

 

总结

记住解题思路就行，我觉得这也是会用到滑动窗口函数累计求和的一个场景。