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

select tuvl.video_id,
round(sum(if(tuvl.end_time - tuvl.start_time >= tvi.duration,1,0)) / COUNT(start_time),3) AS avg_comp_play_rate
FROM tb_user_video_log AS tuvl
LEFT JOIN tb_video_info AS tvi on tuvl.video_id = tvi.video_id
where year(start_time) = 2021
group by video_id 
order by avg_comp_play_rate DESC 

select tuvl.video_id,
round(count(if(tuvl.end_time - tuvl.start_time >= tvi.duration,1,null)) / COUNT(start_time),3) AS avg_comp_play_rate

FROM tb_user_video_log AS tuvl
LEFT JOIN tb_video_info AS tvi on tuvl.video_id = tvi.video_id
where year(start_time) = 2021
group by video_id 
order by avg_comp_play_rate DESC 

count只有在值null时才不算上这一条记录

count(if(tuvl.end_time - tuvl.start_time >= tvi.duration,1,null))

count(if(tuvl.end_time - tuvl.start_time >= tvi.duration,1,0)) #错误的