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

select 
    a.video_id,
    round(count(case when end_time-start_time>=duration then a.video_id end)/count(a.video_id),3) as avg_comp_play_rate
from 
    tb_user_video_log a
    left join 
    tb_video_info b 
        on (a.video_id=b.video_id)
where year(start_time) = '2021'
group by a.video_id
order by avg_comp_play_rate desc

注：mysql里面的datediff()不能计算秒的差