题解 | #各个视频的平均完播率#

select a.video_id,round(avg(case when (timestamp(end_time)-timestamp(start_time))>=b.duration then 1 else 0 end),3) as avg_comp_play_rate
from tb_user_video_log as a
left join tb_video_info as b
on a.video_id=b.video_id
where year(start_time)=2021 and year(end_time)=2021
group by a.video_id
order by avg_comp_play_rate desc

灵活用到avg，此外链接是取视频Id相同的