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

select t.video_id,
format(sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))/count(t.id),3) avg_comp_play_rate
    from tb_user_video_log t
    left join tb_video_info v 
    on v.video_id=t.video_id
where year(start_time)=2021
group by t.video_id
order by avg_comp_play_rate desc
-- 合并两个表内数据-让大于DURATION的行返回1，SUM得到总共完播的次数，除以COUNT总播放次数得到