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

# 思路：连接两表；先计算各播放记录的播放进度；然后按tag分组计算平均播放进度，同时使用having将平均进度大于60%的类别输出
select tag, 
concat(round(avg(if(timestampdiff(second,start_time,end_time)>duration, 1, timestampdiff(second,start_time,end_time)/duration))*100,2),'%') avg_play_progress
from tb_user_video_log tl
join tb_video_info ti
on tl.video_id=ti.video_id
group by tag
having round(avg(if(timestampdiff(second,start_time,end_time)>duration, 1, timestampdiff(second,start_time,end_time)/duration))*100,2)>60
order by avg_play_progress desc;