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

-- 计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序 
-- 完播率=完成播放次数/总播放次数
-- 解题思路：先对需求进行拆解。
-- 1、以互动表为左表，使用left join 匹配信息表中每个视频对应的时长；
-- 2、使用timestampdiff函数计算每个视频start_time和end_time对应的实际播放时长；
-- 3、通过使用if来判断是否完播，再使用avg计算对应的完播率 
-- 4、补充2021年的限制条件，以及对完播率保留3位小数

select 
t1.video_id 
,round(avg(if(timestampdiff(second,start_time,end_time)>= duration,1,0)),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