Hive -- Hive面试题及答案sql语句 ---阿善有时间看
向导
数据结构
视频表
用户表
需求描述
统计影音视频网站的常规指标,各种TopN指标:
- 统计视频观看数Top10
- 统计视频类别热度Top10
- 统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数
- 统计视频观看数Top50所关联视频的所属类别排序
- 统计每个类别中的视频热度,视频流量,观看数Top10
- 统计上传视频最多的用户Top10以及他们上传的观看次数在前20的视频
解答
1. 统计视频观看数Top10
思路:使用order by按照views字段做一个全局排序即可,同时我们设置只显示前10条。
最终代码:
select video_id, views from video_orc order by views desc limit 10;
- 1
2. 统计视频类别热度Top10
思路: 以某类视频的个数作为视频类别热度。
- 即统计每个类别有多少个视频,显示出包含视频最多的前10个类别。
- 我们需要按照类别group by聚合,然后count组内的videoId个数即可。
- 因为当前表结构为:一个视频对应一个或多个类别。所以如果要group by类别,需要先将类别进行列转行(展开),然后再进行count即可。
- 最后按照热度排序,显示前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;
- 1
- 2
- 3
3. 统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数
思路:
- 先找到观看数最高的20个视频所属条目的所有信息,降序排列
- 把这20条信息中的category分裂出来(列转行)
- 最后查询视频分类名称和该分类下有多少个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
- 1
- 2
- 3
- 4
4. 统计视频观看数Top50所关联视频的所属类别排序
思路:
- 查询出观看数最多的前50个视频的所有信息(当然包含了每个视频对应的关联视频),记为临时表t1
- 将找到的50条视频信息的相关视频relatedId列转行,记为临时表t2
- 将相关视频的id和video_orc表进行inner join操作
- 炸裂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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
5. 统计每个类别中的视频热度,视频流量,观看数Top10
思路:
- 炸裂类别一列,分组倒叙排序,取前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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
6. 统计上传视频最多的用户Top10以及他们上传的观看次数在前20的视频
思路:
- 先找到上传视频最多的10个用户的用户信息
- 通过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