题解 | #各个视频的平均完播率#
各个视频的平均完播率
https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
select re.video_id, # sum(re.com)是完播的次数，count(re.com)是播放的次数 # round实现四舍五入保留3位有效数字 round(sum(re.com) / count(re.com),3) avg_comp_play_rate from (select l.video_id video_id, # case when判定视频是否完播，返回到com列 case when l.end_time-l.start_time >= i.duration then 1 else 0 end as com from tb_user_video_log l join tb_video_info i on l.video_id = i.video_id where year(l.start_time) = 2021 ) as re group by re.video_id order by avg_comp_play_rate desc
查看2道真题和解析