题目描述
问题:统计在有用户互动的最近一个月(按包含当天在内的近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 |