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

1.先求完播次数

select
    tuvl.video_id,
    count(*) finish_cnt
from
    tb_user_video_log tuvl,
    tb_video_info tvi
where tuvl.video_id = tvi.video_id
and year(start_time) = 2021
and timediff(end_time, start_time) >= tvi.duration
group by tuvl.video_id

2.再求全部播放次数

select
    video_id,
    count(*) view_cnt
from
    tb_user_video_log tuvl
where year(tuvl.start_time) = 2021
group by tuvl.video_id

3. 求完播率

select
    t2.video_id,
    format(ifnull(finish_cnt, 0)/view_cnt, 3) avg_comp_play_rate
from
    (
        select
            tuvl.video_id,
            count(*) finish_cnt
        from
            tb_user_video_log tuvl,
            tb_video_info tvi
        where tuvl.video_id = tvi.video_id
        and year(start_time) = 2021
        and timediff(end_time, start_time) >= tvi.duration
        group by tuvl.video_id
    )t1 right join
    (
        select
            video_id,
            count(*) view_cnt
        from
            tb_user_video_log tuvl
        where year(tuvl.start_time) = 2021
        group by tuvl.video_id
    )t2
on t1.video_id = t2.video_id
order by avg_comp_play_rate desc

4. 由于1、2步骤中条件很相似，可以进行优化合并在一起

select
    tuvl.video_id,
    format(sum(if(timediff(end_time, start_time) >= tvi.duration, 1,0))/count(*), 3) avg_comp_play_rate
from
    tb_user_video_log tuvl,
    tb_video_info tvi
where tuvl.video_id = tvi.video_id
and year(start_time) = 2021 
group by tuvl.video_id
order by avg_comp_play_rate desc