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

select 
    a.video_id,
    round(count(case when timestampdiff(second,start_time,end_time)>=duration then a.video_id end)/count(a.video_id),3) as avg_comp_play_rate
from 
    tb_user_video_log a
    left join 
    tb_video_info b 
        on (a.video_id=b.video_id)
where year(start_time) = '2021'
group by a.video_id
order by avg_comp_play_rate desc

注：mysql计算秒之差只能用timestampdiff(second,start_time,end_time)，或者直接用时间相减（end_time-start_time）