select c.video_id, round(sum(c.if_finish)/count(c.if_finish),3) as avg_comp_play_rate from (select a.uid, a.video_id, a.start_time, a.end_time, IF(timestampdiff(second,a.start_time, a.end_time)>=b.duration,1,0) as if_finish from tb_user_video_log a left join tb_video_info b on a.video_id = b.video_id where year(a.start_time) =2021) c group by c.video_id order by avg_comp_play_rate desc

需要注意:

  1. round函数
  2. year函数，提取2021年的数据
  3. 降序排序
  4. timestampdiff函数计算播放时间