select tu.video_id,
round(count(case when timestampdiff(second,tu.start_time,tu.end_time)>=tv.duration then 1 else null end)/count(*),3) as avg_comp_play_rate
from tb_user_video_log tu
 join tb_video_info tv
on tu.video_id = tv.video_id
and year(tu.start_time) =2021
group by tu.video_id
order by avg_comp_play_rate desc

用的是join