Hive -- Hive面试题及答案（2）

数据结构

视频表

用户表

需求描述

  统计影音视频网站的常规指标，各种TopN指标：

1. 统计视频观看数Top10
2. 统计视频类别热度Top10
3. 统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数
4. 统计视频观看数Top50所关联视频的所属类别排序
5. 统计每个类别中的视频热度，视频流量，观看数Top10
6. 统计上传视频最多的用户Top10以及他们上传的观看次数在前20的视频

解答

1. 统计视频观看数Top10

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

select video_id, views from video_orc order by views desc limit 10;

2. 统计视频类别热度Top10

思路： 以某类视频的个数作为视频类别热度。

1. 即统计每个类别有多少个视频，显示出包含视频最多的前10个类别。
2. 我们需要按照类别group by聚合，然后count组内的videoId个数即可。
3. 因为当前表结构为：一个视频对应一个或多个类别。所以如果要group by类别，需要先将类别进行列转行(展开)，然后再进行count即可。
4. 最后按照热度排序，显示前10条。
   最终代码：

select cate, count(1) cate_cnt from video_orc 
lateral view explode(category) temp as cate
group by cate order by cate_cnt desc limit 10;

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

思路：

1. 先找到观看数最高的20个视频所属条目的所有信息，降序排列
2. 把这20条信息中的category分裂出来(列转行)
3. 最后查询视频分类名称和该分类下有多少个Top20的视频
   最终代码：

select cate, count(1) from (
	select category, views from video_orc order by views desc limit 20
) t_top20 lateral view explode(category) temp as cate
group by cate

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

思路：

1. 查询出观看数最多的前50个视频的所有信息(当然包含了每个视频对应的关联视频)，记为临时表t1
2. 将找到的50条视频信息的相关视频relatedId列转行，记为临时表t2
3. 将相关视频的id和video_orc表进行inner join操作
4. 炸裂category，按照视频类别进行分组，统计每组视频个数，然后排行

select cate, count(1) cate_cnt from (
	select t_dis.rel_id, video_orc.category from (
		select rel_id from (
			select related_ids,views from video_orc order by views desc limit 50
		) t lateral view explode(related_ids) temp as rel_id group by rel_id
	) t_dis join video_orc on t_dis.rel_id = video_orc.video_id
) t_join lateral view explode(category) exp_temp as cate
group by cate order by cate_cnt desc

5. 统计每个类别中的视频热度，视频流量，观看数Top10

思路：

1. 炸裂类别一列，分组倒叙排序，取前10
   最终代码：

select * from (
	select *, rank() over(partition by cate order by comments desc) rank 
		//  rank() over(partition by cate order by ratings desc) rank
		//  rank() over(partition by cate order by views desc) rank
	from (
		select * from video_orc lateral view explode(category) temp as cate
	) t_exp 
) t_rank where rank <= 10;

6. 统计上传视频最多的用户Top10以及他们上传的观看次数在前20的视频

思路：

1. 先找到上传视频最多的10个用户的用户信息
2. 通过uploader字段与video_orc表进行join，得到的信息再与 views倒叙排前10的join即可
   最终代码：

select t_join_video.* from (
	select v.* from (
		select uploader, videos from user_orc order by videos desc limit 10
	) t join video_orc v on t.uploader = v.uploader
) t_join_video 
join (
	select * from video_orc order by views desc limit 10
) t_views20 on t_join_video.video_id = t_views20.video_id