1. 注意点: timestampdiff(format, start, end) format为second, day, hour...都行,
2. 直接时间相减, 个人感觉不太严谨. 尽量避免直接使用时间加减, 容易出问题
select uv.video_id, round(sum(if(timestampdiff(second, uv.start_time, uv.end_time) >= vi.duration, 1, 0)) / count(1), 3) as avg_comp_play_rate from tb_user_video_log uv join tb_video_info vi on uv.video_id=vi.video_id where year(uv.start_time)=2021 group by uv.video_id order by avg_comp_play_rate desc