题解 | #平均播放进度大于60%的视频类别#
平均播放进度大于60%的视频类别
https://www.nowcoder.com/practice/c60242566ad94bc29959de0cdc6d95ef
注意题目条件：播放时长大于视频时长的一律视为100%，所以先做条件判断再求平均值即可。否则会出现部分视频的完播率>1，导致结果错误
select tag as tag, concat(round(avg(if(unix_timestamp(a.end_time)- unix_timestamp(a.start_time) < b.duration, (unix_timestamp(a.end_time)- unix_timestamp(a.start_time))/b.duration,1))*100,2),'%') as avg_play_progress from tb_user_video_log a left join tb_video_info b on a.video_id = b.video_id group by tag having avg(if(unix_timestamp(a.end_time)- unix_timestamp(a.start_time) < b.duration, (unix_timestamp(a.end_time)- unix_timestamp(a.start_time))/b.duration,1)) >0.6 order by avg_play_progress desc
#数据仓库与数据分析实习#
投递快手等公司10个岗位