题解 | 输出播放量最高的视频

/*给出时间段，计算时刻有关信息，设置时间点标记union all再查询*/
select t2.cid
,round(max(peak_uv),3) as max_peak_uv
from(
select cid
,sum(t1.change_num) over(partition by t1.cid order by t1.event_time)
/*要符合逻辑，所以要按时间排序*/
 as peak_uv
from(
    select cid
    ,start_time as event_time
    ,1 as change_num
    from play_record_tb
    union all
    select cid
    ,end_time as event_time
    ,-1 as change_num
    from play_record_tb
    ) t1
) t2
group by t2.cid
order by max_peak_uv desc
limit 3