题解 | #平均播放进度大于60%的视频类别#

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