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

SELECT
	M.tag,
	concat(
		cast( ( sum( realTime ) / sum( M.duration ) * 100 ) AS DECIMAL ( 10, 2 ) ),
		"%" 
	) as avg_play_progress
FROM
	(
	SELECT
		tv.tag AS tag,
		tv.video_id AS video_id,
		LEAST( TIMESTAMPDIFF( SECOND, tu.start_time, tu.end_time ), tv.duration ) AS realTime,
		tv.duration 
	FROM
		tb_user_video_log tu
		INNER JOIN tb_video_info tv ON tu.video_id = tv.video_id 
	) M 
GROUP BY
	M.tag 
HAVING
	sum( realTime ) / sum( M.duration ) > 0.6
ORDER BY avg_play_progress DESC