HiveSQL大厂面试 每类视频近一个月的转发量率

题目说明

练习题目来自牛客网sq在线编程 大厂面试题每类视频近一个月的转发量/率_牛客题霸_牛客网 (nowcoder.com)

测试用例

create database nk_test;  //创建数据库
use nk_test;    //进入数据库

// 创建表
DROP TABLE IF EXISTS tb_user_video_log;
DROP TABLE IF EXISTS tb_video_info;
CREATE TABLE tb_user_video_log (
    id INT,
    uid INT,
    video_id INT,
    start_time timestamp,
    end_time timestamp,
    if_follow TINYINT,
    if_like TINYINT,
    if_retweet TINYINT,
    comment_id INT
);

CREATE TABLE tb_video_info (
    id INT,
    video_id INT,
    author INT,
    tag string,
    duration INT,
    release_time timestamp
);

// 插入数据
INSERT INTO tb_user_video_log(id, uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id) VALUES
  (1, 101, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:30', 0, 1, 1, null),
  (2, 102, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:24', 0, 0, 1, null),
  (3, 103, 2001, '2021-10-01 11:00:00', '2021-10-01 11:00:34', 0, 1, 0, 1732526),
  (4, 101, 2002, '2021-09-01 10:00:00', '2021-09-01 10:00:42', 1, 0, 1, null),
  (5, 102, 2002, '2021-10-01 11:00:00', '2021-10-01 11:00:30', 1, 0, 1, null);

INSERT INTO tb_video_info(id, video_id, author, tag, duration, release_time) VALUES
  (1, 2001, 901, '影视', 30, '2021-01-01 7:00:00'),
  (2, 2002, 901, '美食', 60, '2021-01-01 7:00:00'),
  (3, 2003, 902, '旅游', 90, '2021-01-01 7:00:00');
           

描述

用户-视频互动表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	1	1	NULL
2	102	2001	2021-10-01 10:00:00	2021-10-01 10:00:15	1	NULL
3	103	2001	2021-10-01 11:00:50	2021-10-01 11:01:15	1	1732526
4	102	2002	2021-09-10 11:00:00	2021-09-10 11:00:30	1	1	NULL
5	103	2002	2021-10-01 10:59:05	2021-10-01 11:00:05	1	1	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-发布时间）

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

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

输出示例：

示例数据的输出结果如下

tag	retweet_cut	retweet_rate
美食	2	1.000
影视	2

解释：

由表tb_user_video_log的数据可得，数据转储当天为2021年10月1日。近30天内，影视类视频2001共有3次播放记录，被转发2次，转发率为0.667；美食类视频2002共有2次播放记录，1次被转发，转发率为0.500。

解决方案

SQL写法

SELECT tag,SUM(if_retweet) AS retweet_cut, 
ROUND(SUM(if_retweet)/count(*) ,3) AS retweet_rate
FROM tb_user_video_log AS t1
LEFT JOIN tb_video_info AS t2
ON t1.video_id = t2.video_id
WHERE date(start_time) > (SELECT DATE_SUB(MAX(date(start_time)),30) FROM tb_user_video_log)
GROUP BY tag
ORDER BY retweet_rate DESC;
           

HIVE写法

-- 错误写法 不知道到什么原因就是在where中的子查询就是用不起要报错，所以我只能将30天前的日期使用变量接收
SELECT tag,SUM(if_retweet) AS retweet_cut, 
ROUND(SUM(if_retweet)/count(*) ,3) AS retweet_rate
FROM tb_user_video_log AS t1
LEFT JOIN tb_video_info AS t2
ON t1.video_id = t2.video_id
WHERE date(start_time) > (SELECT DATE_SUB(MAX(date(start_time)),30) FROM tb_user_video_log)
GROUP BY tag
ORDER BY retweet_rate DESC;


-- 正确写法
-- 查询30天前的日期
SELECT DATE_SUB(MAX(date(start_time)),30) FROM tb_user_video_log;
-- 使用变量接收这个日期，然后'${hiveconf:max_time}'使用这个变量 注意：不能给这个日期打引号否则后边不能成功运行。
set max_time = 2021-09-01;
SELECT tag,SUM(if_retweet) AS retweet_cut, 
ROUND(SUM(if_retweet)/count(*) ,3) AS retweet_rate
FROM tb_user_video_log AS t1
LEFT JOIN tb_video_info AS t2
ON t1.video_id = t2.video_id
WHERE date(start_time) > '${hiveconf:max_time}'
GROUP BY tag
ORDER BY retweet_rate DESC;


#调试代码
# 本来想用试一试直接将查询结果赋值给变量，发现不得行只能采用手动设置了
set max_time = SELECT DATE_SUB(MAX(date(start_time)),30) FROM tb_user_video_log;
set max_time = 2021-09-01;

SELECT start_time
FROM tb_user_video_log
WHERE date(start_time) > to_date('${hiveconf:max_time}');
           

问题分析

• 关联用户-视频互动记录和短视频信息表：tb_user_video_log AS t1 JOIN tb_video_info AS t2 ON t1.video_id = t2.video_id；
• 筛选最近30天的记录：
  • 找到今天的日期：MAX(DATE(start_time)
  • 往过去推移30天：DATE_SUB(MAX(DATE(start_time)), 30)
  • 将过去30天的截止日期使用变量接收 set max_time = 2021-09-01;
  • 筛选最近的：tb_user_video_log)
• 按视频类别分组：GROUP BY tag
• 计算每个类别的转发量：SUM(if_retweet)

• 计算每个类别的转发率（转发率＝转发量÷播放量）：SUM(if_retweet) / COUNT(1)

  max_time = 2021-09-01;

  • 筛选最近的：tb_user_video_log)
• 按视频类别分组：GROUP BY tag
• 计算每个类别的转发量：SUM(if_retweet)
• 计算每个类别的转发率（转发率＝转发量÷播放量）：SUM(if_retweet) / COUNT(1)
• 保留3位小数：ROUND(x, 3)