题解 | #平均播放进度大于60%的视频类别#
平均播放进度大于60%的视频类别
http://www.nowcoder.com/practice/c60242566ad94bc29959de0cdc6d95ef
本题主要结合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 -- 按播放率降序排列
