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

select
    video_id,
    round(avg(is_finished), 3) as avg_comp_play_rate
from
    (
        select
            uid,
            u.video_id,
            u.start_time,
            u.end_time,
            case
                when timestampdiff(SECOND, u.start_time, u.end_time) >= v.duration then 1
                else 0
            end as is_finished
        from
            tb_user_video_log u
            left join tb_video_info v on u.video_id = v.video_id
        where
            year(u.start_time) = '2021'
    ) tb1
group by 
    video_id
order by 
    avg_comp_play_rate desc

case语句筛选完成播放的人

avg计算完播率