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

select
    l.video_id,
    round(avg(if(timestampdiff(second,l.start_time,l.end_time)>=i.duration,1,0)),3) as avg_comp_play_rate
    #  ROUND(AVG(IF(
    #     TIMESTAMPDIFF(SECOND, start_time, end_time)>=duration, 1, 0
    # # )), 3) as avg_comp_play_rate
from tb_user_video_log as l
inner join
tb_video_info as i on l.video_id=i.video_id
where year(l.start_time)=2021
group by l.video_id
order by
avg_comp_play_rate desc;

• 这里平均完播率的计算并没有统计 count（播完的）/总播放次数，而后计算平均数；
• 做法：而是将播放完的记为1，为放完的记为0，求其均值，这样就巧妙的计算出了平均完播率