Hive实战(6)：完整案例（二）业务分析

1 统计视频观看数 Top10

思路：使用 order by 按照 views 字段做一个全局排序即可，同时我们设置只显示前 10

条。

最终代码：

SELECT 
 videoId,
 views
FROM 
 gulivideo_orc
ORDER BY 
 views DESC
LIMIT 10;

2 统计视频类别热度 Top10

思路：

（1）即统计每个类别有多少个视频，显示出包含视频最多的前 10 个类别。

（2）我们需要按照类别 group by 聚合，然后 count 组内的 videoId 个数即可。

（3）因为当前表结构为：一个视频对应一个或多个类别。所以如果要 group by 类别，

需要先将类别进行列转行(展开)，然后再进行 count 即可。

（4）最后按照热度排序，显示前 10 条。

最终代码：

SELECT 
 t1.category_name , 
 COUNT(t1.videoId) hot
FROM 
(
SELECT 
 videoId, 
 category_name 
FROM 
 gulivideo_orc 
lateral VIEW explode(category) gulivideo_orc_tmp AS category_name
) t1
GROUP BY 
 t1.category_name 
ORDER BY
 hot 
DESC 
LIMIT 10

3 统计出视频观看数最高的 20 个视频的所属类别以及类别包含

Top20 视频的个数

思路：

（1）先找到观看数最高的 20 个视频所属条目的所有信息，降序排列

（2）把这 20 条信息中的 category 分裂出来(列转行)

（3）最后查询视频分类名称和该分类下有多少个 Top20 的视频

最终代码：

SELECT
 t2.category_name,
 COUNT(t2.videoId) video_sum
FROM 
(
SELECT
 t1.videoId,
 category_name
FROM 
(
SELECT 
 videoId, 
 views ,
 category 
FROM 
 gulivideo_orc
ORDER BY 
 views 
DESC 
LIMIT 20 
) t1
lateral VIEW explode(t1.category) t1_tmp AS category_name
) t2
GROUP BY t2.category_name

4 统计视频观看数 Top50 所关联视频的所属类别排序

代码：

SELECT
 t6.category_name,
 t6.video_sum,
 rank() over(ORDER BY t6.video_sum DESC ) rk
FROM
(
SELECT
 t5.category_name,
 COUNT(t5.relatedid_id) video_sum
FROM
(
SELECT
 t4.relatedid_id,
 category_name
FROM
(
SELECT 
 t2.relatedid_id ,
 t3.category 
FROM 
(
SELECT 
 relatedid_id
FROM 
(
SELECT 
 videoId, 
 views,
 relatedid 
FROM 
 gulivideo_orc
ORDER BY
 views 
DESC 
LIMIT 50
)t1
lateral VIEW explode(t1.relatedid) t1_tmp AS relatedid_id
)t2 
JOIN 
 gulivideo_orc t3 
ON 
t2.relatedid_id = t3.videoId 
) t4 
lateral VIEW explode(t4.category) t4_tmp AS category_name
) t5
GROUP BY
 t5.category_name
ORDER BY 
 video_sum
DESC 
) t6

5 统计每个类别中的视频热度 Top10，以 Music 为例

思路：

（1）要想统计 Music 类别中的视频热度 Top10，需要先找到 Music 类别，那么就需要将

category 展开，所以可以创建一张表用于存放 categoryId 展开的数据。

（2）向 category 展开的表中插入数据。

（3）统计对应类别（Music）中的视频热度。

统计 Music 类别的 Top10（也可以统计其他）

SELECT 
 t1.videoId, 
 t1.views,
 t1.category_name
FROM 
(
SELECT
 videoId,
 views,
 category_name
FROM gulivideo_orc
lateral VIEW explode(category) gulivideo_orc_tmp AS category_name
)t1 
WHERE 
 t1.category_name = "Music" 
ORDER BY 
 t1.views 
DESC 
LIMIT 10

6 统计每个类别视频观看数 Top10

最终代码：

SELECT 
 t2.videoId,
 t2.views,
 t2.category_name,
 t2.rk
FROM 
(
SELECT 
 t1.videoId,
 t1.views,
 t1.category_name,
 rank() over(PARTITION BY t1.category_name ORDER BY t1.views DESC ) rk
FROM 
(
SELECT
 videoId,
 views,
 category_name
FROM gulivideo_orc
lateral VIEW explode(category) gulivideo_orc_tmp AS category_name
)t1
)t2
WHERE t2.rk <=10

7 统计上传视频最多的用户 Top10以及他们上传的视频

观看次数在前 20 的视频

思路：

（1）求出上传视频最多的 10 个用户

（2）关联 gulivideo_orc 表，求出这 10 个用户上传的所有的视频，按照观看数取前 20

最终代码:

SELECT 
 t2.videoId,
 t2.views,
 t2.uploader
FROM
(
SELECT 
 uploader,
 videos
FROM gulivideo_user_orc 
ORDER BY 
 videos
DESC
LIMIT 10 
) t1
JOIN gulivideo_orc t2 
ON t1.uploader = t2.uploader
ORDER BY 
 t2.views 
DESC
LIMIT 20