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

#左边是用户视频表，右边是信息表，匹配
# 计算每一个视频的播放进度 （时间差）/ duration
# 注意，播放时长大于duration时长时，要计算为100%，这里容易出错,用case when，如果小于，就是播放时长除以duration，如果大于等于，就是1

with t as (
    select l.id,
       l.video_id,
       i.tag,
       (case when timestampdiff(second,l.start_time,l.end_time) <= i.duration 
             then timestampdiff(second,l.start_time,l.end_time) / i.duration 
             else 1 end  )as progress
        from tb_user_video_log l 
        join tb_video_info i 
        on l.video_id = i.video_id
)

# 按照视频tag分类，计算每个类别视频的平均播放进度
# 只输出结果大于60%的
select tag,
       concat(round(100 * avg(progress),2),"%") as avg_play_progress
from t 
group by tag having avg(progress) > 0.6
order by avg_play_progress desc