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

select v.author,
       date_format(u.start_time,'%Y-%m') as '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(u.start_time),3)  as 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')) as fans_total

from tb_video_info as v
left join tb_user_video_log as u
on v.video_id=u.video_id and year(u.start_time)=2021
and year(u.end_time)=2021

group by v.author,month
order by v.author,fans_total

select author,month,fans_growth_rate,sum(fan) over (partition by author order by month) as total_fans from 
(select author,month,round(sum(fan)/count(1),3) as fans_growth_rate,sum(fan) as fan from 
(select video_id,date_format(start_time,"%Y-%m") as "month",
case when if_follow=1 then 1
     when if_follow=0 then 0
     when if_follow=2 then -1
end as fan 
from tb_user_video_log
WHERE year(start_time) =2021) a 
left join 
(select video_id,author from tb_video_info) b
on a.video_id=b.video_id
group by author,month) as a
order by total_fans

WITH A AS(
    SELECT
        author,
        DATE_FORMAT (start_time,'%Y-%m' ) AS 'month',	
        SUM(CASE
            WHEN if_follow=0 THEN 0
            WHEN if_follow=1 THEN 1
            ELSE -1
        END ) AS new_fans,
        COUNT(*) AS number
    FROM tb_user_video_log u
    JOIN tb_video_info v USING(video_id)
    GROUP BY author,DATE_FORMAT(start_time,'%Y-%m')
)
SELECT
    author,
    month,
    ROUND(new_fans/number,3) AS fans_growth_rate,
    SUM(new_fans) OVER (PARTITION BY author ORDER BY month) AS total_fans
FROM A
where month like '2021%'
ORDER BY author, total_fans

#2021年 每个创作者 每月  涨粉率 + 截止当月的总粉丝量
#1 涨粉 0 没关注 2 掉粉
select  author,month,	fans_growth_rate,
        sum(fans_growth) over(partition by author
                                order by month) total_fans
from (
        select
            v.author,
            date_format(u.start_time,"%Y-%m") month,
            round(sum(case when if_follow= 1 then 1
                    when if_follow=2 then -1
                    else 0 end) /count(u.uid),3) 	fans_growth_rate,
            sum(case when if_follow= 1 then 1
                    when if_follow=2 then -1
                    else 0 end) fans_growth
        from tb_video_info v
        left join tb_user_video_log u
            on v.video_id = u.video_id and year(u.start_time) = 2021
        where u.video_id is not null
        group by v.author,date_format(u.start_time,"%Y-%m")
) t
order by author,total_fans 

with cte as (
    select author, left(start_time, 7) as month, 
    sum(case when if_follow = 0 then 0
    when if_follow = 1 then 1
    when if_follow = 2 then -1
    end)as fan_growth, 
    count(if_follow) as played
    from tb_user_video_log u join tb_video_info v
    on u.video_id = v.video_id
    group by author, month
)

select author, month, round(fan_growth/played, 3) as fans_growth_rate, sum(fan_growth) over(partition by author order by month asc) as total_fans
from cte
where left(month,4) = '2021'
order by author asc, total_fans asc

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

select author, 
       month,
       round((add_fans-sub_fans)/play_count,3) as fans_growth_rate,
       sum(add_fans-sub_fans) over (partition by author order by month)total_fans 
from( 
select b.author, 
       DATE_FORMAT(a.start_time, '%Y-%m') as month,
       count(a.video_id) as play_count,
       sum(if(a.if_follow=1,1,0)) as add_fans,
       sum(if(a.if_follow=2,1,0)) as sub_fans
from tb_user_video_log a
left join tb_video_info b
using (video_id)
where year(start_time)='2021'
group by b.author, 
         DATE_FORMAT(a.start_time, '%Y-%m')
)c
order by author， total_fans

select author, month, fans_growth_rate, SUM(fans_acc) OVER (partition by author order by month) as fans_acc_cnt
from(
select author, month, round((acf + dcf) / num_play, 3) as fans_growth_rate, acf+dcf as fans_acc
from (
    select author,DATE_FORMAT(start_time, '%Y-%m') AS month, 
    sum(case when if_follow = 1 then 1 else 0 end) as acf,
    sum(case when if_follow = 2 then -1 else 0 end) as dcf,
    count(*) as num_play
    from tb_user_video_log 
    left join tb_video_info on 
    tb_user_video_log.video_id = tb_video_info.video_id
    where year(start_time) = 2021
    group by author, month
) as fans_info
) as final
order by author, fans_acc_cnt

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