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

# 要求：统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量
# 近一周“总点赞量”
# 一周内“最大单天转发量”
# 条件1 2021年；条件2 国庆头3天近一周；条件3 视频类别降序、日期升序
SELECT
    tag,
    dt,
    sum_like_cnt_7d,
    max_retweet_cnt_7d
FROM( # 2.使用窗口函数计算每天近一周的总点赞量SUM() OVER();和最大转发量 MAX() OVER()。
    SELECT
        tag,
        dt,
        SUM(like_cnt_1d) OVER(ORDER BY tag DESC, dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS sum_like_cnt_7d, # SUM窗口函数，current row加前6行，包含条件3
        MAX(retweet_cnt_1d) OVER(ORDER BY tag DESC, dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS max_retweet_cnt_7d # MAX窗口函数，current row加前6行，包含条件3
    FROM(
        SELECT # 1.计算出单日点赞量和转发量
            tag,
            DATE(start_time) AS dt,
            SUM(if_like) AS like_cnt_1d,
            SUM(if_retweet) AS retweet_cnt_1d
        FROM tb_user_video_log tu 
        JOIN tb_video_info tv
        ON tu.video_id = tv.video_id
        GROUP BY tag, dt
    ) AS tb1
) AS tb2
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03' # 条件2021国庆头三天