题解|窗口函数框架规则|#国庆期间每类视频点赞量和转发量#
国庆期间每类视频点赞量和转发量
https://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11
# ### 第一步生成国庆三天及每天往前推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
