题解 | #国庆期间每类视频点赞量和转发量#
国庆期间每类视频点赞量和转发量
http://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11
这题还是有点难度啊，可以用窗口函数里面的滑动窗口解决
rows between current row and 6 following 表示取当前行和下6行
select * from ( select tag,day, sum(if_like)over(partition by tag order by day desc rows between current row and 6 following) as sum_like_cnt_7d, max(if_retweet)over(partition by tag order by day desc rows between current row and 6 following) as max_retweet_cnt_7d from ( select tag,date_format(start_time,'%Y-%m-%d') as day, sum(if_like) as if_like, sum(if_retweet) as if_retweet from tb_user_video_log t1 join tb_video_info t2 on t1.video_id = t2.video_id where tag in (select tag from tb_user_video_log where date_format(start_time,'%Y-%m-%d') >= '2021-10-01' and date_format(start_time,'%Y-%m-%d') <= '2021-10-03') and date_format(start_time,'%Y-%m-%d') >= '2021-09-25' and date_format(start_time,'%Y-%m-%d') <= '2021-10-03' group by tag,date_format(start_time,'%Y-%m-%d') ) t ) t1 where day >= '2021-10-01' and day <= '2021-10-03' order by tag desc,day
