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

1. 先算出每个用户的观看时间

2. 判断用户观看时间超过视频时长的统计个数

3. 统计出每个视频的总观看数

4. 根据2，3求出完播率

select 
    tvi.video_id as video_id,
    round(sum(if(duration<=tdiff,1,0))/count(*),3) as avg_comp_play_rate
from tb_video_info tvi join (
    select 
        video_id,
        timediff(end_time,start_time) as tdiff
    from tb_user_video_log
    where year(start_time)='2021'
)t on tvi.video_id=t.video_id 
 group by video_id
 order by avg_comp_play_rate desc