# 完播的定义:(结束时间-开始时间) > 视频时长 = 1 else 0 # 完播率: 完成播放次数 / 总次数 # 筛选条件: 2021年有播放记录,那么就是 year(start_time) = 2021 # 第一步,筛选出来了2021年所有视频的播放情况,完播率计算到新的列里面 with new_table as ( select l.video_id, i.duration, (case when (timestampdiff(second,l.start_time,l.end_time) - i.duration) >= 0 then 1 else 0 end) as wanbo from tb_user_video_log l join tb_video_info i on l.video_id = i.video_id where year(l.start_time) = 2021 ) # 第二步,完播数量之和/总播放量 select video_id, round(sum(wanbo)/count(*),3) as avg_comp_play_rate from new_table group by video_id order by avg_comp_play_rate desc