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),仅限于转化的字段为数值类型,且为正值的情况。