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

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;

步骤梳理：

1. 子查询 (内层)：

   • 从两个表 tb_user_video_log 和 tb_video_info 中提取数据，通过 video_id 进行连接。
   • 筛选出 start_time 日期在 ‘2021-09-25’ 到 ‘2021-10-03’ 之间的记录。
   • 对于每个 tag 和 start_time 的日期分组，计算点赞数 (like_cnt) 和转发数 (retweet_cnt)。
   • 使用 SUM 和 if 语句（或类似语句，取决于数据库）来聚合数据。

2. 窗口函数：

   • 在上述结果基础上，使用窗口函数计算每个 tag 在过去7天（包括当天）的总点赞数 (sum_like_cnt_7d) 和最大转发数 (max_retweet_cnt_7d)。
   • 窗口函数 SUM 和 MAX 分别用于对点赞数和转发数进行求和和求最大值的操作。
   • ROWS 6 preceding 表示考虑当前行和前6行，即总共7天的数据。

3. 外层查询：

   • 从上述结果中选择 tag, dt, sum_like_cnt_7d, max_retweet_cnt_7d。
   • 筛选出日期 dt 在 ‘2021-10-01’ 到 ‘2021-10-03’ 之间的记录。
   • 按照标签 (tag) 降序和日期 (dt) 进行排序。