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

select 
c.id as  video_id,
ROUND(ifnull((end1.e/c.a),0) ,3)as avg_comp_play_rate
from (
    select
        tb_video_info.video_id as id,
        count(tb_video_info.id) as a
    from
        tb_user_video_log
        left join tb_video_info on tb_video_info.video_id = tb_user_video_log.video_id
        where   
        tb_user_video_log.start_time>= '2021-01-01'
    group by
        tb_video_info.id
) as c left join 
( select
    tb_user_video_log.video_id as id ,
        count(tb_video_info.id) as e
    from
        tb_user_video_log
        left join tb_video_info on tb_video_info.video_id = tb_user_video_log.video_id
        where    TIMESTAMPDIFF(second,tb_user_video_log.start_time,tb_user_video_log.end_time) >= duration 
        and tb_user_video_log.start_time >= "2021-01-01"
         group by
         tb_video_info.video_id
) end1  on c.id=end1.id
order by avg_comp_play_rate desc