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

# t1先得出10月1日往前推7天到10月3日这段期间，每类视频每天的播放量和转发量
with t1 as(
select 
    tag,
    date(start_time) as dt,
    sum(if_like) as like_cnt,
    sum(if_retweet) as retweet_cnt
from tb_user_video_log u 
    join tb_video_info i using(video_id)
where date(start_time) >= date_sub('2021-10-01',interval 6 day)
group by tag,date(start_time)
),
# t2对得到的数据进行累加和取最大值。
# rows 6 preceding意思为从当前行到前6行，比如当前行为7时，为1-7行的值相加或取最大值。总行数低于7的，当前行的前6行记为0，比如当前行为1时，其前6行都没有值，所以累加或最大值的结果为第一行的值本身。
t2 as(
select 
    tag,
    dt,
    sum(like_cnt) over(partition by tag order by dt rows 6 preceding) as sum_like_cnt_7d,
    max(retweet_cnt) over(partition by tag order by dt rows 6 preceding) as max_retweet_cnt_7d
from t1)
# 最后一步，对得到的结果进行筛选，只要10月1，10月2,10月3的前一周累计结果，注意where并不能直接在t2的时候就用，否则聚合函数会受到where影响，导致不能进行正确的累加。
select 
    tag,
    dt,
    sum_like_cnt_7d,
    max_retweet_cnt_7d
from t2
where dt between '2021-10-01' and '2021-10-03'
order by tag desc,dt asc