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

先通过tb_user_video_log，tb_video_info计算出每个viedo_id的播放情况

select 
    tu.video_id
    ,tv.tag
    ,if((TIMESTAMPDIFF(second,tu.start_time,tu.end_time) / tv.duration) > 1,1,TIMESTAMPDIFF(second,tu.start_time,tu.end_time) / tv.duration) Isno
from tb_user_video_log tu
left join tb_video_info tv
on tu.video_id = tv.video_id

再通过tag分组，使用AVG()计算出每个tag对应的平均完播率 筛选出avg_play_progress大于0.6的分组

select 
    t.tag
    ,concat(ROUND(avg(Isno) * 100, 2), '%') avg_play_progress
from 
(select 
    tu.video_id
    ,tv.tag
    ,if((TIMESTAMPDIFF(second,tu.start_time,tu.end_time) / tv.duration) > 1,1,TIMESTAMPDIFF(second,tu.start_time,tu.end_time) / tv.duration) Isno
from tb_user_video_log tu
left join tb_video_info tv
on tu.video_id = tv.video_id) t
group by t.tag
having avg(Isno) > 0.6
order by avg_play_progress desc