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

-- 左连接两个表
 
-- 把二表的时间附上来
select 
p2.video_id,
-- 这一步是关键 计算各个视频的完播率
round(sum(if((p2.watch_time-p2.duration) >=0,1,0))/count(p2.video_id),3) avg_comp_play_rate
-- count(p2.watch_time>=p2.duration)	avg_comp_play_rate
from
(SELECT
	uid,
	video_id,
	end_time-start_time watch_time,
	duration
	-- 表1把两个表左连接起来了
FROM
	( SELECT a.*, b.duration FROM tb_user_video_log a LEFT JOIN tb_video_info b ON a.video_id = b.video_id ) p1
	-- p2 挑选出来了2021年的视频
WHERE substr(start_time,1,4)='2021')p2
-- 按视频编号分组
GROUP BY p2.video_id
-- 按完播率降序排列
ORDER BY avg_comp_play_rate DESC;