Hive實戰企業級專案之谷粒影音統計各種TopN問題

1）需要準備的表

create table gulivideo_ori(
 videoId string, 
 uploader string, 
 age int, 
 category array<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 textfile;

create table gulivideo_user_ori(
 uploader string,
 videos int,
 friends int)
row format delimited 
fields terminated by "\t" 
stored as textfile;

load data inpath '/guliout/part-r-00000' into table gulivideo_ori;
select * from gulivideo_ori limit 2;
load data inpath '/user/root/guliVideo/user/user.txt' into table gulivideo_user_ori;
select * from gulivideo_user_ori limit 2;

create table gulivideo_orc(
 videoId string, 
 uploader string, 
 age int, 
 category array<string>, 
 length int, 
 views int, 
 rate float, 
 ratings int, 
 comments int,
 relatedId array<string>)
stored as orc
tblproperties("orc.compress"="SNAPPY");

create table gulivideo_user_orc(
 uploader string,
 videos int,
 friends int)
row format delimited 
fields terminated by "\t" 
stored as orc
tblproperties("orc.compress"="SNAPPY");

select 
videoId,category_name 
from 
gulivideo_orc
lateral view explode(category) tmp_category as category_name;    --t1

結果：

dMH0bHeiRNg 42513417
0XxI-hvPRRA 20282464
1dmVU08zVpA 16087899
RB-wUgnyGv0 15712924
QjA5faZF1A8 15256922
-_CSo1gOd48 13199833
49IDp76kjPw 11970018
tYnn51C3X_w 11823701
pv5zWaTEVkI 11672017
D2kJZOfq7zk 11184051

select category_name,count(*) category_count 
from t1
group by category_name
order by category_count 
desc limit 10;

3.最終SQL

select category_name,count(*) category_count 
from (select videoId,category_name from gulivideo_orc
lateral view explode(category) tmp_category as category_name) t1
group by category_name
order by category_count 
desc limit 10;

4.結果：

3 統計出視頻觀看數最高的 20 個視頻的所屬類別以及類別包含

Top20 視頻的個數

select 
videoId,views,category
from 
gulivideo_orc 
order by 
views desc 
limit 20; --t1

結果：

dMH0bHeiRNg 42513417 ["Comedy"]
0XxI-hvPRRA 20282464 ["Comedy"]
1dmVU08zVpA 16087899 ["Entertainment"]
RB-wUgnyGv0 15712924 ["Entertainment"]
QjA5faZF1A8 15256922 ["Music"]
-_CSo1gOd48 13199833 ["People","Blogs"]
49IDp76kjPw 11970018 ["Comedy"]
tYnn51C3X_w 11823701 ["Music"]
pv5zWaTEVkI 11672017 ["Music"]
D2kJZOfq7zk 11184051 ["People","Blogs"]
vr3x_RRJdd4 10786529 ["Entertainment"]
lsO6D1rwrKc 10334975 ["Entertainment"]
5P6UU6m3cqk 10107491 ["Comedy"]
8bbTtPL1jRs 9579911 ["Music"]
_BuRwH59oAo 9566609 ["Comedy"]
aRNzWyD7C9o 8825788 ["UNA"]
UMf40daefsI 7533070 ["Music"]
ixsZy2425eY 7456875 ["Entertainment"]
MNxwAU_xAMk 7066676 ["Comedy"]
RUCZJVJ_M8o 6952767 ["Entertainment"]

select 
	videoId,
	category_name
from 
	t1
lateral view explode(category) tmp_category as category_name;--t2

執行t2，結果：

dMH0bHeiRNg Comedy
0XxI-hvPRRA Comedy
1dmVU08zVpA Entertainment
RB-wUgnyGv0 Entertainment
QjA5faZF1A8 Music
-_CSo1gOd48 People
-_CSo1gOd48 Blogs
49IDp76kjPw Comedy
tYnn51C3X_w Music
pv5zWaTEVkI Music
D2kJZOfq7zk People
D2kJZOfq7zk Blogs
vr3x_RRJdd4 Entertainment
lsO6D1rwrKc Entertainment
5P6UU6m3cqk Comedy
8bbTtPL1jRs Music
_BuRwH59oAo Comedy
aRNzWyD7C9o UNA
UMf40daefsI Music
ixsZy2425eY Entertainment
MNxwAU_xAMk Comedy
RUCZJVJ_M8o Entertainment

select category_name,count(*) category_count 
from t2
group by category_name
order by category_count desc;

4.最終SQL

select category_name,count(*) category_count 
from 
(
select 
videoId,
category_name
from 
(
select 
videoId,views,category
from 
gulivideo_orc 
order by 
views desc 
limit 20
)t1
lateral view explode(category) tmp_category as category_name)t2
group by category_name
order by category_count desc;

結果：

4 統計視頻觀看數 Top50 所關聯視頻的所屬類別排序

select 
relatedId,views
from 
gulivideo_orc 
order by 
views desc 
limit 50;--t1

2.對t1表中的relatedId進行炸裂（列轉行）并去重

select related_id
from t1
lateral view explode(relatedId) tmp_related as related_id
group by related_id; --t2 

3. 取出觀看數前50視頻關聯ID視頻的類別

select category
from t2
join gulivideo_orc orc
on t2.related_id=orc.videoId; --t3

4.對t3表中的category進行炸裂

select explode(category) category_name
from t3; --t4

5.分組（類別）求和（總數）

select category_name,count(*) category_count
from t4
group by category_name
order by category_count desc;

6.最終SQL：

select category_name,count(*) category_count
from (select explode(category) category_name
from (select category
from (select related_id
from (select 
relatedId,views
from 
gulivideo_orc 
order by 
views desc 
limit 50)t1
lateral view explode(relatedId) tmp_related as related_id
group by related_id)t2
join gulivideo_orc orc
on t2.related_id=orc.videoId)t3)t4
group by category_name
order by category_count desc; 

方法二：使用rank()函式：

SELECT
 t6.category_name,
 t6.video_sum,
 rank() over(ORDER BY t6.video_sum DESC ) rk
FROM
(
SELECT
 t5.category_name,
 COUNT(t5.relatedid_id) video_sum
FROM
(
SELECT
 t4.relatedid_id,
 category_name
FROM
(
SELECT 
 t2.relatedid_id ,
 t3.category 
FROM 
(
SELECT 
 relatedid_id
FROM 
(
SELECT 
 videoId, 
 views,
 relatedid 
FROM 
 gulivideo_orc
ORDER BY
 views 
DESC 
LIMIT 50
)t1
lateral VIEW explode(t1.relatedid) t1_tmp AS relatedid_id
)t2 
JOIN 
 gulivideo_orc t3 
ON 
t2.relatedid_id = t3.videoId 
) t4 
lateral VIEW explode(t4.category) t4_tmp AS category_name
) t5
GROUP BY
 t5.category_name
ORDER BY 
 video_sum
DESC 
) t6;

結果：

Comedy 232
Entertainment 216
Music 195
Blogs 51
People 51
Film 47
Animation 47
News 22
Politics 22
Games 20
Gadgets 20
Sports 19
Howto 14
DIY 14
UNA 13
Places 12
Travel 12
Animals 11
Pets 11
Autos 4
Vehicles 4

SELECT 
 t1.videoId, 
 t1.views,
 t1.category_name
FROM 
(
SELECT
 videoId,
 views,
 category_name
FROM gulivideo_orc
lateral VIEW explode(category) gulivideo_orc_tmp AS category_name
)t1 
WHERE 
 t1.category_name = "Music" 
ORDER BY 
 t1.views 
DESC 
LIMIT 10;

上面的方法雖然也可以解決問題，但是不建議使用，可以使用如下方法：
思路：
1)要想統計music類別中的視頻熱度Top10，需要先找到Music類別，那么就需要將category展開，
所以可以創建一張表用于存放categoryId展開的資料，
2)向category展開的表中插入資料
3)統計對應類別（Music）中的視頻熱度，
1.創建類別表：

create table guliVideo_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;

2.向類別表中插入資料：

insert into table guliVideo_category
select 
videoId,
uploader,
age,
categoryId,
length,
views,
rate,
ratings,
comments,
relatedId
from gulivideo_orc
lateral view explode(category) tmp_category as categoryId;

3.統計Music類別的Top10(也可以統計其他)

select videoId,views 
from guliVideo_category 
where categoryId = "Music" 
order by views desc 
limit 10;

結果：

QjA5faZF1A8 15256922
tYnn51C3X_w 11823701
pv5zWaTEVkI 11672017
8bbTtPL1jRs 9579911
UMf40daefsI 7533070
-xEzGIuY7kw 6946033
d6C0bNDqf3Y 6935578
HSoVKUVOnfQ 6193057
3URfWTEPmtE 5581171
thtmaZnxk_0 5142238

select
categoryId,
videoId,
views,
rank() over(partition by categoryId order by views desc) rk
from guliVideo_category; --t1

2.過濾前10

select categoryId,videoId,views
from t1
where rk <=10;

3.最終SQL:

select categoryId,videoId,views
from (select
categoryId,
videoId,
views,
rank() over(partition by categoryId order by views desc) rk
from guliVideo_category)t1
where rk <=10;

7 統計上傳視頻最多的用戶 Top10 以及他們上傳的視頻觀看次數在前 20 的視頻

select uploader,videos
from gulivideo_user_orc
order by videos desc 
limit 10; --t1

2.關聯 gulivideo_orc 表，求出這 10 個用戶上傳的所有的視頻，按照觀看數取前 20
最終SQL:

SELECT 
 t2.videoId,
 t2.views
FROM
(
SELECT 
 uploader,
 videos
FROM gulivideo_user_orc 
ORDER BY 
 videos
DESC
LIMIT 10 
) t1
JOIN gulivideo_orc t2 
ON t1.uploader = t2.uploader
ORDER BY 
t2.views DESC
LIMIT 20;

3.結果：

-IxHBW0YpZw 39059
BU-fT5XI_8I 29975
ADOcaBYbMl0 26270
yAqsULIDJFE 25511
vcm-t0TJXNg 25366
0KYGFawp14c 24659
j4DpuPvMLF4 22593
Msu4lZb2oeQ 18822
ZHZVj44rpjE 16304
foATQY3wovI 13576
-UnQ8rcBOQs 13450
crtNd46CDks 11639
D1leA0JKHhE 11553
NJu2oG1Wm98 11452
CapbXdyv4j4 10915
epr5erraEp4 10817
IyQoDgaLM7U 10597
tbZibBnusLQ 10402
_GnCHodc7mk 9422
hvEYlSlRitU 7123