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

记录一下每题的题解和思路，本题主要需要注意需要的数据是2021年，同时count里嵌套if可以解决group by之后不能使用where的问题

SELECT t1.video_id,round(t2.finish/t1.total,3) avg 
from(SELECT video_id,COUNT(if(YEAR(end_time)=2021,video_id,null)) as total FROM tb_user_video_log JOIN tb_video_info USING(video_id)
GROUP BY video_id) t1
LEFT JOIN 
(SELECT video_id,COUNT(if (TIMESTAMPDIFF(second,start_time,end_time)>=duration and YEAR(end_time)=2021,video_id,null) )as finish 
FROM tb_user_video_log  JOIN tb_video_info  USING(video_id)
GROUP BY video_id) t2
USING(video_id)
order by avg desc