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

with t1 as(
    select v.tag, date_format(u.start_time,'%Y-%m-%d') dt,
    sum(sum(u.if_like)) over(partition by v.tag order by date_format(u.start_time,'%Y-%m-%d') rows 6 preceding) as sum_like_cnt_7d,
    max(sum(u.if_retweet)) over(partition by v.tag order by date_format(u.start_time,'%Y-%m-%d') rows 6 preceding) max_retweet_cnt_7d
    from tb_user_video_log u
    right join
    tb_video_info v
    on u.video_id=v.video_id
    where timestampdiff(day,'2021-10-03 23:59:59',u.start_time)<9
    group by dt,v.tag)
select * from t1 where dt between '2021-10-01' AND '2021-10-03' ORDER BY tag DESC,dt ASC;

• 使用聚合函数和窗口函数得到相关的数据
• 使用 rows n preciding 限定窗口函数计算的范围
• 使用 with 表名 as () select 创建临时表然后继续操作
• 日期可以between and