题解 | 国庆期间每类视频点赞量和转发量

# 该题的主要难点主要是通过开窗进行二次聚合，第一次进行sum，按照日期进行聚合，第二次按照tag进行聚合
#为了更清晰一点，也可以写成cte语句
# 1.按照日期进行聚合获取2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量
# 通过日期进行汇总，得到从10月1号前7天也就是9月25号之间的所有日期到10月3号每天的点赞数和转发数，
# 再通过类别进行分组，得到当前类别下每天的近7天的点赞量和转发量
# 最后通过dt进行过滤，过滤掉不在范围内的数据
with t1 as(
    SELECT  tag,DATE_FORMAT(start_time,'%Y-%m-%d') dt,
    sum(if_like) as a,
    sum(if_retweet) as b
    FROM tb_user_video_log JOIN tb_video_info USING(video_id)
    WHERE DATEDIFF('2021-10-03',DATE_FORMAT(start_time,'%Y-%m-%d'))<9
    GROUP BY  dt,tag
),
t2 as(
    select tag,dt,
        sum(a) over(partition by tag order by dt rows 6 preceding) AS sum_like_cnt_7d,
        max(b) over(partition by tag order by dt rows 6 preceding) as max_retweet_cnt_7d
    from t1
)

SELECT * FROM t2 WHERE dt BETWEEN '2021-10-01' AND '2021-10-03' ORDER BY tag DESC,dt ASC;

# 该题的主要难点主要是通过开窗进行二次聚合，第一次进行sum，按照日期进行聚合，第二次按照tag进行聚合

#为了更清晰一点，也可以写成cte语句，分步骤进行

# 1.按照日期进行聚合获取2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量

# 通过日期进行汇总，得到从10月1号前7天也就是9月25号之间的所有日期到10月3号每天的点赞数和转发数，

# 再通过类别进行分组，得到当前类别下每天的近7天的点赞量和转发量

# 最后通过dt进行过滤，过滤掉不在范围内的数据