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

select *
from
(select tag,dt,
sum(like_cnt)over(partition by tag order by dt rows between 6 preceding and current row) as sum_like_cnt_7d,
max(retweet_cnt)over(partition by tag order by dt rows between 6 preceding and current row) as max_retweet_cnt_7d
from
(select b.tag as tag,date_format(a.start_time,'%Y-%m-%d') as dt,
sum(a.if_like) as like_cnt,
sum(a.if_retweet) as retweet_cnt
from tb_user_video_log as a
left join tb_video_info as b
on a.video_id=b.video_id
group by b.tag,date_format(a.start_time,'%Y-%m-%d')
)k
)e
where dt between '2021-10-01'and '2021-10-03'
group by tag,dt
order by tag desc,dt

总结一下遇到的问题：1、取过去的7天，rows between 6 preceding and current row;2、先 sum再筛选国庆后3天。