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

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

注意点

1. concat的用法，注意60%不是数值 无法在having子句中直接对比大小，所以需要创建一个子表用于存储具体数值，然后再使用concat合并出具体百分数
2. 时间相减一定要使用时间戳格式 timestampdiff(second, start_time, end_time)
3. 注意视频播放时间可能会长于视频长度，所以需要条件语句进行区分：

case when timestampdifff()>duration

then 1 else timestampdiff()/duration

或者 if(timestampdifff()>duration,1,timestampdiff()/duration)