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

# 10.01 guo qin tou san tian
#by tag by day sum(like) from previous 7 days max(retweet) from previous 7 days
# tag desc 
# day asc

#t1 统计每天的每个视频的点赞和转发总量（到天）
with t1 as (
    select  tv.tag as tag,
            date(t.start_time) as dt,
            sum(t.if_like) as likeperday,
            sum(t.if_retweet) as retweetperday
    from tb_user_video_log t 
    left join tb_video_info tv on t.video_id=tv.video_id
    group by tv.tag,date(t.start_time)
    order by dt 
),

#t2 统计时间窗口，每天按tag往前看7天，此时不能where因为where会提前运算摧毁表格

t2 as (select t1.tag as tag,t1.dt as dt,sum(t1.likeperday) over (partition by t1.tag order by t1.dt rows between 6 preceding and current row) as sum_like_cnt_7d, max(t1.retweetperday) over (partition by t1.tag order by t1.dt rows between 6 preceding and current row) as max_retweet_cnt_7d
from t1
order by t1.tag desc,t1.dt asc)

#此时再where
select t2.*
from t2 
where t2.dt>='2021-10-01' and t2.dt<='2021-10-03'