行轉(zhuǎn)列
定義：多行轉(zhuǎn)多列或者多行轉(zhuǎn)一列
原始數(shù)據(jù)如下：
| 姓名 | 科目 | 分?jǐn)?shù) |
|---|---|---|
| 小明 | 語文 | 90 |
| 小明 | 數(shù)學(xué) | 98 |
| 小明 | 英語 | 95 |
| 小紅 | 語文 | 92 |
| 小紅 | 數(shù)學(xué) | 93 |
| 小紅 | 英語 | 97 |
drop table tmp.test0002;
create table tmp.test0002
(
name string comment '姓名',
type string comment '科目',
score string comment '分?jǐn)?shù)'
);
insert into table tmp.test0002
select '小明', '語文', '90';
insert into table tmp.test0002
select '小明', '數(shù)學(xué)', '98';
insert into table tmp.test0002
select '小明', '英語', '95';
insert into table tmp.test0002
select '小紅', '語文', '92';
insert into table tmp.test0002
select '小紅', '數(shù)學(xué)', '93';
insert into table tmp.test0002
select '小紅', '英語', '97';
多行轉(zhuǎn)多列示例一（利用case when函數(shù)）：
| 姓名 | 語文 | 數(shù)學(xué) | 英語 |
|---|---|---|---|
| 小明 | 90 | 98 | 95 |
| 小紅 | 92 | 93 | 97 |
select name,
max(case when type = '語文' then score else 0 end),
max(case when type = '數(shù)學(xué)' then score else 0 end),
max(case when type = '英語' then score else 0 end)
from tmp.test0002
group by name
order by name;
多行轉(zhuǎn)多列示例二（利用str_to_map函數(shù)）：
| 姓名 | 語文 | 數(shù)學(xué) | 英語 |
|---|---|---|---|
| 小明 | 90 | 98 | 95 |
| 小紅 | 92 | 93 | 97 |
select name
,info['語文'] as Chinese
,info['數(shù)學(xué)'] as Math
,info['英語'] as English
from (select name,str_to_map(concat_ws(',',collect_set(concat_ws(':',type,cast(score as string))))) as info
from tmp.test0002
group by name
) a;
多行轉(zhuǎn)一列示例：
| 姓名 | 分?jǐn)?shù) |
|---|---|
| 小明 | 90,98,95 |
| 小紅 | 92,93,97 |
select name,
concat_ws(',', collect_list(score)) as score_list
from tmp.test0002
group by name
order by name;
列轉(zhuǎn)行
定義：多列轉(zhuǎn)多行或一列轉(zhuǎn)多行
多列轉(zhuǎn)多行示例一：
原始數(shù)據(jù)如下：
| 姓名 | 類型 | 分?jǐn)?shù) |
|---|---|---|
| 小明 | 語文,數(shù)學(xué),英語 | 90,98,95 |
| 小紅 | 語文,數(shù)學(xué),英語 | 92,93,97 |
drop table tmp.test0003;
create table tmp.test0003
(
name string comment '姓名',
type string comment '學(xué)科',
score string comment '分?jǐn)?shù)'
);
insert into table tmp.test0003
select '小明', '語文,數(shù)學(xué),英語', '90,98,95';
insert into table tmp.test0003
select '小紅', '語文,數(shù)學(xué),英語', '92,93,97';
轉(zhuǎn)換后：
| 姓名 | 學(xué)科 | 分?jǐn)?shù) |
|---|---|---|
| 小明 | 語文 | 90 |
| 小明 | 數(shù)學(xué) | 98 |
| 小明 | 英語 | 95 |
| 小紅 | 語文 | 92 |
| 小紅 | 數(shù)學(xué) | 93 |
| 小紅 | 英語 | 97 |
select name,
type1,
score1
from tmp.test0003
lateral view
explode(
str_to_map(
concat(
'語文=', split(score, ',')[0],
'&數(shù)學(xué)=', split(score, ',')[1],
'&英語=', split(score, ',')[2]
)
, '&', '=')
) lateral_table as type1, score1
;
多列轉(zhuǎn)多行示例二：
原始數(shù)據(jù)如下：
| 姓名 | 語文 | 數(shù)學(xué) | 英語 |
|---|---|---|---|
| 小明 | 90 | 98 | 95 |
drop table tmp.test0003;
create table tmp.test0003
(
name string comment '姓名',
chinese string comment '語文',
math string comment '數(shù)學(xué)',
english string comment '英語'
);
insert into table tmp.test0003
select '小明', '90', '98', '95';
轉(zhuǎn)換后：
| 姓名 | 學(xué)科 | 分?jǐn)?shù) |
|---|---|---|
| 小明 | 語文 | 90 |
| 小明 | 數(shù)學(xué) | 98 |
| 小明 | 英語 | 95 |
select a.name
, b.label
, b.value
from (select *
from tmp.test0003) a
lateral view explode(map(
'語文', chinese
, '數(shù)學(xué)', math
, '英語', english
)) b as label, value;
一列轉(zhuǎn)多行示例
原始數(shù)據(jù)如下：
| 姓名 | 分?jǐn)?shù) |
|---|---|
| 小明 | 90,98,95 |
| 小紅 | 92,93,97 |
drop table tmp.test0003;
create table tmp.test0003
(
name string comment '姓名',
score string comment '分?jǐn)?shù)'
);
insert into table tmp.test0003
select '小明', '90,98,95';
insert into table tmp.test0003
select '小紅', '92,93,97';
轉(zhuǎn)換后：
| 姓名 | 分?jǐn)?shù) |
|---|---|
| 小明 | 90 |
| 小明 | 98 |
| 小明 | 95 |
| 小紅 | 92 |
| 小紅 | 93 |
| 小紅 | 97 |
select name,
b.scores
from tmp.test0003
lateral view explode(split(score, ',')) b as scores;
