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

select 
  date_format(start_time,"%Y-%m-%d") as dt,
  tag,
  sum(if_like) as like_cn,
  sum(if_retweet) as retweet_cn
from  tb_user_video_log
left join tb_video_info using(video_id)
where year(start_time) = 2021
group by 2,1

select 
  dt,
  tag,
  sum(like_cn) over(partition by tag order by dt rows 6 preceding) as like_cn_week,
  max(retweet_cn) over (partition by tag order by dt rows 6 preceding) as retweet_cn_week
from  子表
group by 2,1

select 
  tag,
  dt,
  like_cnt,
  retweet_cnt
from (
  select 
      dt,
      tag,
      sum(like_cn) over(partition by tag order by dt rows 6 preceding) as like_cnt,
      max(retweet_cn) over (partition by tag order by dt rows 6 preceding) as retweet_cnt
  from(
      select 
          date_format(start_time,"%Y-%m-%d") as dt,
          tag,
          sum(if_like) as like_cn,
          sum(if_retweet) as retweet_cn
      from  tb_user_video_log
      left join tb_video_info using(video_id)
      where year(start_time) = 2021
      group by 2,1
  ) t1 
  group by 2,1
) t2
where dt between '2021-10-01' and '2021-10-03'
order by  1 desc,2