题解 | #平均播放进度大于60%的视频类别#

本题主要结合timestampdiff函数、left join连接等来计算视频播放率
select tag,  
concat(round(avg(if(TIMESTAMPDIFF(SECOND,start_time,end_time)>duration,100,TIMESTAMPDIFF(SECOND,start_time,end_time)*100/duration)),2),'%') rate   -- 使用concat添加%，也可采用round(avg(if(TIMESTAMPDIFF(SECOND,start_time,end_time)>duration,100,TIMESTAMPDIFF(SECOND,start_time,end_time)*100/duration)),2)||'%'
from tb_user_video_log a left join tb_video_info b --连接用户表和视频信息表
on a.video_id = b.video_id
group by tag
having avg(TIMESTAMPDIFF(SECOND,start_time,end_time)/duration) > 0.6  --筛选播放率超过60%的类别
order by rate desc  -- 按播放率降序排列