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

select * from(
select tag, dt,sum(if_like) over(partition by tag order by dt asc rows 6 preceding) sum_like_cnt_7d,
max(if_retweet) over(partition by tag order by dt asc rows 6 preceding) max_retweet_cnt_7d
from(
select tag,date_format(start_time,"%Y-%m-%d") dt,sum(if_like) if_like,sum(if_retweet) if_retweet
from tb_user_video_log t1 
left join tb_video_info t2
on t1.video_id = t2.video_id
WHERE timestampdiff(day,'2021-10-03',DATE_FORMAT(start_time,'%Y-%m-%d'))<9
group by tag,dt
) t3) t4
where dt BETWEEN '2021-10-01' AND '2021-10-03' 
ORDER BY tag DESC,dt ASC;

sum(if_like) over(partition by tag order by dt asc rows 6 preceding) 可以计算出最近七天的累加额