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

select b.tag,concat(round(avg(
    if (timestampdiff(second,a.start_time,a.end_time)>=b.duration,100,
       timestampdiff(second,a.start_time,a.end_time)/b.duration*100
    )),2),'%') as avg_play_progress
from tb_user_video_log as a
inner join tb_video_info as b
on a.video_id = b.video_id
group by b.video_id
having avg_play_progress>60
order by avg_play_progress desc

弱点记录：

加%号方法：concat（num,'%'）

时间计算秒数：timestampdiff(second,开始的，结束的)

保留小数：round（num，2） 保留2位小数

if（条件一，结果，其他）