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

问题：

统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，结果按视频类别降序、日期升序排序。假设数据库中数据足够多，至少每个类别下国庆头3天及之前一周的每天都有播放记录。

考点：

1. 近一周 考点

当前行

rows between current row and current row

当前行及前几行 preceding为在……之前

rows between 1 preceding and current row

当前行及后几行 following 为在……之后

rows between current row and 1 following

本题涉及近一周

rows between 6 preceding and current row

2. 总点赞量 sum

   最大转发量 max

3. 升降序 order by desc(降序） asc（升序）

解题步骤：

1. 选出9.25-10.3日的转发和点赞

select 
*
from
(select
  tag,
  dt,
  sum(if_like_cnc) over(
    partition by tag
    order by
      dt rows between 6 preceding
      and current row
  ) sum_like_cnt_7d,
  max(if_retweet_cnc) over (
    partition by tag
    order by
      dt rows between 6 preceding
      and current row
  )
from
  (
    select
      tv.tag tag,
      date(tu.start_time) dt,
      sum(if_like) if_like_cnc,
      sum(if_retweet) if_retweet_cnc
    from
      tb_user_video_log tu
      left join tb_video_info tv on tu.video_id = tv.video_id
    where
      date(tu.start_time) between '2021-09-25' and '2021-10-23'
    group by
      tag,
      dt
  ) t
group by
  tag,
  dt
order by
  tag desc,
  dt) t2
  where dt between '2021-10-01' and '2021-10-03'