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

select author, date_format(t.start_time, '%Y-%m') month, round( sum(if(t.if_follow = 2, -1, t.if_follow)) / count(t.video_id), 3 ) fans_growth_rate, sum(sum(if(t.if_follow = 2, -1, t.if_follow))) over( partition by tt.author order by date_format(t.start_time, '%Y-%m') ) as total_fans from tb_user_video_log t, tb_video_info tt where t.video_id = tt.video_id and year(start_time)=2021 group by author, month order by author, total_fans