[开发工具]国庆期间每类视频点赞量和转发量

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

题目链接

描述

用户-视频互动表tb_user_video_log

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

短视频信息表tb_video_info

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

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

输出示例：

示例数据的输出结果如下

解释：

由表tb_user_video_log里的数据可得只有旅游类视频的播放，2021年9月25到10月3日每天的点赞量和转发量如下：

因此国庆头3天（10.01_{10.03）里10.01的近7天（9.25}10.01）总点赞量为5次，单天最大转发量为2次（9月25那天最大）；同理可得10.02和10.03的两个指标。

1. 数据准备

DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_user_video_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    video_id INT NOT NULL COMMENT '视频ID',
    start_time datetime COMMENT '开始观看时间',
    end_time datetime COMMENT '结束观看时间',
    if_follow TINYINT COMMENT '是否关注',
    if_like TINYINT COMMENT '是否点赞',
    if_retweet TINYINT COMMENT '是否转发',
    comment_id INT COMMENT '评论ID'
) CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE tb_video_info (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    video_id INT UNIQUE NOT NULL COMMENT '视频ID',
    author INT NOT NULL COMMENT '创作者ID',
    tag VARCHAR(16) NOT NULL COMMENT '类别标签',
    duration INT NOT NULL COMMENT '视频时长(秒数)',
    release_time datetime NOT NULL COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_video_log(uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id) VALUES
   (101, 2001, '2021-09-24 10:00:00', '2021-09-24 10:00:20', 1, 1, 0, null)
  ,(105, 2002, '2021-09-25 11:00:00', '2021-09-25 11:00:30', 0, 0, 1, null)
  ,(102, 2002, '2021-09-25 11:00:00', '2021-09-25 11:00:30', 1, 1, 1, null)
  ,(101, 2002, '2021-09-26 11:00:00', '2021-09-26 11:00:30', 1, 0, 1, null)
  ,(101, 2002, '2021-09-27 11:00:00', '2021-09-27 11:00:30', 1, 1, 0, null)
  ,(102, 2002, '2021-09-28 11:00:00', '2021-09-28 11:00:30', 1, 0, 1, null)
  ,(103, 2002, '2021-09-29 11:00:00', '2021-09-29 11:00:30', 1, 0, 1, null)
  ,(102, 2002, '2021-09-30 11:00:00', '2021-09-30 11:00:30', 1, 1, 1, null)
  ,(101, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:20', 1, 1, 0, null)
  ,(102, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:15', 0, 0, 1, null)
  ,(103, 2001, '2021-10-01 11:00:50', '2021-10-01 11:01:15', 1, 1, 0, 1732526)
  ,(106, 2002, '2021-10-02 10:59:05', '2021-10-02 11:00:05', 2, 0, 1, null)
  ,(107, 2002, '2021-10-02 10:59:05', '2021-10-02 11:00:05', 1, 0, 1, null)
  ,(108, 2002, '2021-10-02 10:59:05', '2021-10-02 11:00:05', 1, 1, 1, null)
  ,(109, 2002, '2021-10-03 10:59:05', '2021-10-03 11:00:05', 0, 1, 0, null);

INSERT INTO tb_video_info(video_id, author, tag, duration, release_time) VALUES
   (2001, 901, '旅游', 30, '2020-01-01 7:00:00')
  ,(2002, 901, '旅游', 60, '2021-01-01 7:00:00')
  ,(2003, 902, '影视', 90, '2020-01-01 7:00:00')
  ,(2004, 902, '美女', 90, '2020-01-01 8:00:00');

2.查询

SELECT * FROM tb_user_video_log; 
SELECT * FROM tb_video_info;

3.问题

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

难点：

• 近一周SQL怎么实现？
• 最大单天转发量怎么求？if_retweet = 1的多条记录求和

解析：

1. 求每类视频每天的点赞量和每天的转发量，时间是2021-9-25 到 2021-10-3
2. 使用窗口函数求 每个dt日期之前6天(题目中所要求的一周内)的 日点赞量 的和
3. 以及单天转发量的最大值

4. 求解

1. 先求解求每类视频每天的点赞量和每天的转发量，时间是2021-9-25 到 2021-10-3：

	SELECT
			y.tag,
			DATE(x.start_time) 	  dt,
			SUM(x.if_like) 	  AS  daily_like_cnt,
			SUM(x.if_retweet) AS  daily_retweet_cnt
		FROM
			tb_user_video_log x,
			tb_video_info y
		WHERE
			x.video_id = y.video_id
		AND DATE(start_time) BETWEEN "2021-9-25" AND "2021-10-3"
		GROUP BY tag, dt
		ORDER BY tag, dt

2. 求每个日期每个日期近一周的点赞量和单天转发量。

WITH t AS (
			SELECT
				y.tag,
				DATE(x.start_time) 	  dt,
				SUM(x.if_like) 	  AS  daily_like_cnt,
				SUM(x.if_retweet) AS  daily_retweet_cnt
			FROM
				tb_user_video_log x,
				tb_video_info y
			WHERE
				x.video_id = y.video_id
			AND DATE(start_time) BETWEEN "2021-9-25" AND "2021-10-3"
			GROUP BY tag, dt
			ORDER BY tag, dt
		) SELECT
			tag,
			dt,
			daily_like_cnt,
			daily_retweet_cnt,
			SUM(daily_like_cnt) over(partition by tag ORDER BY dt rows between 6 preceding AND current row) AS sum_like_cnt_7d,
			max(daily_retweet_cnt) over(partition by tag order by dt rows between 6 preceding and current row) AS max_retweet_cnt_7d
		FROM
			t

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tg9Lbpiq-1647783437315)(https://s2.loli.net/2022/03/20/FZRzngeIpyYkOhQ.png)]

注意：

SUM(daily_like_cnt) over(partition by tag ORDER BY dt rows between 6 preceding AND current row)
-- 根据tag分组，dt升序排列，按照行求出当前日期对应的daily_like_cnt之前6行的值到当前行的值的和，结果刚好是一周内的总点赞量。

注：窗口函数的使用

1. 方法一

聚集函数/非聚集函数 OVER window_name
WINDOW window_name AS (window_spec)
	

按照第一种方法上面的代码为：

WITH t AS (
				SELECT
					y.tag,
					DATE(x.start_time) 	  dt,
					SUM(x.if_like) 	  AS  daily_like_cnt,
					SUM(x.if_retweet) AS  daily_retweet_cnt
				FROM
					tb_user_video_log x,
					tb_video_info y
				WHERE
					x.video_id = y.video_id
				AND DATE(start_time) BETWEEN "2021-9-25" AND "2021-10-3"
				GROUP BY tag, dt
				ORDER BY tag, dt
			) SELECT
				tag,
				dt,
				daily_like_cnt,
				daily_retweet_cnt,
				SUM(daily_like_cnt) over(partition by tag ORDER BY dt rows between 6 preceding AND current row) AS sum_like_cnt_7d,
				max(daily_retweet_cnt) over(partition by tag order by dt rows between 6 preceding and current row) AS max_retweet_cnt_7d
			FROM
				t

2. 方法二

聚集函数/非聚集函数 OVER(window_spec)
window_spec : [window_name] [partition_clause] [order_clause] [frame_clause]

按照第二种方法上面的代码改写为：

WITH t AS (
				SELECT
					y.tag,
					DATE(x.start_time) 	  dt,
					SUM(x.if_like) 	  AS  daily_like_cnt,
					SUM(x.if_retweet) AS  daily_retweet_cnt
				FROM
					tb_user_video_log x,
					tb_video_info y
				WHERE
					x.video_id = y.video_id
				AND DATE(start_time) BETWEEN "2021-9-25" AND "2021-10-3"
				GROUP BY tag, dt
				ORDER BY tag, dt
			) SELECT
				tag,
				dt,
				daily_like_cnt,
				daily_retweet_cnt,
				SUM(daily_like_cnt) over w AS sum_like_cnt_7d,
				max(daily_retweet_cnt) over w AS max_retweet_cnt_7d
			FROM t
			WINDOW w  AS (partition by tag ORDER BY dt rows between 6 preceding AND current row);

我们对着两种方式进行对比发现：

over(windos_spec), 在 select 后使用多个窗口函数时， windos_spec 过多，我们使用第二种方法相当于把windos_spec重复的代码只写了 一次，进而减少重复。

3. 以上我们已经得到了所有日期的近一周的点赞量和转发量，下面只需要 多一条where语句求出指定日期的即可：

SELECT tag, dt, sum_like_cnt_7d, max_retweet_cnt_7d 
FROM (
		上面的with代码
	 ) tt
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
order by tag desc, dt asc

全部代码如下：

SELECT tag, dt, sum_like_cnt_7d, max_retweet_cnt_7d 
FROM (
			WITH t AS (
				SELECT
					y.tag,
					DATE(x.start_time) 	  dt,
					SUM(x.if_like) 	  AS  daily_like_cnt,
					SUM(x.if_retweet) AS  daily_retweet_cnt
				FROM
					tb_user_video_log x,
					tb_video_info y
				WHERE
					x.video_id = y.video_id
				AND DATE(start_time) BETWEEN "2021-9-25" AND "2021-10-3"
				GROUP BY tag, dt
				ORDER BY tag, dt
			) SELECT
				tag,
				dt,
				daily_like_cnt,
				daily_retweet_cnt,
				SUM(daily_like_cnt) over(partition by tag ORDER BY dt rows between 6 preceding AND current row) AS sum_like_cnt_7d,
				max(daily_retweet_cnt) over(partition by tag order by dt rows between 6 preceding and current row) AS max_retweet_cnt_7d
			FROM
				t) tt
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
order by tag desc, dt asc

结果图：