题解 | #平均播放进度大于60%的视频类别#
平均播放进度大于60%的视频类别
https://www.nowcoder.com/practice/c60242566ad94bc29959de0cdc6d95ef
SELECT tp.tag,CONCAT(avg_play_progress, "%") avg_play_progress FROM (SELECT tag, ROUND( AVG(IF( TIMESTAMPDIFF(SECOND, start_time, end_time)>tvi.duration, 1, TIMESTAMPDIFF(SECOND, start_time, end_time)/tvi.duration ) ) *100, 2 ) avg_play_progress FROM tb_video_info tvi JOIN tb_user_video_log tuv USING(video_id) GROUP BY tag HAVING avg_play_progress > 60 ORDER BY avg_play_progress DESC) tp;
1.当播放时长大于视频时长时,播放进度均记为100%
2.只输出大于60%
3.添加“%”
4.给每个衍生表(派生表)提供一个别名
5.时间戳计算https://blog.csdn.net/qq_41688840/article/details/123450457