问题切分

筛选条件：2021年、有播放记录的视频

要求的是：每个视频的完播率（按照视频分组，求各自完播率）

隐藏条件：完播率结果保留三位小数、按完播率降序排序

重点问题

如何计算视频完播率？

视频完播率是指完成播放次数占总播放次数的比例。

简单起见，结束观看时间与开始播放时间的差>=视频时长时，视为完成播放。

完成播放次数

结束观看时间与开始播放时间的差：timestampdiff(second,start_time,end_time)

判断是否timestampdiff(second,start_time,end_time)>=duration

如果是，记作完成播放，如果否，不记作完成播放

sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))

总播放次数

count(*)

整合代码

select

    tu.video_id,

    round(sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))/count(*),3) avg_comp_play_rate

from

    tb_user_video_log tu left join tb_video_info tv

    on tu.video_id = tv.video_id

where

    start_time is not null and year(start_time) = 2021

group by 1

order by 2 desc