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

明确题意：

计算2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，结果按视频类别降序、日期升序排序

问题分解：

• 计算每类视频每天的点赞量和转发量：
  • 关联用户-视频互动记录和短视频信息表：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;