行列互换

 

1.concat：将同一行数据拼接

drop table student;
create table if not exists student
(
name string,
orderdate string,
cost int,
sex string,
dep string,
class string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';



jack,2017-01-01,10,male,a,A
tony,2017-01-02,15,female,a,A
tom,2017-02-03,23,female,a,B
lili,2017-01-04,29,female,b,B
join,2017-01-05,46,male,b,B



load  data local inpath "/home/kg/hive/student.txt" into table student;

 

select concat(name,"-",dep) from student;
jack-a
tony-a
jack-a
tony-b
jack-b

select concat_ws("-",name,sex,dep) from student;
jack-male-a
tony-female-a
jack-female-a
tony-female-b
jack-male-b

 

2.collect_set：将同一列数据拼接

聚合函数collect_set():将某个字段的值去重汇总，产生array数组

select collect_set(name) from student;
["jack","tony"]

select concat_ws("-",collect_set(name)) from student;
jack-tony

 

相同部门相同等级人的姓名

select name,concat_ws("-",dep,class)dep_class from student;t1
jack    a-A
tony    a-A
tom     a-B
lili    b-B
join    b-B

select dep_class,collect_set(name) from 
(select name,concat_ws("-",dep,class)dep_class from student)t1
group by dep_class;
a-A    ["jack","tony"]
a-B    ["tom"]
b-B    ["lili","join"]

select dep_class,concat_ws("|",collect_set(name)) from 
(select name,concat_ws("-",dep,class)dep_class from student)t1
group by dep_class;
a-A    jack|tony
a-B    tom
b-B    lili|join

 

3.列转行

explode(col):将一列中复杂的array或者map拆分成多行

drop table movie;
create table if not exists movie
(
name string,
hobby array<string>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
collection items terminated by ",";

jack    喜剧,悬疑,战争
tony    悲剧,悬疑,战争
tom    喜剧,悬疑,爱情
lili    喜剧,童话,战争
join    喜剧,文学,战争


load  data local inpath "/home/kg/hive/movie.txt" into table movie;

 

select name,movie_hobby
from
movie lateral view explode(hobby) table_tmp as movie_hobby;

jack    喜剧
jack    悬疑
jack    战争
tony    悲剧
tony    悬疑
tony    战争
tom     喜剧
tom     悬疑
tom     爱情
lili    喜剧
lili    童话
lili    战争
join    喜剧
join    文学
join    战争

 

create table if not exists phone
(
gid string,
phone1 string,
phone2 string,
phone3 string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

into1    111    222    333
into2    114    224    334

select gid,concat_ws(",",phone1,phone2,phone3)phones from phone;t1
into1    111,222,333
into2    114,224,334


select gid,split(phones,",") phone_array
from 
(select gid,concat_ws(",",phone1,phone2,phone3)phones from phone)t1;t2

into1    ["111","222","333"]
into2    ["114","224","334"]



select gid,top_phone
from 
(
select gid,split(phones,",") phone_array
from 
 (select gid,concat_ws(",",phone1,phone2,phone3)phones from phone)t1
)
t2 lateral view explode(phone_array) phone_temp as top_phone;

into1    111
into1    222
into1    333
into2    114
into2    224
into2    334