完播率计算

• 难点在于时间差的计算，个人觉得end_time - start_time这种写法存在一定的问题，最好的方式是采用timestampdiff进行计算。

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