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

select u.video_id,
    round(
        avg(
            case when timestampdiff(second, start_time,end_time) >= duration 
            then 1 else 0
            end)
    ,3) as avg_comp_play_rate
from tb_user_video_log u
left join tb_video_info v
on u.video_id = v.video_id
where date_format(start_time, '%Y') = 2021
and date_format(end_time, '%Y') = 2021
group by u.video_id
order by avg_comp_play_rate desc

1. timestampdiff(时间单位,开始时间,结束时间) 刚开始开始和结束写反了不知道为什么错
2. date_format(时间,'%Y-%m-%d') 单引号里的格式可选
3. 求...率的时候考虑avg()+case when