Hive 实操案例六：统计上传视频最多的用户 Top10 以及他们上传的视频观看次数在前 20 的视频

一、数据表结构

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

用户表 t_user

字段	注释	类型
uploader	上传者用户名	string
videos	上传视频数	int
friends	朋友数量	int

二、具体实现

-- 统计上传视频最多的用户 Top10 以及他们上传的视频观看次数在前 20 的视频
/**
  思路：
    1.处理结果集 1：在用户表中按照 uploader 分组聚合统计每个用户的总上传视频数，再使用 order by 降序和 limit 获取前 10 的用户
    2.处理结果集 2：在视频表中按照 videoid 和 uploader 分组聚合统计观看次数
    3.使用结果集 1 左连接结果集 2，关联字段为 uploader，然后使用排序窗口函数按左表的 uploader 分组，右表的观看次数降序进行组内编号
    4.使用 where 子句选取前 20 的数据
*/
SELECT 
	uploader,
	videoid,
	num,
	rn 
FROM 
(
	SELECT 
		a.uploader,
		b.videoid,
		b.num,
		DENSE_RANK() over(PARTITION by a.uploader ORDER by b.num DESC) as rn 
	FROM 
	(
		SELECT 
			uploader,
			SUM(videos) as videos  
		FROM t_user
		group by uploader 
		order by videos DESC 
		limit 10
	) a
	left join
	(
		SELECT 
			videoid,
			uploader,
			SUM(`views`) as num 
		FROM t_video
		group by videoid, uploader
	) b
	on a.uploader=b.uploader
) t
WHERE rn <= 20;