先计算播放时长，再筛选出大于等于总时长的

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