问题切分

筛选条件：将进度大于60%的类别输出（先聚合求进度，再筛选，可以用having筛选)

要求的是：各类视频的平均播放进度

隐藏条件：结果保留两位小数、按播放进度倒序排序

重点问题

如何求各类视频的平均播放进度？

播放进度=播放时长÷视频时长*100%，当播放时长大于视频时长时，播放进度均记为100%。

播放时长

timestampdiff(second,start_time,end_time)

视频时长

duration

需要注意的是如果播放时长大于视频时长时，播放进度均记为100%

也就是if(timestampdiff(second,start_time,end_time)>=duration,1,timestampdiff(second,start_time,end_time)/duration)

最后求平均

avg(if(timestampdiff(second,start_time,end_time)>=duration,1,timestampdiff(second,start_time,end_time)/duration))

整合代码

select

    tag,

    concat(round(avg_play_progress*100,2),'%') avg_play_progress

from

    (select

        tag,

        avg(if(timestampdiff(second,start_time,end_time)>=duration,1,timestampdiff(second,start_time,end_time)/duration)) avg_play_progress

    from

        tb_user_video_log tu left join tb_video_info tv

        on tu.video_id = tv.video_id

    group by 1

    having avg_play_progress > 0.6

    order by 2 desc) a