题解 | #各个视频的平均完播率#
各个视频的平均完播率
https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
##先用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
投递吉利控股等公司10个岗位