hive-行转列列转行

一、

 

ids	type
1	A
1	C
1	E
2	B
2	D
2	F

 

ids	A	B	C	D	E	F
1	1	0	1	0	1	0
2	0	1	0	1	0	1

将第一个表格的数据变成第二种

两种方式：1. 

 select ids, 
 count(if(type='A',1,null))A,
 count(if(type='B',1,null))B,
 count(if(type='C',1,null))C,
 count(if(type='D',1,null))D,
 count(if(type='E',1,null))E,
 count(if(type='F',1,null))F from lp.study group by ids;

2.

 select ids,sum(if(type='A',1,0))A,
 sum(if(type='B',1,0))B,
 sum(if(type='C',1,0))C,
 sum(if(type='D',1,0))D,
 sum(if(type='E',1,0))E,
 sum(if(type='F',1,0))F from lp.study group by ids;

 

二.行转列 使用 collect_set

 

ids	type
1	A
1	C
1	E
2	B
2	D
2	F

 

ids	type
1	A,C,E
2	B,D,F

将第一个表格转为第二个表格

 

select ids,concat_ws(",",collect_set(type)) types  from lp.study group by ids;

执行下个句子了解

select ids,concat_ws(',',collect_set(type)) types
from
(
select '1' ids,'A' type
union all
select '1' ids,'B' type
union all
select '1' ids,'C' type
union all
select '2' ids,'E' type
union all
select '2' ids,'F' type
union all
select '2' ids,'D' type
) a
group by ids;

三.列传行 使用 lateral view explode

ids	type
1	A,C,E
2	B,D,F

 

ids	type
1	A
1	C
1	E
2	B
2	D
2	F

将第一个表格转为第二个表格

select ids,type from 
 (select ids,concat_ws(",",collect_set(type)) types  from lp.study group by ids) a lateral view explode(split(types,",")) col as type ;

执行下面这个句子了解

select ids,type from
(
select '1' ids,'A,B,C' types
union all
select '2' ids,'D,E,F' types
)
a lateral view explode(split(types,",")) col as type ;