1.一行转多行
vi test.txt
a b 1,2,3
c d 4,5,6
create table test(
col1 string,
col2 string,
col3 string
)
comment'一行转多行测试表'
row format delimited fields terminated by '\t'
stored as textfile;
load data local inpath '/root/col_to_row.txt' into table test;
+
| test.col1 | test.col2 | test.col3 |
+
| a | b | 1,2,3 |
| c | d | 4,5,6 |
+
select
col1,
col2,
tmp.col3
from test
lateral view explode(split(col3,',')) tmp as col3
;
+
| col1 | col2 | tmp.col3 |
+
| a | b | 1 |
| a | b | 2 |
| a | b | 3 |
| c | d | 4 |
| c | d | 5 |
| c | d | 6 |
+
create table array_to_row_test(
col1 string,
col2 string,
col3 array<string>
)
comment'一行转多行测试表'
row format delimited fields terminated by '\t'
collection items terminated by ','
stored as textfile;
select * from array_to_row_test;
+
| array_to_row_test.col1 | array_to_row_test.col2 | array_to_row_test.col3 |
+
| a | b | ["1","2","3"] |
| c | d | ["4","5","6"] |
+
select
col1,
col2,
col4
from array_to_row_test
lateral view explode(col3) tmp as col4
;
+
| col1 | col2 | col4 |
+
| a | b | 1 |
| a | b | 2 |
| a | b | 3 |
| c | d | 4 |
| c | d | 5 |
| c | d | 6 |
+
2.多行转一行
源表:
+
| test2.field1 | test2.field2 | test2.field3 |
+
| a | b | 1 |
| a | b | 2 |
| a | b | 3 |
| c | d | 4 |
| c | d | 5 |
| c | d | 6 |
+
目标表:
+
| field1 | field3 |
+
| a | [1,2,3] |
| c | [4,5,6] |
+
函数介绍:
collect_set(字段)
collect_list(字段)
计算:
select
field1,
field2,
collect_set(field3) as field3
from test2
group by field1,field2
;
+
| field1 | field2 | field3 |
+
| a | b | [1,2,3] |
| c | d | [4,5,6] |
+
concat(字段,字段,字段...)
concat_ws(分割符,字段1,字段2...)
select
field1,
field2,
concat_ws(',',collect_set(concat(field3,''))) as field3
from test2
group by field1,field2
;
+
| field1 | field2 | field3 |
+
| a | b | 1,2,3 |
| c | d | 4,5,6 |
+
3.行转列
建表:
create table student_score(s_id int,s_name string,s_sub string,s_score bigint);
insert into student_score values
(1,'张三','数学',90),
(2,'张三','语文',85),
(3,'张三','英语',92),
(4,'李四','数学',88),
(5,'李四','语文',91),
(6,'李四','英语',99),
(7,'王五','数学',100),
(8,'王五','语文',82),
(9,'王五','英语',88);
源表:
+
| student_score.s_id | student_score.s_name | student_score.s_sub | student_score.s_score |
+
| 1 | 张三 | 数学 | 90 |
| 2 | 张三 | 语文 | 85 |
| 3 | 张三 | 英语 | 92 |
| 4 | 李四 | 数学 | 88 |
| 5 | 李四 | 语文 | 91 |
| 6 | 李四 | 英语 | 99 |
| 7 | 王五 | 数学 | 100 |
| 8 | 王五 | 语文 | 82 |
| 9 | 王五 | 英语 | 88 |
+
目标表:
+
| 姓名 | 语文 | 数学 | 英语 |
+
| 张三 | 85 | 90 | 92 |
| 李四 | 91 | 88 | 99 |
| 王五 | 82 | 100 | 88 |
+
计算:
select
s_name as `姓名`,
max(case when s_sub='语文' then s_score else 0 end) as `语文`,
max(case when s_sub='数学' then s_score else 0 end) as `数学`,
max(case when s_sub='英语' then s_score else 0 end) as `英语`
from student_score
group by s_name;
+
| 姓名 | 语文 | 数学 | 英语 |
+
| 张三 | 85 | 90 | 92 |
| 李四 | 91 | 88 | 99 |
| 王五 | 82 | 100 | 88 |
+
4.列转行
建表:
create table student_score2 as
select
s_name as `姓名`,
max(case when s_sub='语文' then s_score else 0 end) as `语文`,
max(case when s_sub='数学' then s_score else 0 end) as `数学`,
max(case when s_sub='英语' then s_score else 0 end) as `英语`
from student_score
group by s_name;
源表:
+
| 姓名 | 语文 | 数学 | 英语 |
+
| 张三 | 85 | 90 | 92 |
| 李四 | 91 | 88 | 99 |
| 王五 | 82 | 100 | 88 |
+
目标表:
+
| student_score.s_name | student_score.s_sub | student_score.s_score |
+
| 张三 | 数学 | 90 |
| 张三 | 语文 | 85 |
| 张三 | 英语 | 92 |
| 李四 | 数学 | 88 |
| 李四 | 语文 | 91 |
| 李四 | 英语 | 99 |
| 王五 | 数学 | 100 |
| 王五 | 语文 | 82 |
| 王五 | 英语 | 88 |
+
计算:
select `姓名` as s_name,'数学' as s_sub,`数学` as s_score from student_score2
union all
select `姓名` as s_name,'语文' as s_sub,`语文` as s_score from student_score2
union all
select `姓名` as s_name,'英语' as s_sub,`英语` as s_score from student_score2
;
+
| _u1.s_name | _u1.s_sub | _u1.s_score |
+
| 张三 | 数学 | 90 |
| 张三 | 语文 | 85 |
| 张三 | 英语 | 92 |
| 李四 | 数学 | 88 |
| 李四 | 语文 | 91 |
| 李四 | 英语 | 99 |
| 王五 | 数学 | 100 |
| 王五 | 语文 | 82 |
| 王五 | 英语 | 88 |
+
|