题解|窗口函数框架规则|#国庆期间每类视频点赞量和转发量#

# ### 第一步生成国庆三天及每天往前推7天的日期 + 这10天里每天的like_cnt和retweet_cnt
# SELECT 
#     tag,
#     DATE_FORMAT(start_time,'%Y-%m-%d') AS dt,
#     SUM(if_like) AS like_cnt,
#     SUM(if_retweet) AS retweet_cnt
# FROM tb_user_video_log a
# JOIN tb_video_info b
# ON a.video_id = b.video_id
# WHERE DATEDIFF('2021-10-03',DATE_FORMAT(start_time,'%Y-%m-%d'))<9
# GROUP BY dt,tag


# ### 第二部是使用窗口函数的框架规则对上面生成的进行整理 计算每天往前推7天的sum,当前行+前面6行
# SUM(like_cnt) OVER(PARTITION BY tag ORDER BY dt ROW 6 PRECEDING) AS sum_like_cnt_7d
# MAX(retweet_cnt) OVER(PARTITON BY tag ORDER BY dt ROW 6 PRECEDING) AS max_retweet_cnt_7d


# ### 第三步将上面两个综合起来
# SELECT 
#     tag,
#     DATE_FORMAT(start_time,'%Y-%m-%d') AS dt,
#     SUM(SUM(if_like)) OVER(PARTITION BY tag ORDER BY DATE_FORMAT(start_time,'%Y-%m-%d') ROWS 6 PRECEDING) AS sum_like_cnt_7d,
#     MAX(SUM(if_retweet)) OVER(PARTITION BY tag ORDER BY DATE_FORMAT(start_time,'%Y-%m-%d') ROWS 6 PRECEDING) AS max_retweet_cnt_7d
# FROM tb_user_video_log a
# JOIN tb_video_info b
# ON a.video_id = b.video_id
# WHERE DATEDIFF('2021-10-03',DATE_FORMAT(start_time,'%Y-%m-%d'))<9
# GROUP BY dt,tag


### 第四步只输出10-01到10-03的
WITH t1 AS(
    SELECT 
    tag,
    DATE_FORMAT(start_time,'%Y-%m-%d') AS dt,
    SUM(SUM(if_like)) OVER(PARTITION BY tag ORDER BY DATE_FORMAT(start_time,'%Y-%m-%d') ROWS 6 PRECEDING) AS sum_like_cnt_7d,
    MAX(SUM(if_retweet)) OVER(PARTITION BY tag ORDER BY DATE_FORMAT(start_time,'%Y-%m-%d') ROWS 6 PRECEDING) AS max_retweet_cnt_7d
    FROM tb_user_video_log a
    JOIN tb_video_info b
    ON a.video_id = b.video_id
    WHERE DATEDIFF('2021-10-03',DATE_FORMAT(start_time,'%Y-%m-%d'))<9
    GROUP BY dt,tag
)
SELECT * 
FROM t1 
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY tag DESC,dt

### 窗口函数的框架规则学习：https://www.cnblogs.com/zhaoshujie/p/9594676.html