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

笔记心得：

就是最近几天求和，需要使用到开窗函数，如果涉及跨行需要开窗的row的知识点

rows n perceding：从当前行到前n行（一共n+1行）
rang/rows between 边界规则1 and 边界规则2：rang表示按照值的范围进行定义框架，rows表示按照行的范围进行定义框架
rows between 2 perceding and 2 following #当前行往前2行+当前行+当前行往后2行（一共5行）
rows between 1 following 3 following #当前行的后1——>后3（共3行）
rows between unbounded preceding and current row #从第一行到当前行

答案

select
*
from 
(select
  c.tag,
  c.dt,
  -- sum(sum(c.like_cnt))over( partition by c.tag order by c.dt desc rows 6 following) as like_cnt
  SUM(c.like_cnt) OVER( PARTITION BY c.tag ORDER BY c.dt  rows 6 preceding ),
  MAX(c.retweet_cnt) OVER( PARTITION BY c.tag ORDER BY c.dt  rows 6 preceding) -- sum(c.retweet_cnt) over( partition by c.tag order by c.dt desc rows 6 following) as retweet_cnt
from
  (
    select
      b.tag,
      date_format(a.start_time, '%Y-%m-%d') as dt,
      sum(a.if_like) like_cnt,
      sum(a.if_retweet) retweet_cnt
    from
      tb_user_video_log a
      left join tb_video_info b on a.video_id = b.video_id
    group by
      b.tag,
      date_format(a.start_time, '%Y-%m-%d')
  )c)d
  where d.dt>=date_format('2021-10-01','%Y-%m-%d') 
  and d.dt<=date_format('2021-10-03','%Y-%m-%d')
  order by d.tag desc ,d.dt