1、统计视频观看数Top10
思路：使用order by按照views字段做一个全局排序即可，同时我们设置只显示前10条。
最终代码：
hive> select
videoId,
uploader,
age,
category,
length,
views,
rate,
ratings,
comments
from
youtube_orc
order by
views
desc limit
10;
2、统计视频类别热度Top10
思路
1）即统计每个类别有多少个视频，显示出包含视频最多的前10个类别。
2）我们需要按照类别group by聚合，然后count组内的videoId个数即可。
3）因为当前表结构为：一个视频对应一个或多个类别。所以如果要group by类别，需要先将类别进行列转行(展开)，然后再进行count即可。
4）最后按照热度排序，显示前10条。
最终代码
hive> select
category_name as category,
count(t1.videoId) as hot
from (
select
videoId,
category_name
from
youtube_orc lateral view explode(category) t_catetory as category_name) t1
group by
t1.category_name
order by
hot
desc limit
10;
3、统计出视频观看数最高的20个视频，的所属类别，这些类别分别包含Top20视频的个数
思路
1）先找到观看数最高的20个视频所属条目的所有信息，降序排列
2）把这20条信息中的category分裂出来(列转行)
3）最后查询视频分类名称和该分类下有多少个Top20的视频
最终代码
hive> select
category_name as category,
count(t2.videoId) as hot_with_views
from (
select
videoId,
category_name
from (
select
*
from
youtube_orc
order by
views
desc limit
20) t1 lateral view explode(category) t_catetory as category_name) t2
group by
category_name
order by
hot_with_views
desc;
4、统计视频观看数Top50，所关联视频，的所属类别Rank
思路
1）查询出观看数最多的前50个视频的所有信息(当然包含了每个视频对应的关联视频)，记为临时表t1
t1:观看数前50的视频
hive> select
*
from
youtube_orc
order by
views
desc limit
50;
2）将找到的50条视频信息的相关视频relatedId列转行，记为临时表t2
t2:将相关视频的id进行列转行操作
hive> select
explode(relatedId) as videoId
from
t1;
3）将相关视频的id和youtube_orc表进行inner join操作
t5:得到两列数据，一列是category，一列是之前查询出来的相关视频id
(select
distinct(t2.videoId),
t3.category
from
t2
inner join
youtube_orc t3 on t2.videoId = t3.videoId) t4 lateral view explode(category) t_catetory as category_name;
4）按照视频类别进行分组，统计每组视频个数，然后排行
最终代码
hive> select
category_name as category,
count(t5.videoId) as hot
from (
select
videoId,
category_name
from (
select
distinct(t2.videoId),
t3.category
from (
select
explode(relatedId) as videoId
from (
select
*
from
youtube_orc
order by
views
desc limit
50) t1) t2
inner join
youtube_orc t3 on t2.videoId = t3.videoId) t4 lateral view explode(category) t_catetory as category_name) t5
group by
category_name
order by
hot
desc;
5、统计每个类别中的视频热度Top10，以Music为例
思路
1）要想统计Music类别中的视频热度Top10，需要先找到Music类别，那么就需要将category展开，所以可以创建一张表用于存放categoryId展开的数据。
2）向category展开的表中插入数据。
3）统计对应类别（Music）中的视频热度。
最终代码
创建表类别表
hive> create table youtube_category(
videoId string,
uploader string,
age int,
categoryId string,
length int,
views int,
rate float,
ratings int,
comments int,
relatedId array<string>)
row format delimited
fields terminated by "\t"
collection items terminated by "&"
stored as orc;
向类别表中插入数据
hive> insert into table youtube_category
select
videoId,
uploader,
age,
categoryId,
length,
views,
rate,
ratings,
comments,
relatedId
from
youtube_orc lateral view explode(category) catetory as categoryId;
统计Music类别的Top10（也可以统计其他）
hive> select
videoId,
views
from
youtube_category
where
categoryId = "Music"
order by
views
desc limit
10;
6、统计每个类别中视频流量Top10，以Music为例
思路
1）创建视频类别展开表（categoryId列转行后的表）
2）按照ratings排序即可
最终代码
hive> select
videoId,
views,
ratings
from
youtube_category
where
categoryId = "Music"
order by
ratings
desc limit
10;
7、统计上传视频最多的用户Top10以及他们上传的观看次数在前20的视频
思路
1）先找到上传视频最多的10个用户的用户信息
hive> select
*
from
youtube_user_orc
order by
videos
desc limit
10;
2）通过uploader字段与youtube_orc表进行join，得到的信息按照views观看次数进行排序即可。
最终代码
hive> select
t2.videoId,
t2.views,
t2.ratings,
t1.videos,
t1.friends
from (
select
*
from
youtube_user_orc
order by
videos desc
limit
10) t1
join
youtube_orc t2
on
t1.uploader = t2.uploader
order by
views desc
limit
20;
8、统计每个类别视频观看数Top10
思路
1）先得到categoryId展开的表数据
2）子查询按照categoryId进行分区，然后分区内排序，并生成递增数字，该递增数字这一列起名为rank列
3）通过子查询产生的临时表，查询rank值小于等于10的数据行即可。
最终代码
hive> select
t1.*
from (
select
videoId,
categoryId,
views,
row_number() over(partition by categoryId order by views desc) rank from youtube_category) t1
where
rank <= 10;
