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

#需求：统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量
#输出：tag、日期、近一周点赞量、一周内最大单天转发量
#要求：按tag desc,dt asc输出；时间范围为2021-09-25到2021-10-03；
#拆分问题：dt：date_format(end_time,'%Y-%m-%d') dt+where dt between '2021-09-25' and '2021-10-03';输出时where dt>='2021-10-01'
#近一周点赞量:sum(sum(if_like))over(partition by tag order by dt rows 6 preceding)
#一周内最大单天转发:max(sum(if_retweet))over(partition by tag order by dt rows 6 preceding)
#需求要的是dt值一周内的量，用rows限制取值范围，rows 6 preceding表示包含当前行，再向前取值6行
select*
from(
  select tag,date_format(end_time,'%Y-%m-%d') dt,
  sum(sum(if_like))over(partition by tag order by date_format(end_time,'%Y-%m-%d') rows 6 preceding) sum_like_cnt_7d,
  max(sum(if_retweet))over(partition by tag order by date_format(end_time,'%Y-%m-%d') rows 6 preceding) max_retweet_cnt_7d
  from tb_user_video_log join tb_video_info using(video_id)
  where date_format(end_time,'%Y-%m-%d') between '2021-09-25' and '2021-10-03'
  group by 1,2
) t1
where dt>='2021-10-01'
order by tag desc,dt