题解 | #平均播放进度大于60%的视频类别#
平均播放进度大于60%的视频类别
https://www.nowcoder.com/practice/c60242566ad94bc29959de0cdc6d95ef
- 输出% 需要用concat(...,'%')和replace(...,'%','')
- 可能存在播放时长大于视频时长的情况,需要用IF(判断语句, 真则a, 假则b)
- 播放时长要以秒为单位,用timestampdiff(second, start,end)
select tag, concat( round( avg( if (timestampdiff(second, start_time, end_time)/duration > 1, 100, timestampdiff(second, start_time, end_time)/duration*100)), 2), '%') as play_prop from tb_user_video_log a left join tb_video_info b on a.video_id = b.video_id group by tag having replace(play_prop,'%','') > 60 order by play_prop desc