(SQL刷题）平均播放进度大于60%的视频类别

问题

问题解析

问题分解：

• 关联用户-视频互动记录和短视频信息表：LEFT JOIN tb_video_info USING(video_id)；
• 按视频类别分组：GROUP BY tag
• 计算每个类别的平均播放进度：
  • 播放进度=播放时长÷视频时长*100%
  • 播放时长=TIMESTAMPDIFF(SECOND, start_time, end_time)；特殊情况：播放时长大于视频时长时，播放进度为100%（加个IF判断或者用case）
  • 平均进度=AVG(每个进度)
  • 结果保留2位小数：ROUND(x, 2)
  • 百分比格式化：CONCAT(x, '%')

• 播放进度计算

CONCAT(
    ROUND(
        AVG(
            case when TIMESTAMPDIFF(SECOND，start_time，end_time) > duration then 1 else TIMESTAMPDIFF(SECOND，start_time，end_time)/duration
end) *100,2),'%)

完整代码：

select t2.tag, 
    concat(
        round(
            avg(
                case when TIMESTAMPDIFF(SECOND, t1.start_time, t1.end_time)>=t2.
                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
left join tb_video_info t2 on t1.video_id=t2.video_id
group by t2.tag
having round(
            avg(
                case when TIMESTAMPDIFF(SECOND, t1.start_time, t1.end_time)>t2.
                duration then 1 else TIMESTAMPDIFF(SECOND, t1.start_time, t1.
                end_time)/t2.duration end)*100,2)>60
order by avg_play_progress desc