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

select tag,dt,num1 as sum_like_cnt_7d, num2 as max_retweet_cnt_7d from(
select tag , date(start_time) dt,
sum(if_like),sum(if_retweet),
sum(sum(if_like)) over(partition by tag order by date(start_time)
                       rows 6 preceding) as num1,
max(sum(if_retweet)) over(partition by tag order by date(start_time)
                       rows 6 preceding) as num2
from tb_user_video_log join tb_video_info using(video_id)
group by tag , date(start_time))t 
where dt between '2021-10-01' and '2021-10-03'
order by tag desc,dt

主要是窗口函数的计算，并加入了框架概念。