MySQL 国庆期间每类视频点赞量和转发量

用户-视频互动表tb_user_video_log

id	uid	video_id	start_time	end_time	if_follow	if_like	if_retweet	comment_id
1	101	2001	2021-09-24 10:00:00	2021-09-24 10:00:20	1	1	0	NULL
2	105	2002	2021-09-25 11:00:00	2021-09-25 11:00:30	0	0	1	NULL
3	102	2002	2021-09-25 11:00:00	2021-09-25 11:00:30	1	1	1	NULL
4	101	2002	2021-09-26 11:00:00	2021-09-26 11:00:30	1	0	1	NULL
5	101	2002	2021-09-27 11:00:00	2021-09-27 11:00:30	1	1	0	NULL
6	102	2002	2021-09-28 11:00:00	2021-09-28 11:00:30	1	0	1	NULL
7	103	2002	2021-09-29 11:00:00	2021-10-02 11:00:30	1	0	1	NULL
8	102	2002	2021-09-30 11:00:00	2021-09-30 11:00:30	1	1	1	NULL
9	101	2001	2021-10-01 10:00:00	2021-10-01 10:00:20	1	1	0	NULL
10	102	2001	2021-10-01 10:00:00	2021-10-01 10:00:15	0	0	1	NULL
11	103	2001	2021-10-01 11:00:50	2021-10-01 11:01:15	1	1	0	1732526
12	106	2002	2021-10-02 10:59:05	2021-10-02 11:00:05	2	0	1	NULL
13	107	2002	2021-10-02 10:59:05	2021-10-02 11:00:05	1	0	1	NULL
14	108	2002	2021-10-02 10:59:05	2021-10-02 11:00:05	1	1	1	NULL
15	109	2002	2021-10-03 10:59:05	2021-10-03 11:00:05	0	1	0	NULL

（uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注, if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID）

短视频信息表tb_video_info

id	video_id	author	tag	duration	release_time
1	2001	901	旅游	30	2020-01-01 07:00:00
2	2002	901	旅游	60	2021-01-01 07:00:00
3	2003	902	影视	90	2020-01-01 07:00:00
4	2004	902	美女	90	2020-01-01 08:00:00

（ideo_id-视频ID, author-创作者ID, tag-类别标签, duration-视频时长, release_time-发布时间）

问题：统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，结果按视频类别降序、日期升序排序。假设数据库中数据足够多，至少每个类别下国庆头3天及之前一周的每天都有播放记录。

重点：

1. 2021年国庆头三天
2. 每类视频每天的近一周总点赞量、一周内最大单天转发量
3. 排序

思路：

如何求每类视频每天的近一周总点赞量和一周内最大单天转发量？

先求出每类视频每天的点赞总量和转发量，再求一周的。

--每类视频每天的总点赞量和总转发量
select tag,date_format(start_time,'%Y-%m-%d') dt,
sum(if_like) sum_like,sum(if_retweet) sum_retweet
from
tb_user_video_log u left join
tb_video_info v on u.video_id = v.video_id
group by tag,dt

输出结果如下：

 

 

一周的数据该如何求？

可以借助窗口函数开窗求和，那一周如何表示？

bingo！滑动窗口求解。

sum(sum_like) over(partition by tag order by dt rows 6 preceding) 
sum_like_cnt_7d,
max(sum_retweet) over(partition by tag order by dt rows 6 preceding) 
max_retweet_cnt_7d

整理到一起：

-- 每天的近一周总点赞量、一周内最大单天转发量   滑动窗口函数
select tag,dt,
sum(sum_like) over(partition by tag order by dt rows 6 preceding) 
sum_like_cnt_7d,
max(sum_retweet) over(partition by tag order by dt rows 6 preceding) 
max_retweet_cnt_7d
from
-- 计算每类视频每天的总点赞量、总关注量  
(select tag,date_format(start_time,'%Y-%m-%d') dt,
sum(if_like) sum_like,sum(if_retweet) sum_retweet
from
tb_user_video_log u left join
tb_video_info v on u.video_id = v.video_id
group by tag,dt) t1

此时输出结果如下

 

下面是时间限制，国庆头三天，上面我们已经将日期表示改写，可直接输入，但不能直接在上述函数后添加，why？

窗口函数是在where子句后执行，若直接写where子句则窗口函数执行的时间范围不再是一周，所以可以再嵌套一个查询。

-- 每天的近一周总点赞量、一周内最大单天转发量   滑动窗口函数
select * from
(select tag,dt,
sum(sum_like) over(partition by tag order by dt rows 6 preceding) 
sum_like_cnt_7d,
max(sum_retweet) over(partition by tag order by dt rows 6 preceding) 
max_retweet_cnt_7d
from
-- 计算每类视频每天的总点赞量、总关注量  
(select tag,date_format(start_time,'%Y-%m-%d') dt,
sum(if_like) sum_like,sum(if_retweet) sum_retweet
from
tb_user_video_log u left join
tb_video_info v on u.video_id = v.video_id
group by tag,dt) t1)t2
where dt between '2021-10-01' and '2021-10-03'
order by tag desc,dt 

最终结果：

 

关于窗口函数：

<窗口函数> over (partition by <字段名> order by <字段名> [滑动窗口] )

滑动窗口部分可以是：

rows:选择数据前后几行

range:选择数据范围

rows n preceding  --表示前n行到当前行，共n+1行
rows between current row and n following  -- 表示当前行到往后n行
rows between m preceding and n following  -- 表示往前m到往后n行，共m+n+1行

range between m preceding and n following --表示所有值在[c−m,c+n]范围内的行，c为当前行的值
-- 表示日期范围时，必须使用Interval
range interval 7 day