WITH tb_caculate_cnt as ( SELECT tag, SUM(if_like) like_day_cnt, SUM(if_retweet) retweet_day_cnt, DATE(start_time) dt FROM tb_video_info video LEFT JOIN tb_user_video_log user ON video.video_id = user.video_id GROUP BY tag, dt) SELECT tag,dt,sum_like_cnt_7d,max_retweet_cnt_7d FROM( SELECT tag, dt, sum(like_day_cnt) over (PARTITION by tag ORDER BY tag,dt rows 6 preceding) as sum_like_cnt_7d, max(retweet_day_cnt) over (PARTITION by tag ORDER BY tag,dt rows 6 preceding) as max_retweet_cnt_7d FROM tb_caculate_cnt)t_1 WHERE dt >= '2021-10-01' and dt <= '2021-10-03' ORDER BY tag desc,dt;
核心通过开窗函数滑动窗口统计
两个窗口函数可以实现滑窗求和(指定rows范围)或者指定范围内数据求和(指定range范围)
sum(user_cnt) over(partition by leader_uid order by dt asc rows 6 preceding) as pre_7d_user_num,
sum(user_cnt) over(partition by leader_uid order by dt asc rows between 6 preceding and current row) pre_7d_user_num