select a.video_id,
       round(sum(case when end_time-start_time>=b.duration then 1 else 0 end )/count(1),3) as    avg_comp_play_rate
from tb_user_video_log as a
left join tb_video_info as b
on a.video_id = b.video_id
where year(a.start_time)='2021'
group by a.video_id
order by avg_comp_play_rate desc
用if(end_time-start_time>=b.duration,1,0)也可求出结果