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

-- time国庆期间 group by tag每类视频 点赞量和转发量
select d.*
  from(select tag,dt,
  sum(日点赞量)over(partition by tag order by dt rows between 6 preceding and current row ) 前七日点赞量,
  max(日转发量)over(partition by tag order by dt rows between 6 preceding and current row ) 前七日最大转发量
  from
  (select b.tag,date_format(a.start_time,'%Y-%m-%d') dt,sum(a.if_like) 日点赞量,sum(a.if_retweet) 日转发量
  from tb_user_video_log a
  left join tb_video_info b
  on a.video_id=b.video_id
  where year(a.start_time)='2021'
  group by b.tag,date_format(a.start_time,'%Y-%m-%d'))c)d
  where d.dt >='2021-10-01' and d.dt <='2021-10-03'
  order by d.tag desc,date_format(d.dt,'%Y-%m-%d') asc

感谢题解里的各位朋友，让我知道了如何使用滑动窗口函数rows between ... and ... preceding, following