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

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