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

#2、将关联且经过条件筛选后的数据表，按照需求挑选展示字段#
select t1.video_id,
#2.1、该字段展示的是“视频完播率”，而“视频完播率”=“完成播放次数”/“总播放次数”（①“完成播放次数”=“结束观看时间与开始播放时间的差>=视频时长时，视为完成播放” 👉 如果“结束观看时间-开始播放时间 >= 视频时长”那就算完成1次 👉 sum(case when timestampdiff(second,start_time,end_time) >= t2.duration then 1 else 0 end)）（②总播放次数=所有“播放过”的视频数 👉 只要有播放，那就算1次 👉 count(start_time)）
round(sum(case when timestampdiff(second,start_time,end_time) >= t2.duration then 1 else 0 end) / count(start_time),3) as avg_comp_play_rate

#1、确定需求所需的数据表（求结束观看时间与开始播放时间的差👉tb_user_video_log，>=视频时长时👉tb_video_info）#
from tb_user_video_log as t1
left join tb_video_info as t2
on t1.video_id = t2.video_id
#1.1、由于video的duration仅仅几秒，基本是在当天看完，因此随便拿start_time或end_time中任意一个字段，来作为“2021年里有播放记录的每个视频”中“2021年”的条件筛选字段#
where extract(year from t1.start_time) = '2021'

#3、按照video_id进行分组统计，并对“avg_comp_play_rate”字段进行降序排序#
group by 1
order by 2 desc;