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

with newList as (select
    tag,
    date_format(start_time,'%Y-%m-%d') dt,
    sum(if_like) as like_cnt,
    sum(if_retweet) as retweet_cnt
    # sum(if_like_sum)over(partition by t1.tag order by t1.d rows 6 preceding)
from
    tb_user_video_log
    join tb_video_info using (video_id)
group by
    tag,
    dt
)
select * from (
select tag,dt,sum(like_cnt)over(partition by tag order by dt rows 6 preceding) as like_cnt ,max(retweet_cnt)over(partition by tag order by dt rows 6 preceding) as retweet_cnt from newList 
) t1
where dt between '2021-10-01' AND '2021-10-03'
order by tag desc

本题的知识点是之前的窗口函数的补充知识点，需要在over()函数内新增一个rows 6 preceding，来表明一周内的集合结果，代码如上所示。