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

select 
tag
,concat(round(avg(play_progress)*100, 2), '%') as avg_play_progress
from
(
    select 
    info.tag
    ,case when timestampdiff(second, log.start_time, log.end_time) <= info.duration then timestampdiff(second, log.start_time, log.end_time)/info.duration 
     else 1
    end as play_progress
    from tb_user_video_log log
    left join tb_video_info info
    on log.video_id = info.video_id
) a
group by tag
having substring_index(avg_play_progress, '%', 1) > 60
order by avg_play_progress desc

为啥把having substring_index(avg_play_progress, '%', 1) > 60改成having avg_play_progress > 0.6就会多一个60%的结果