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

# 3. 窗口函数后，如果需要设置日期condition，必须进行全选后再加上日期和排序条件，否则会对窗口函数的结果有影响。
select * from (
# 2. 计算近一周总点赞量和一周内最大单天转发量
select tag, dt, 
sum(if_like) over(partition by tag order by dt rows 6 preceding) as sum_like_cnt_7d,
max(if_retweet) over(partition by tag order by dt rows 6 preceding) as max_retweet_cnt_7d
from(
# 1. 计算每个类别每天的点赞量和转发量
select tag, dt, sum(if_like) as if_like, sum(if_retweet) as if_retweet from(
    select tb_user_video_log.video_id, date_format(start_time,"%Y-%m-%d") as dt,
    tag, if_like, if_retweet from tb_user_video_log
    left join tb_video_info
    on tb_video_info.video_id = tb_user_video_log.video_id
)t
group by tag, dt
)t1
)t2
where dt between "2021-10-01" and "2021-10-03"
order by tag desc, dt

累加连续7天的值：sum(x) over(partition by xx order by xxx rows 6 preceding)

果需要对时间和顺序设置条件，一定要放置在窗口函数后！在窗口函数后，进行全选再加上日期和排序条件。否则会对窗口函数的结果有影响。