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

step1：先处理时间

		select
		date_format(start_time,'%Y-%m-%d') dt,a.video_id,if_like,if_retweet,tag
		from 
		tb_user_video_log a join tb_video_info b on a.video_id=b.video_id

step2：再按tag和dt先汇总每个标签，每天的总量

		select
		distinct tag,dt,sum(if_like) over(partition by tag,dt) daily_like_cnt,sum(if_retweet) over(partition by tag,dt) daily_retweet_cnt
		from 
		(
		select
		date_format(start_time,'%Y-%m-%d') dt,a.video_id,if_like,if_retweet,tag
		from 
		tb_user_video_log a join tb_video_info b on a.video_id=b.video_id
		) t1

step3：再使用开窗范围的写法rows 6 preceding，表示当前行及前6天也就是前一个星期，比如10月1号统计的就是9月25到10月1号7天的总计，点赞求总数，转发求最大

with s1 as
(
	select
	tag,dt,sum(daily_like_cnt) over(partition by tag ORDER BY dt rows 6 preceding) sum_like_cnt_7d,max(daily_retweet_cnt) over(partition by tag ORDER BY dt rows 6 preceding) max_retweet_cnt_7d
	from
	(
		select
		distinct tag,dt,sum(if_like) over(partition by tag,dt) daily_like_cnt,sum(if_retweet) over(partition by tag,dt) daily_retweet_cnt
		from 
		(
		select
		date_format(start_time,'%Y-%m-%d') dt,a.video_id,if_like,if_retweet,tag
		from 
		tb_user_video_log a join tb_video_info b on a.video_id=b.video_id
		) t1
	) t2
)

step4：然后取相应时间的

SELECT * FROM s1 WHERE dt BETWEEN '2021-10-01' AND '2021-10-03' ORDER BY tag DESC,dt ASC;

总的sql：

with s1 as
(
	select
	tag,dt,sum(daily_like_cnt) over(partition by tag ORDER BY dt rows 6 preceding) sum_like_cnt_7d,max(daily_retweet_cnt) over(partition by tag ORDER BY dt rows 6 preceding) max_retweet_cnt_7d
	from
	(
		select
		distinct tag,dt,sum(if_like) over(partition by tag,dt) daily_like_cnt,sum(if_retweet) over(partition by tag,dt) daily_retweet_cnt
		from 
		(
		select
		date_format(start_time,'%Y-%m-%d') dt,a.video_id,if_like,if_retweet,tag
		from 
		tb_user_video_log a join tb_video_info b on a.video_id=b.video_id
		) t1
	) t2
)
SELECT * FROM s1 WHERE dt BETWEEN '2021-10-01' AND '2021-10-03' ORDER BY tag DESC,dt ASC;