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

select a.video_id, 
round(sum(if(a.end_time-a.start_time>=b.duration,1,0))/count(a.start_time),3) as avg_comp_play_rate
from tb_user_video_log a join tb_video_info b 
on a.video_id=b.video_id
where year(a.start_time)='2021' and year(a.end_time)='2021'
group by a.video_id
order by avg_comp_play_rate desc

首先明白要计算的内容，前提条件2021年的，有播放记录的完播率，2021年可以通过where进行筛选，但是其实我有疑惑，2020年开始，2021年结束的视频是怎么算？2021年开始，2022年结束的怎么算？这里是默认开始和结束播放都是2021年的，有播放记录，其实在用户视频互动表里的都是有播放记录的，所以和短视频信息表进行连接的时候，使用left join或者join都可以，这样连接出来的不会有空值，也都会有播放记录。然后计算每个视频的完播率，首先是要对视频分组group by，然后计算这个视频总播放次数，使用count就行，count（起始时间），完成播放次数是先判断每次的播放记录是不是完成播放，是计1，不是0，使用sumn求和，再和总播放次数相除，就得出了播放率