SELECT t2.video_id , round(sum(t2.isOver)/count(t2.video_id),3) `avg`
FROM (
select t.video_id,
       t.start_time,
       t.end_time,
       tvi.duration,
       case
           when (t.end_time - t.start_time) >= tvi.duration then 1
           when (t.end_time - t.start_time) < tvi.duration then 0
       end `isOver`
from tb_user_video_log as t
         join tb_video_info tvi on t.video_id = tvi.video_id) t2
where YEAR(t2.end_time)=2021
group by t2.video_id 
order by avg desc;

分步骤进行

1.第一步

select t.video_id,
       t.start_time,
       t.end_time,
       tvi.duration,
       case
           when (t.end_time - t.start_time) >= tvi.duration then 1
           when (t.end_time - t.start_time) < tvi.duration then 0
       end `isOver`
from tb_user_video_log as t
         join tb_video_info tvi on t.video_id = tvi.video_id) t2
where YEAR(t2.end_time)=2021

通过 where Year(date) 判断时间是否为2021 通过 case 判断出 是否 (endTime - startTime)>=duration 如果是追加一列isOver 为1 否则为 0

2.第二步

SELECT t2.video_id , round(sum(t2.isOver)/count(t2.video_id),3) `avg`
FROM () 
t2
where YEAR(t2.end_time)=2021
group by t2.video_id
order by avg desc;

通过group by 的sum() count()函数据算出最终的结果。