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

SELECT
DISTINCT 
video_id,
ROUND(finished_num/total_num, 3) AS avg_comp_play_rate

FROM(
	SELECT
	video_id,
	IF((TIME_TO_SEC(end_time) - TIME_TO_SEC(start_time)) - duration >= '0', 1 , 0) AS mark,
	SUM(IF((TIME_TO_SEC(end_time) - TIME_TO_SEC(start_time)) - duration >= '0', 1 , 0)) OVER(PARTITION BY video_id) AS finished_num,
	COUNT(*) OVER(PARTITION BY video_id) AS total_num
    
	FROM
	tb_user_video_log AS uv  

	LEFT JOIN
	tb_video_info AS v
	USING (video_id) 

	WHERE YEAR(end_time) = '2021') AS a

    ORDER BY avg_comp_play_rate DESC