【SQL真题】SQL2：平均播放进度大于60%的视频类别

题目：https://www.nowcoder.com/practice/c60242566ad94bc29959de0cdc6d95ef?tpId=268&tqId=2285039&ru=/exam/oj&qru=/ta/sql-factory-interview/question-ranking&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3DSQL%25E7%25AF%2587%26topicId%3D268

 

 方法1：

select
    a.tag,
    avg_play_progress
from  (
    select 
        tag,
        concat(round(avg(if(timestampdiff(second,start_time,end_time)>=duration,
                      1,
                      timestampdiff(second,start_time,end_time)/duration)*100),2),
               "%") as avg_play_progress
    from tb_user_video_log log
    join tb_video_info info
    on log.video_id=info.video_id
    group by tag
) a
where replace(avg_play_progress,"%","")>60
order by avg_play_progress desc

方法2：

#第一步 取出需要的数据，主要是取出每条视频的播放时间
with t as (select tag,TIMESTAMPDIFF(second,start_time,end_time) times,duration d
from tb_user_video_log tl left join tb_video_info ti on tl.video_id = ti.video_id)
#第二步，利用if函数计算每条视频的播放率，在利用avg()函数计算各类视频的平均完播率
select tag, concat(round(avg(if(times <= d,times/d*100,100)),2),'%') avg_play_progress
from t
group by tag
having avg(if(times <= d,times/d*100,100)) > 60
order by avg_play_progress desc