题解 | #各个视频的平均完播率#思路清晰！

指标：完播率=完成播放的次数/总播放次数

接下来进行指标拆解：

# 1.每个视频总播放次数
# SELECT video_id,count(*) cnt
# from tb_user_video_log
# group by video_id

# 2.每个视频完成播放的次数
# 2.1 连接两个表，播放时长>=视频时长的记录记为1，否则为0
# select tvl.video_id
# ,case when end_time-start_time>=duration then 1 else 0 end finished
# from tb_user_video_log tvl
# inner join tb_video_info tvi
# on tvl.video_id=tvi.video_id
# 2.2 按照视频id进行分组求和
# select tvl.video_id
# ,sum(case when end_time-start_time>=duration then 1 else 0 end) cnt
# from tb_user_video_log tvl
# inner join tb_video_info tvi
# on tvl.video_id=tvi.video_id
# group by t1.video_id

# 3.代码整合：
# 因为1的总播放次数指标可以通过2中tb_user_video_log求得，所以可以直接将两个指标求解过程合并
select tvl.video_id
,round(sum(case when end_time-start_time>=duration then 1 else 0 end)/count(*),3) rate
from tb_user_video_log tvl
inner join tb_video_info tvi
on tvl.video_id=tvi.video_id
WHERE year(start_time) = 2021
group by tvl.video_id
order by rate desc