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

# 先获取每类视频每天的点赞量和转发量
with a as (
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 
inner join tb_video_info b 
on a.video_id = b.video_id 
group by tag,dt ) 
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 a 
order by tag desc ,dt ) b 
where dt between "2021-10-01" and "2021-10-03"