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)