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

select tag,dt,sum_like_cnt_7d,max_retweet_cnt_7d
from
(
-- 利用滚动求和/最大值求出点赞量和最大单天转发量
select tag,dt,
sum(like_cnt) over (partition by tag order by dt rows 6 preceding) as sum_like_cnt_7d,
max(retweet_cnt) over (partition by tag order by dt rows 6 preceding) as max_retweet_cnt_7d
from
(
-- 列出国庆前7天的所有点赞和转发量
select 
tag,
date_format(start_time,'%Y-%m-%d') as dt,
sum(if_like) as like_cnt,
sum(if_retweet) as retweet_cnt
from tb_user_video_log a
join tb_video_info b 
on a.video_id=b.video_id
where date_format(start_time,'%Y-%m-%d') between'2021-09-25'and'2021-10-03'
group by tag,dt) t)t1
-- 然后再找出国庆节期间的数
where dt>='2021-10-01'
order by tag desc,dt