题解 | #平均播放进度大于60%的视频类别#
平均播放进度大于60%的视频类别
https://www.nowcoder.com/practice/c60242566ad94bc29959de0cdc6d95ef
# 先计算出每条记录的播放时长(秒) # 根据播放时长和对应的视频时长,计算百分比(大于的是100%) # 分类别平均百分比 SELECT tag, CONCAT(ROUND(AVG(cost_percent) * 100,2), '%') avg_play_progress FROM( SELECT a.uid, a.video_id, b.tag, b.duration, IF(TIMESTAMPDIFF(SECOND, a.start_time, a.end_time)>=b.duration, 1, TIMESTAMPDIFF(SECOND, a.start_time, a.end_time) / b.duration) cost_percent FROM tb_user_video_log a JOIN tb_video_info b ON a.video_id = b.video_id ) a GROUP BY tag HAVING LEFT(avg_play_progress, 5) > 60.00 ORDER BY avg_play_progress DESC
重点在于怎么控制平均进度大于60%的记录
我这里直接使用平均后的,截断方式
但是也可以
HAVING AVG(cost_percent) > 0.6 ORDER BY AVG(cost_percent) DESC;