SQL练习六—每月涨粉率和总粉丝量

1.先把想要的信息提取出来，作者 月份 粉丝增长情况

把2-掉粉 变成-1 便于计算

select author,date_format(start_time,'%y-%m') month,if(if_follow=2,-1,if_follow) if_follow from tb_user_video_log u
join tb_video_info v on u.video_id=v.video_id
where year(start_time)=2021

2.如何表示 涨粉率=(加粉量 - 掉粉量) / 播放量    就是把sum(if_follow)/count(if_follow)    

sum是加粉掉粉情况，count是一共多少播放

with t1 as(
select author,
date_format(start_time,'%Y-%m') month,
if(if_follow=2,-1,if_follow) if_follow from tb_user_video_log u
join tb_video_info v on u.video_id=v.video_id
where year(start_time)=2021
)
select author,month,sum(if_follow)/count(if_follow) as fans_growth_rate 
from t1 
group by author,month

3.总粉丝量计算  sum(if_follow)表示的是粉丝增减情况

总粉丝数=现有粉丝+粉丝增减情况

sum(sum(if_follow))over (partition by author order by month)

with t1 as(
select author,date_format(start_time,'%Y-%m') month,if(if_follow=2,-1,if_follow) if_follow from tb_user_video_log u
join tb_video_info v on u.video_id=v.video_id
where year(start_time)=2021
)
select author,month,sum(if_follow)/count(if_follow) as fans_growth_rate,
       sum(sum(if_follow))over(partition by author order by month) as total_fans
from t1 group by author,month

4.条件排序 代码整理

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