问题:假设存在一张用户-视频观看记录表,分别记录了用户id user_id,视频id video_id,用户进入时间enter_time、用户离开时间left_time,现在想要计算每个视频同时在线的最大人数,如何用sql求解?
分析思路:观看记录表实际上记录了每个用户每次的观看行为和观看时长,用户每次进出视频是一段时间范围,在这段时间范围内可能同时有其他用户进出。极端举例:假设一个视频总共只有两个用户观看,A用户在12:00:00进入,在12:00:08离开,B用户在12:00:05进入,在12:00:10离开。那么这个视频在12:00:00至12:00:04时同时在线用户数为1;在12:00:05至12:00:07时同时在线为2;在12:00:08至12:00:10时同时在线人数为1;那么该视频最大同时在线人数就是2。反之,如果两个用户的观看记录周期没有交集,那么最大同时在线人数仅为1。
求解方式:从这个简单的例子可以看出,最大同时在线人数其实和用户每一次进入和离开的时间节点有关。我们需要将所有用户的离开和进入时间节点排序,同时对进入标记用户+1,离开标记用户-1,根据每个节点对标记值进行累计求和计算,就能得到在每个节点时的在线人数。然后对人数求最大值,就能得到该视频的最大在线人数。
实现代码:从求解方式的【排序】、【累计求和】这两个关键词就可以推导出我们需要用到窗口函数,具体代码如下:
select
video_id,max(user_cnt) max_user_cnt
from(
select
video_id
,log_time
,sum(user_cnt_flag)over(partition by video_id order by log_time asc,user_cnt_flag
desc) user_cnt
from
--对用户进出视频记录加标记,并转化成行数据
(
select user_id,video_id,enter_time log_time , 1 user_cnt_flag
from table
union all
select user_id,video_id,left_time log_time , -1 user_cnt_flag
from table
) t
) tt
group by video_id