行列转换的4种情况

行列转换

1. 多行转多列

id	subject	score
1001	语文	120
1001	数学	130
1001	英语	140
1002	语文	120
1002	数学	121
1002	英语	122

转化为：

id	chinese	math	english
1001	120	130	140
1002	120	121	122

sql 语句：

select 
id
,max(case when subject = '语文' then score else 0 end) as chinese
,max(case when subject = '数学' then score else 0 end) as math
,max(case when subject = '英语' then score else 0 end) as english
from student
group by id;

2. 多行转单列

id	subject	score
1001	语文	120
1001	数学	130
1001	英语	140
1002	语文	120
1002	数学	121
1002	英语	122

转化为：

id	score
1001	120,130,140
1002	120,121,122

sql 语句：

select 
id
,concat_ws(',',collect_list(cast(score as string)))
from student
group by id;

解析：

cancat：用于实现字符串拼接，不可指定分隔符。如果任意一个元素为null，结果就为null
语法：concat(element1,element2,element3……)

concat_ws：用于实现字符串拼接，可以指定分隔符。任意一个元素不为null，结果就不为null
语法：concat_ws(SplitChar，element1，element2……)

collect_list：用于将一列中的多行合并为一行，不进行去重
语法：collect_list(col)

collect_set：用于将一列中的多行合并为一行，并进行去重
语法：collect_set(col)

3. 多列转多行

id	chinese	math	english
1001	120	130	140
1002	120	121	122

转化为：

id	subject	score
1001	语文	120
1001	数学	130
1001	英语	140
1002	语文	120
1002	数学	121
1002	英语	122

sql 语句：

select id,'语文' as subject,chinese from student
union all
select id,'数学' as subject,math from student
union all
select id,'英语' as subject,english from student
;

4. 单列转多行

id	score
1001	120,130,140
1002	120,121,122

转化为：

id	score
1001	120
1001	130
1001	140
1002	120
1002	121
1002	122

sql 语句：

select id,col1 from student
lateral view
explode(split(score,',')) temp as col1;

解析：

select id,explode(split(score,',')) from student;
-- 报错原因：炸裂之后，转换成多列，而id 字段依然是1列，列数不匹配

侧视图的原理是将UDTF的结果构建成一个类似于视图的表，
然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表。
一般只要使用UDTF，就会固定搭配lateral view使用

基本语法如下：
select …… from tabel_name lateral view UDTF(xxx) 别名 as col1,col2,col3……