牛客SQL专项练习|SQL157平均播放进度大于60%的视频类别

SQL157平均播放进度大于60%的视频类别

指标：平均播放进度=总播放进度/播放次数
播放进度=（播放时长/视频时长）*100%
播放时长>=视频时长 播放进度=100%
“如果播放时长大于视频时长按照100%算，其余按照计算的数值算”。可以使用条件判断语句if或者case.

if(播放时长>视频时长,1,播放时长/视频时长)
case when 播放时长>视频时长 then 1 else 播放时长/视频时长 end

一下代码使用if()函数进行条件判断。

select tag,CONCAT(avg_play_progress,"%") as avg_play_progress from 
(
    select tag,
    round(sum(if(TIMESTAMPDIFF(second,start_time,end_time)>=duration,1,TIMESTAMPDIFF(second,start_time,end_time)/duration))/count(*)*100,2)  as avg_play_progress
    from tb_user_video_log as a inner join tb_video_info as b on a.video_id=b.video_id
    group by tag 
    having avg_play_progress >60
    order by avg_play_progress desc
) as t;

该代码是把tag字段和avg_play_progress字段合成一个新表t，并从这个t中筛选出tag，与avg_play_progress(与%合并之后的字段)
需要注意的地方是： 时间类型的时间差不能直接相减，要用TIMESTAMPDIFF函数计算

# TIMESTAMPDIFF函数介绍
# TIMESTAMPDIFF函数用于计算两个日期或者时间之间的差异，返回值是以指定单位表示的差异值。
TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)
#unit 是要计算的时间单位，可以是以下值之一：SECOND、MINUTE、#HOUR、DAY、WEEK、MONTH、QUARTER、YEAR。
#datetime_expr1 和 datetime_expr2 是要进行比较的日期或时间表达式。