题解 | #各个视频的平均完播率#

计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序

思考步骤

1. 2021年即需要限定一下时间。

年：
year()
限定 2021
where year(start_time)=2021

2. 完播率 题中有提示：视频完播率是指完成播放次数占总播放次数的比例。简单起见，结束观看时间与开始播放时间的差>=视频时长时，视为完成播放。

• 即需要看下时间差与视频时长的对比

时间差函数 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)

3. 保留三位小数

round(列，3)

4. 降序排序

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