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

#滑动窗口函数：window w（简称） as（partition by 1 order by 2 rows between a and b）可以完成对a到b范围行数的的内容开窗，在本题中可以获取近七天的数据内容

1. select*from
2. (select
3. tag,
4. dt,
5. sum(like_cnt) over w sum_like_snt_7d, # over w（窗口函数的简称）即可使用窗口函数
6. max(retweet_cnt) over w sum_retweet_7d
7. from
8. (select
9. tag,
10. date(start_time) dt,
11. sum(if_like) like_cnt,
12. sum(if_retweet) retweet_cnt
13. from tb_video_info
14. left join tb_user_video_log using (video_id)
15. where date(start_time) between '2021-09-25' and '2021-10-03'
16. group by tag, dt) t1
17. window w as(partition by tag order by dt desc rows between current row and 6 following) #滑动窗口函数放在表格t的后面
18. )t2
19. group by tag, dt
20. having dt between '2021-10-01' and '2021-10-03'
21. order by tag desc, dt