# 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'