牛客网SQL156

各个视频的平均完播率_牛客题霸_牛客网

方法一

select a.video_id,format(count(b.video_id)/count(a.video_id),3) 完播率
from
(
    select uid,video_id,(end_time-start_time) 播放时长
    from tb_user_video_log
    where year(start_time)=2021 or year(end_time)=2021
) a left join tb_video_info b
on a.video_id=b.video_id and a.播放时长>=b.duration
group by a.video_id
order by 完播率 desc

方法二

select a.video_id,format(count(c.video_id)/a.播放次数,3) 完播率
from
(
    select video_id,count(*) 播放次数
    from tb_user_video_log
    where year(start_time)=2021 or year(start_time)=2021
    group by video_id
) a
join
(
    select video_id,end_time-start_time 播放时长
    from tb_user_video_log
    where year(start_time)=2021 or year(start_time)=2021
) b
on a.video_id=b.video_id
left join
(
    select video_id,duration 视频时长
    from tb_video_info
) c
on b.video_id=c.video_id and b.播放时长>=c.视频时长
group by a.video_id
order by 完播率 desc

方法二注意不能用where ，否则2002年完播率为0会直接被过滤掉，最后的结果都没有2002

要用left join，然后最后count时选择count的对象很重要，要count(c.video_id)