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

##先用video_id关联得出一个按照每条播放记录整理的字段为uid，video_id,开始时间，结束时间，视频时长的子表：
select uv.uid, uv.video_id, 
uv.start_time, uv.end_time, vi.duration 
from tb_user_video_log uv
left join tb_video_info vi
on uv.video_id=vi.video_id
where year(start_time)='2021'

##再按照video_id聚合，计算总播放量和完播量相除：
round(count(case when end_time-start_time>=duration then 1 else null end)/count(uid),3) as avg_comp_play_rate

##完整代码：
select video_id,
round(count(case when end_time-start_time>=duration then 1 else null end)/count(uid),3) as avg_comp_play_rate
from 
(select uv.uid, uv.video_id, 
uv.start_time, uv.end_time, vi.duration 
from tb_user_video_log uv
left join tb_video_info vi
on uv.video_id=vi.video_id
where year(start_time)='2021'
) a
group by video_id
order by 2 desc