【场景】：截止当前

【分类】：窗口函数、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