Hive 实操案例三：统计 Top 20 观看数的视频所属类别以及类别包含 Top20 视频的个数

一、数据表结构

视频表 t_video

字段	注释	描述
videoId	视频唯一 id（String）	11 位字符串
uploader	视频上传者（String）	上传视频的用户名 String
age	视频年龄（int）	视频在平台上的整数天
category	视频类别（Array<String>）	上传视频指定的视频分类
length	视频长度（Int）	整形数字标识的视频长度
views	观看次数（Int）	视频被浏览的次数
rate	视频评分（Double）	满分 5 分
Ratings	流量（Int）	视频的流量，整型数字
conments	评论数（Int）	一个视频的整数评论数
relatedId	相关视频 id（Array<String>）	相关视频的 id，最多 20 个

二、具体实现

-- 统计出视频观看数最高的 20 个视频的所属类别以及类别包含 Top20 视频的个数
/**
 思路：
   1.先按照 videoid 和 category 字段分组聚合统计观看数
   2.使用排序窗口函数按观看数对视频及其类别进行降序编号
   3.使用 where 子句选取 top20 的视频类别
   4.由于 category 字段为 Array 类型，所以使用 explode 函数将其扁平化，再分组聚合统计类别下的视频个数
*/
SELECT 
	cg,
	SUM(1) as num 
FROM 
(
	SELECT 
		videoid,
		category
	FROM 
	(
		SELECT 
			videoid,
			category,
			DENSE_RANK() over(ORDER by `views` desc) as rn
		FROM 
		(
			SELECT 
				videoid,
				category,
				SUM(`views`) as `views`  
			FROM t_video 
			group by videoid,category 
		) t1
	) t2
	WHERE rn <= 20
) t3 LATERAL view explode(category) adtable as cg
group by cg;