Hive 实操案例四：统计视频观看数 Top50 所关联视频的所属类别排序

一、数据表结构

视频表 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 个

二、具体实现

-- 统计视频观看数 Top50 的视频所有相关视频的所属类别排序
/**
  思路：
    1.先按照 videoid 和 relatedid 分组聚合统计观看数
    2.使用排序窗口函数按照观看数降序编号
    3.使用 where 子句获取 top50 的视频的关联视频字段 relatedid
    4.由于 relatedid 字段为 Array 类型，使用 explode 函数将字段扁平化并去重作为结果表 1
    5.再从 t_video 表中使用 explode 函数将 category 字段扁平化作为结果表 2
    6.将结果表 1 和结果表 2 按照视频 id 字段进行关联，并按视频类别分组聚合统计次数，最终按次数降序展示
*/
SELECT 
	b.cg,
	SUM(1) as num 
FROM 
(
	SELECT 
		reId
	FROM 
	(
		SELECT 
			relatedId
		FROM 
		(
			SELECT 
				videoid,
				relatedId,
				DENSE_RANK() over(ORDER by `views` desc) as rn
			FROM 
			(
				SELECT 
					videoid,
					relatedId,
					SUM(`views`) as `views`  
				FROM t_video 
				group by videoid,relatedId 
			) t1
		) t2
		WHERE rn <= 50
	) t3 LATERAL view explode(relatedId) adtable as reId
	group by reId
) a
JOIN 
(
	select
		videoid,
		cg
	FROM t_video tv 
	lateral view explode(category) adtable as cg 
	group by videoid,cg 
) b
on a.reId=b.videoid
GROUP by b.cg
order by num DESC;