Hive行列互转

1 多行转多列

• 数据：

张三	语文	90
张三	数学	85
张三	英语	92
李四	语文	75
李四	数学	90
李四	英语	80
王五	语文	95
王五	数学	100
王五	英语	98

• 建表：

create table stu(
name string,
subject string,
score int
)
row format delimited
fields terminated by '\t'
;

• 加载数据：

load data local inpath '/myfile/db1116/stu.txt' into table stu;

• 查询：

select
name,
sum(case when subject='语文' then score else 0 end ) as Chinese,
sum(case when subject='数学' then score else 0 end ) as Math,
sum(case when subject='英语' then score else 0 end ) as English
from stu
group by name
;

• 结果：

result:
OK
name    chinese math    english
张三    90      85      92
李四    75      90      80
王五    95      100     98
Time taken: 4.507 seconds, Fetched: 3 row(s)

2 多列转多行 (一张map表映射成两张表)

• 数据：

datev	uv	newuv	video	newvideo	vv	vip_num	new_vip_num
2019-05-10	5000000	200000	3000000	10000	20000000	500000	80000

• 建表：

create table video(
datev date,
uv int,
newuv int,
video int,
newvideo int,
vv int,
vip_num int,
new_vip_num int
)
row format delimited
fields terminated by '\t'
;

• 加载数据：

load data local inpath '/myfile/db1116/video.txt' into table video;

• 查询：

select
datev,
label,
value
from video
lateral view explode(
map('uv',uv,
'新增UV',newuv,
'视频存量',video,
'新增视频',newvideo,
'播放量',vv,
'会员数',vip_num,
'新增会员数',new_vip_num
) ) k as label,value
;

• 结果：

date	label	value
2019-05-10	UV	5000000
2019-05-10	新增UV	200000
2019-05-10	视频存量	3000000
2019-05-10	新增视频	10000
2019-05-10	播放量	20000000
2019-05-10	会员数	500000
2019-05-10	新增会员数	80000

3 多行转多列

• 假设数据表：

row1：
col1   col2    col3
a	c	1
a	d	2
a	e	3
b	c	4
b	d	5
b	e	6

• 现在要将其转化为：

col1   c      d      e
a      1      2      3
b      4      5      6

• 建表：

create table row1(
col1 string,
col2 string,
col3 int
)
row format delimited
fields terminated by '\t'
;

• 加载数据：

load data local inpath '/myfile/db1116/row1.txt' into table row1;

• 查询：

select
col1,
sum(case when col2='c' then col3 else 0 end) as c,
sum(case when col2='d' then col3 else 0 end) as d,
sum(case when col2='e' then col3 else 0 end) as e
from row1
group by col1
;

注：需要使用到max(case … when … then … else 0 end)，仅限于转化的字段为数值类型，且为正值的情况。