思路:构建临时表,首先明确表中可以确定的数据就是 影视分类
日期相减函数timestampdiff(),本题中使用的是秒数,所以里面写法是
timestampdiff(second,start_time,end_time)
利用if函数判断,例如 if(times(视频播放时长)<= duration(视频原时长) ,times/duration*100 ,100) 播放率
拼接函数concat (,‘%’) 将百分号拼接到里面.
with t as ( select tag,timestampdiff(second,start_time,end_time) as times,duration as d
from tb_user_video_log as t1 left join tb_video_info as t2 on t1.video_id = t2.video_id
)
select tag,concat(round(avg(if(times <= d,times/d100,100)),2),‘%’) as avg_play_progress
from t
group by tag
having avg(if(times <= d,times/d100,100)) > 60
order by avg_play_progress desc