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

select t2.tag as tag,
concat(
    round(
    avg(case when 
    timestampdiff(second,t1.start_time,t1.end_time)>=duration
    then 1
    else timestampdiff(second,t1.start_time,t1.end_time)/t2.duration end)
    *100,2),'%') 
    as avg_play_progress
from tb_user_video_log t1,tb_video_info t2 
where t1.video_id=t2.video_id
group by t2.tag
having substring_index(avg_play_progress,'%',1)>60
order by avg_play_progress desc

substring_index函数：按照某个字符拆分并取前n个子串

concat函数：拼串

按tag分组，针对每个分组求出完播率，如果完播率大于1那就取1

乘以100后利用round函数取2位小数即可

having中需要截取%前面的子串，判断是否大于60