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

窗口函数+rows preceing操作

select
  M.t0,
  M.t1,
  M.t2,
  M.t3
from
  (
    select
      N.b t0,
      N.a t1,
      sum(N.c) OVER(
        PARTITION BY N.b
        order by
          N.a rows 6 preceding
      ) t2,
      max(N.d) OVER(
        PARTITION BY N.b
        order by
          N.a rows 6 preceding
      ) t3
    from
      (
        select
          date_format(t.start_time, "%Y-%m-%d") a,
          tvi.tag b,
          sum(t.if_like) c,
          sum(t.if_retweet) d
        from
          tb_user_video_log t
          inner join tb_video_info tvi on tvi.video_id = t.video_id
        group by
          tvi.tag,
          date_format(t.start_time, "%Y-%m-%d")
      ) N
  ) M
where
  M.t1 >= "2021-10-01"
  and M.t1 <= "2021-10-03"
order by
  M.t0 desc,
  M.t1