每个创作者每月的涨粉率及截止当前的总粉丝量

select author, month,
round(add_fans/counts,3) fans_growth_rate, 
sum(add_fans)over(partition by author order by month) total_fans
from (select author, 
      DATE_FORMAT(start_time,'%Y-%m') month, 
     sum(case when if_follow=2 then -1 else if_follow end) add_fans,
     count(*) counts
     from tb_user_video_log t1
     join tb_video_info t2 
     on t1.video_id=t2.video_id
     where year(start_time)=2021
     group by author, month) a
ORDER BY author, total_fans

【场景】：截止当前

【分类】：窗口函数、sum

分析思路

难点：

1.截止当前

（1）统计每个用户的播放量、加粉量、掉粉量

• [条件]：year(start_time) = 2021

• [使用]：group by author,month；count

（2）计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量

当月的总粉丝量 = (上个月)(加粉量 - 掉粉量) + 这个月(加粉量 - 掉粉量)

需要用到聚合窗口函数

• [使用]：sum(follow_add-follow_sub) over(partition by author order by month)

求解代码

方法一：

with 子句

with
    main as(
        #统计每个用户的播放量、加粉量、掉粉量
        select 
            author,
            mid(start_time,1,7) as month,
            count(start_time) as b,
            count(if(if_follow = 1, 1, null)) as follow_add,
            count(if(if_follow = 2, 1, null)) as follow_sub
        from tb_user_video_log a, tb_video_info b
        where a.video_id = b.video_id
        and year(start_time) = 2021
        group by author,month
    )
#计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量
select 
    author,
    month,
    round((follow_add-follow_sub)/b ,3) as fans_growth_rate,
    sum(follow_add-follow_sub) over(partition by author order by month) as total_fans
from main
order by author,total_fans

方法二：

from子查询

#计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量
#当月的总粉丝量 = (上个月)(加粉量 - 掉粉量) + 这个月(加粉量 - 掉粉量) 
select 
    author,
    month,
    round((follow_add-follow_sub)/b ,3) as fans_growth_rate,
    sum(follow_add-follow_sub) over(partition by author order by month) total_fans
from(
    #统计每个用户的播放量、加粉量、掉粉量
    select 
        author,
        mid(start_time,1,7) as month,
        count(start_time) as b,
        count(if(if_follow = 1, 1, null)) as follow_add,
        count(if(if_follow = 2, 1, null)) as follow_sub
    from tb_user_video_log a, tb_video_info b
    where a.video_id = b.video_id
    and year(start_time) = 2021
    group by author,month
) main
order by author,total_fans

select author,date_format(start_time,'%Y-%m') month,
round(sum(case when if_follow=2 then -1  #这里注意不能写成case when if_follow=2 then if_follow=-1
      else if_follow
      end)/count(start_time),3) as fans_growth_rate,
sum(          #外面这个sum是把里面的每个月涨跌数全部再加起来，得到总粉丝数。
    sum(case when if_follow = 2 then -1
        else if_follow
        end)  #里面的sum计算的是每个创作者每个月的涨跌粉丝数
    ) over (partition by author order by start_time) as fans_total
from tb_user_video_log as log
left join tb_video_info as info
on log.video_id=info.video_id
where year(start_time)=2021
and year(end_time)=2021
group by author,month
order by author,fans_total

select v.author,
date_format(u.start_time,'%Y-%m') month,
round((sum(case when u.if_follow=1 then 1 else 0 end)-sum(case when u.if_follow=2 then 1 else 0 end))/count(*),3) fans_growth_rate ,
sum(sum(case when u.if_follow = 1 then 1
         when u.if_follow = 2 then -1
         else 0 end) ) over (partition by v.author order by date_format(u.start_time,'%Y-%m')) total_fans
from tb_user_video_log u inner join tb_video_info v
on u.video_id = v.video_id
where year(u.start_time) = '2021'
group by v.author,month
order by v.author,total_fans

• 联结
• 按照作者和月进行分组：group by author,date_format(start_time,"%Y-%m")
• 涨粉率：
  • 加粉和减粉变化：sum(if(if_follow=2,-1,if_follow))
  • 播放量：count(1)
• 截止当月的总粉丝量
  • sum(粉丝变化)over(partition by author order by date_format(start_time,"%Y-%m"))
• 条件：2021年

select
    author,
    date_format(start_time,"%Y-%m") as month,
    round(sum(if(if_follow=2,-1,if_follow))/count(1),3) as fans_growth_rate,
    sum(sum(if(if_follow=2,-1,if_follow))) over (partition by author order by date_format(start_time,"%Y-%m")) as total_fans
from  tb_user_video_log
left join tb_video_info using(video_id)
where year(start_time) = 2021 
group by 1,2
order by 1,4

with t as (
select author,
    date_format(start_time,"%Y-%m") as month,
    sum(case when if_follow = 1 then 1
         when if_follow = 2 then -1
         else 0 end) add_fans,
    count(*) counts
from tb_user_video_log t1
join tb_video_info t2 
on t1.video_id=t2.video_id
where year(start_time)=2021
group by author,month
)
select author, month,
round(add_fans/counts,3) fans_growth_rate, 
sum(add_fans) over(partition by author order by month) total_fans
from t 
order by author,total_fans;

select 
author,date_format(start_time,'%Y-%m') as month,
round((sum(case when if_follow =1 then 1 else 0 end) -
 sum(case when if_follow =2 then 1 else 0 end))/count(*),3) as fans_growth_rate,
 sum((sum(case when if_follow =1 then 1 else 0 end) -
 sum(case when if_follow =2 then 1 else 0 end))) 
 over(partition by author order by date_format(start_time,'%Y-%m') ) as total_fans
from tb_video_info join tb_user_video_log using(video_id)
where year(start_time) = 2021
group by author,date_format(start_time,'%Y-%m')
order by author,total_fans

重点是日期格式的准备
另外就是大量 case when 的计算

聚合窗口函数的使用，加入orderby后具有累计计算的能力。

[[分组计算]]

with new_tab as # 第一步，对原始表数据进行拼接处理成一张新表，
# 其中利用case when 函数将关注设为1，取消关注设为-1，从而方便sum()求和
(select ut.uid,vt.author,ut.if_follow,substring(ut.start_time,1,7) as mth
,(case when ut.if_follow=1 then 1 when ut.if_follow=2 then -1 else 0 end) as new_follow
from tb_user_video_log ut left join tb_video_info vt on ut.video_id=vt.video_id
where year(ut.start_time)='2021')

select distinct author,mth
,round(sum(new_follow)over(partition by author,mth)/count(uid)over(partition by author,mth),3) as fans_growth_rate
,sum(new_follow)over(partition by author order by mth) as total_fans
# 第二步，利用窗口函数解决截止当月的累计粉丝数
from new_tab
order by author,total_fans

select tbv.author,date_format(tbu.start_time,'%Y-%m') month,
       round(sum(case when if_follow = 1 then 1
           when if_follow = 2 then -1 else 0 end)/count(1),3) fans_growth_rate,
       sum(sum(case when if_follow = 1 then 1
           when if_follow = 2 then -1 else 0 end)) over(partition by author order by date_format(tbu.start_time,'%Y-%m')) total_fans
from  tb_user_video_log tbu
join tb_video_info tbv on tbu.video_id = tbv.video_id
where year(tbu.start_time) = 2021
group by tbv.author,month
order by tbv.author,total_fans;

select
    author
    ,month
    ,fans_growth_rate
    ,sum(add_fans) over(partition by author order by month) total_fans
from(
    select
        author
        ,date_format(start_time,'%Y-%m') month
        ,round((sum(if_follow = 1) - sum(if_follow = 2)) / count(*),3) fans_growth_rate
        ,sum(if_follow = 1) - sum(if_follow = 2) add_fans

    from
        tb_user_video_log t1
    inner join tb_video_info t2
    on t1.video_id = t2.video_id
    where year(start_time) = 2021
    group by author,month
)t3
order by author,total_fans

select author,
date_format(start_time,'%Y-%m') month, 
round(avg(if(if_follow<2,if_follow,-1)),3) fans_growth_rate,
sum(sum(if(if_follow<2,if_follow,-1))) over(partition by author order by date_format(start_time,'%Y-%m')) total_fans 
from tb_user_video_log 
join tb_video_info 
using(video_id)
where year(start_time)='2021'
group by author,date_format(start_time,'%Y-%m') 
order by author,total_fans

select
author,
month,
round(add_fans/video_play,3) fans_growth_rate, 
sum(add_fans)over(partition by author order by month) total_fans
from (select author, 
      date_format(start_time,'%Y-%m') month, 
      sum(if(if_follow=2 ,-1,if_follow)) add_fans,
      count(1) video_play
      from tb_user_video_log 
      join tb_video_info 
      using(video_id)
      where year(start_time)=2021
      group by 1, 2) a
order by 1, 4

with a as (select 
           author,
           date_format(start_time,'%Y-%m') month,
           sum(if(if_follow=2,-1,if_follow)) add_fans,
           count(1) video_play
           from tb_user_video_log
           join tb_video_info 
           using(video_id)
          where year(start_time)=2021
          group by 1,2)
select 
author,
month,
round(add_fans/video_play,3) fans_growth_rate,
sum(add_fans) over(partition by author order by month) total_fans
from a
order by 1,4

select
    author,
    date_format(start_time,'%Y-%m') `month`,
    round(avg(if(if_follow = 2,-1,if_follow)),3) fans_growth_rate,
    sum(sum(if(if_follow = 2,-1,if_follow))) over (partition by author order by date_format(start_time,'%Y-%m')) total_fans
from tb_user_video_log uvl
join tb_video_info vi
on uvl.video_id = vi.video_id
where year(start_time) = 2021
group by author,`month`
order by author,total_fans;