【SQL 04 统计每类视频近一个月的转发量/率】

题目描述

问题：统计在有用户互动的最近一个月（按包含当天在内的近30天算，比如10月31日的近30天为10.2~10.31之间的数据）中，每类视频的转发量和转发率（保留3位小数）。

用户-视频互动表tb_user_video_log:

id	uid	video_id	start_time	end_time	if_follow	if_like	if_retweet	comment_id
1	101	2001	2021-10-01 10:00:00	2021-10-01 10:00:20	0	1	1	NULL
2	102	2001	2021-10-01 10:00:00	2021-10-01 10:00:15	0	0	1	NULL
3	103	2001	2021-10-01 11:00:50	2021-10-01 11:01:15	0	1	0	1732526
4	102	2002	2021-09-10 11:00:00	2021-09-10 11:00:30	1	0	1	NULL
5	103	2002	2021-10-01 10:59:05	2021-10-01 11:00:05	1	0	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	2021-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

• video_id：视频ID
• author：创作者ID
• tag：类别标签
• duration：视频时长
• release_time：发布时间

注：转发率＝转发量÷播放量。结果按转发率降序排序。

最终输出样式：

tag	retweet_cut	retweet_rate
影视	2	0.667
美食	1	0.500

题目分析

题目理解：

• 计算范围：
  - 计算日期：包含当天在内的30天之内；比如今天是10月31日，那么计算范围是10月2日至10月31日；
  - 计算动作：有用户参与互动（互动包括所有动作，既在tb_buser_bideo_log里有记录）
• 计算形式：
  - 按视频类别分组
  - 计算每个类型的转发量
  - 计算每个类别的转发率

分解解决方案：

• 关联用户-视频互动记录和短视频信息表：JOIN tb_video_info USING(video_id)；
• 筛选最近30天的记录：
  • 找到今天的日期：MAX(DATE(start_time)
  • 往过去推移30天：DATE_SUB(MAX(DATE(start_time)), INTERVAL 30 DAY)
  • 筛选最近的：WHERE DATE(start_time) > (SELECT DATE_SUB(MAX(DATE(start_time)), INTERVAL 30 DAY) FROM tb_user_video_log)
• 按视频类别分组：GROUP BY tag
• 计算每个类别的转发量：SUM(if_retweet)
• 计算每个类别的转发率（转发率＝转发量÷播放量）：SUM(if_retweet) / COUNT(1)
• 保留3位小数：ROUND(x, 3)

SQL实现

SELECT tag, 
	   SUM(if_retweet) as retweet_cnt,
       ROUND(SUM(if_retweet) / COUNT(1), 3) as retweet_rate
FROM tb_user_video_log
JOIN tb_video_info USING(video_id)
WHERE DATE(start_time) > 
    (
    SELECT DATE_SUB(MAX(DATE(start_time)), INTERVAL 30 DAY)
    FROM tb_user_video_log
    )
GROUP BY tag
ORDER BY retweet_rate DESC;

Knowledge Point 知识点

1.DATE_SUB() 函数

定义：DATE_SUB() 函数从日期减去指定的时间间隔。
语法：DATE_SUB(date,INTERVAL expr type)

date 参数是合法的日期表达式。
expr 参数是您希望添加的时间间隔。
type 参数是时间单位。

type 参数可以是以下值：

Type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

Reference

牛客网练习
SQL在线速查手册