# 国庆期间每类视频点赞量和转发量

### 问题分解：

• 计算每类视频每天的点赞量和转发量：
• 关联用户-视频互动记录和短视频信息表：JOIN tb_video_info USING(video_id)；
• 筛选必要时间窗的记录（因为最终只需要2021国庆头三天的近7日量，所以只需要这三天+前6天的数据即可，提前过滤提高效率）：WHERE DATE(start_time) between '2021-09-25' and '2021-10-03'
• 按视频类别和日期分组：GROUP BY tag, DATE(start_time)
• 计算点赞量和转发量：SUM(if_like) as like_cnt, SUM(if_retweet) as retweet_cnt
• 计算每类视频每天的近7日总量：
• 定义窗口（视频类别分区内按日期排序），方便复用：window wd_tag_dt as (partition by tag order by dt)
• 计算近7日点赞总量：SUM(like_cnt) over(wd_tag_dt ROWS 6 preceding)
• 计算近7日最大单天转发量：MAX(retweet_cnt) over(wd_tag_dt ROWS 6 preceding)
• 过滤出国庆三天的结果：where dt between '2021-10-01' and '2021-10-03'

### 细节问题：

• 表头重命名：as
• 按视频类别降序、日期升序排序：order by tag DESC, dt

### 完整代码：

``````select tag, dt, sum_like_cnt_7d, max_retweet_cnt_7d
from (
select tag, dt,
SUM(like_cnt) over(wd_tag_dt ROWS 6 preceding) as sum_like_cnt_7d,
MAX(retweet_cnt) over(wd_tag_dt ROWS 6 preceding) as max_retweet_cnt_7d
from (
SELECT tag, DATE(start_time) as dt, SUM(if_like) as like_cnt,
SUM(if_retweet) as retweet_cnt
FROM tb_user_video_log
JOIN tb_video_info USING(video_id)
WHERE DATE(start_time) between '2021-09-25' and '2021-10-03'
GROUP BY tag, DATE(start_time)
) as t_tag_daily_like_retweet_cnt
window wd_tag_dt as (partition by tag order by dt)
) as t_tag_dt_sum_max_cnt
where dt between '2021-10-01' and '2021-10-03'
order by tag DESC, dt;
``````