题解 | #各个视频的平均完播率#
各个视频的平均完播率
http://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序
思考步骤
- 2021年即需要限定一下时间。
年：
year()
限定 2021
where year(start_time)=2021
- 完播率 题中有提示：视频完播率是指完成播放次数占总播放次数的比例。简单起见，结束观看时间与开始播放时间的差>=视频时长时，视为完成播放。
- 即需要看下时间差与视频时长的对比
时间差函数 timediff(time1,time2) 返回结果 time1-time2
timediff(t1.end_time,t1.start_time)
- 完播次数需要做次筛选
筛选函数
case when then
else end
case
when timediff(t1.end_time,t1.start_time)>=t2.duration then 1
else 0 end
求和函数 sum()
sum(case
when timediff(t1.end_time,t1.start_time)>=t2.duration then 1
else 0 end)
- 总播放次数
计数函数 count
count(start_time)
- 保留三位小数
round(列，3)
- 降序排序
order by desc
完整答案
select
t1.video_id,
round(sum(case
when timediff(t1.end_time,t1.start_time)>=t2.duration then 1
else 0 end)/
count(t1.start_time),3) as avg_comp_play_rate
from
tb_user_video_log t1 left join
tb_video_info t2 on
t1.video_id = t2.video_id
where year(start_time)=2021
GROUP BY t1.video_id
ORDER BY avg_comp_play_rate DESC