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

自用备忘

select
    t3.video_id
    ,round(avg(flag),3) avg_comp_play_rate
from(
    select
        uid
        ,t1.video_id
        ,if(timestampdiff(second,start_time,end_time) < duration,0,1) flag
    from
        tb_user_video_log t1
    inner join tb_video_info t2
    on t1.video_id = t2.video_id
    where year(start_time) = 2021
)t3
group by t3.video_id
order by avg_comp_play_rate desc