hive hql 行列转换 练习

行列互换

行转列

使用 case when 查询出多列即可，即可增加列。

列转行

1、lateral view explode()

使用炸裂函数可以将1列转成多行，被转换列适用于array、map等类型。 lateral view posexplode(数组)，如有排序需求，则需要索引。将数组炸开成两行(索引 , 值),需要 as 两个别名。

2、case when 结合concat_ws与collect_set/collect_list实现。内层用case when，外层用 collect_set/list收集，对搜集完后用concat_ws分割连接形成列。

练习

1、

编写sql实现行列互换

数据：
id	userid	subject	score
1	001		语文		90
2	001		数学		92
3	001		英语		80
4	002		语文		88
5	002		数学		90
6	002		英语		75.5
7	003		语文		70
8	003		语文		85
9	003		语文		90
10	003		政治		82
实现
userid	语文	数学		英语		政治	
001		90		92		80		0
002		88		90		75.5	0
003		70		85		90		82

SELECT userid 
,SUM(case WHEN subject='语文' THEN score else 0 end) AS yuwen 
,SUM(case WHEN subject='数学' THEN score else 0 end) AS shuxue 
,SUM(case WHEN subject='英语' THEN score else 0 end) AS yingyu 
,SUM(case WHEN subject='政治' THEN score else 0 end) AS zhengzhi 
FROM score 
GROUP BY userid
;

2、

编写sql实现如下

数据：
t1表 uid tags
1   1,2,3
2   2,3 
3   1,2
编写sql实现如下结果：
uid tag 
1   1 
1   2 
1   3 
2   2 
2   3 
3   1 
3   2 

SELECT
uid,
tag
from t1
lateral view explode(split(tags,',')) t as tag
;

3、

用户标签连接查询

数据：
T1表:
Tags 
1,2,3 
1,2
2,3
T2表:
Id  lab 
1 A 
2 B 
3 C
根据T1和T2表的数据，编写sql实现如下结果： 
ids tags 
1,2,3   A,B,C 
1,2 A,B 
2,3 B,C

SELECT tags 
,concat_ws(",",collect_list(lab)) 
FROM 
( 
SELECT t1.tags tags 
,t3.lab lab 
FROM 
( 
SELECT tags 
,id 
FROM exam3t1 t2
lateral view explode (split(tags,",") ) tmp AS id ) t1 
JOIN exam3t2 t3
ON t1.id=t3.id 
) tmp 
GROUP BY tags
; 

4、

用户标签组合

数据：
t1表：
id  tag flag 
a   b   2   
a   b   1   
a   b   3   
c   d   6   
c   d   8  
c   d   8
编写sql实现如下结果： 
id  tag flag 
a   b   1|2|3 
c   d   6|8

SELECT id 
,tag 
,concat_ws("|",collect_set(flag)) 
FROM t1 
GROUP BY id 
,tab
;

5、

用户标签行列互换

数据：
t1表 uid name    tags
1   goudan  chihuo,huaci
2   mazi    sleep 
3   laotie  paly
编写sql实现如下结果：
uid name    tag 
1   goudan  chihuo
1   goudan  huaci 
2   mazi    sleep
3   laotie  paly

--1.2.6
SELECT uid 
,name 
,tag 
FROM t1 lateral view explode 
(split(tags,",") 
) t AS tag 
GROUP BY uid 
,name
;

6、

hive实现词频统计

数据：
t1表： 
uid contents 
1   i|love|china 
2   china|is|good|i|i|like
统计结果如下,如果出现次数一样，则按照content名称排序：
content cnt 
i   3 
china   2 
good    1 
like    1 
love    1 
is  1

--1.2.7
select
content
,count(*)
from wc
lateral view explode(split(contents,'\\|')) t as content
GROUP by content
;

7、

课程行转列

数据：
t1表 
id course 
1,a 
1,b 
1,c 
1,e 
2,a 
2,c 
2,d 
2,f 
3,a 
3,b 
3,c 
3,e
根据编写sql，得到结果如下(表中的1表示选修，表中的0表示未选修)： 
id a b c d e f 
1 1 1 1 0 1 0 
2 1 0 1 1 0 1 
3 1 1 1 0 1 0 8

SELECT id 
,SUM(case WHEN course='a' THEN cnt else 0 end) AS a 
,SUM(case WHEN course='b' THEN cnt else 0 end) AS b 
,SUM(case WHEN course='c' THEN cnt else 0 end) AS c 
,SUM(case WHEN course='d' THEN cnt else 0 end) AS d 
,SUM(case WHEN course='e' THEN cnt else 0 end) AS e 
,SUM(case WHEN course='f' THEN cnt else 0 end) AS f
FROM 
( 
SELECT id 
,course 
,COUNT(*) cnt 
FROM exam4 
GROUP BY id 
,course 
) t 
GROUP BY id
;

8、

兴趣行转列

数据：
t1表 
name    sex hobby 
janson  男   打乒乓球、游泳、看电影
tom 男   打乒乓球、看电影
使用hql实现结果如下: 
name    sex hobby1  hobby2  hobby3 
janson  男   打乒乓球    游泳     看电影 
tom 男   打乒乓球    看电影

SELECT
name,
sex,
nvl(hobby[0],"") as hobby1,
nvl(hobby[1],"") as hobby2,
nvl(hobby[2],"") as hobby3
FROM ho
;

9、

用户商品行列互换

数据：
t1表： 用户   商品
A       P1 
B       P1 
A       P2 
B       P3 
请你使用hql变成如下结果: 
用户    P1   P2    P3 
A        1    1    0    
B        1    0    1

SELECT uname 
,SUM(case WHEN pro='P1' THEN 1 else 0 end) AS P1 
,SUM(case WHEN pro='P2' THEN 1 else 0 end) AS P2 
,SUM(case WHEN pro='P3' THEN 1 else 0 end) AS P3 
FROM t
GROUP BY uname
;

10、

成绩课程行列互换

数据：
t1表：
name    course      score 
aa      English     75
bb      math        85 
aa      math        90
使用hql输出以下结果 
name    English     math 
aa      75          90 
bb      0           85 

SELECT
name,
sum(case when course = "English" then score else 0 end) as English,
sum(case when course = "math" then score else 0 end) as math
from t
GROUP BY name
;

11、

求top3英雄及其pick

数据：
id	names
1	亚索,挖掘机,艾瑞莉娅,洛,卡莎
2	亚索,盖伦,奥巴马,牛头,皇子
3	亚索,盖伦,艾瑞莉娅,宝石,琴女
4	亚索,盖伦,赵信,老鼠,锤石

SELECT * 
FROM 
( 
SELECT l1 
,c1 
,row_number() over(order by c1 desc) r1 
,rud1 
FROM 
( 
SELECT l1 
,COUNT(*) c1 
,round(COUNT(*)/total,3) rud1 
FROM 
( 
SELECT l1 
,COUNT(distinct id) over() total 
FROM hero lateral view explode 
(names 
) t AS l1 ) t1 
GROUP BY l1 
,total ) t2 ) t3 
WHERE r1<=3 
;