题解 | 输出播放量最高的视频
输出播放量最高的视频
https://www.nowcoder.com/practice/9e9cb264e1f64e28846975d5a32ba8e4
# select cid,cnt as max_peak_uv from
# (
# select
# cid,count(1) as cnt
# from play_record_tb
# group by cid
# ) t
# order by cnt desc
# limit 3
with t1 as(
select uid,cid,start_time as time,1 as log from play_record_tb
union all
select uid,cid,end_time as time,-1 as log from play_record_tb
),
t2 as(
select cid,time,sum(log) over(partition by t1.cid order by time) as sum
from t1
)
select cid,round(max(sum),3) as max_peak_uv from t2
group by cid
order by max(sum) desc
limit 3
1.将一行内容通过开始时间和结束时间分成两行
2.对上述内容添加标志列，开始时间为1，结束时间为-1
3.根据直播间进行分组，按照时间顺序进行排序，进行开窗聚合，那么此时的sum就可以获取到每个时间点的直播间人数
查看18道真题和解析