题解 | #各个视频的平均完播率#

1.先求出2021年各个视频播放完成情况，播放完成的条件为：end_time-start_time>=duration

SELECT t.video_id,
(CASE WHEN t.end_time-t.start_time>=t1.duration THEN 1 ELSE 0 END) AS finish
FROM tb_user_video_log AS t JOIN tb_video_info AS t1
ON t.video_id=t1.video_id
WHERE YEAR(t.start_time)=2021

2.再通过avg函数求出来播放率的情况

ROUND(AVG(t2.finish),3)

3.根据题目的要求，需要按照vedio_id进行分组，同时按照avg_comp_play_rate排序，综合整理代码

SELECT
	t2.video_id,
	ROUND( AVG( t2.finish ), 3 ) AS avg_comp_play_rate 
FROM
	(
	SELECT
		t.video_id,
		( CASE WHEN t.end_time - t.start_time >= t1.duration THEN 1 ELSE 0 END ) AS finish 
	FROM
		tb_user_video_log AS t
		JOIN tb_video_info AS t1 ON t.video_id = t1.video_id 
	WHERE
		YEAR ( t.start_time )= 2021 
	) AS t2 
GROUP BY
	t2.video_id 
ORDER BY
	avg_comp_play_rate DESC