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

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

if这里脱离出了表格，只是单纯计算完播率，如果满足结束时间-开始时间>视频时长，则为完整播放，这时有效次数+1。if也不一定一定要和sum搭配。