对于行专列和列转行,有两种不同的理解,这里将两中不同的理解都整理出来,供大家参考。
1. 字段值不发生改变,只是改变行列的分布
1.1. 行转列
有如下的一张成绩表tab_scores,每个人的成绩是按行存储的,要求每个人的成绩按列存储,即为行转列
name subject score Tom Math 85 Tom Chinese 92 Tom English 88 Jack Math 92 Jack Chinese 86 Jack English 70 Korry Math 100 Korry Chinese 100 Korry English 90
转换sql如下:
select name,
sum ( case subject when 'Math' then score else 0 end ) as Math,
sum ( case subject when 'Chinese' then score else 0 end ) as Chinese,
sum ( case subject when 'English' then score else 0 end ) as English
from tab_scores
group by name
查询结果如下:
name Math English Chinese Tom 85 92 88 Jack 92 86 70 Korry 100 100 90
1.2. 列转行
数据如下
name Math English Chinese Tom 85 92 88 Jack 92 86 70 Korry 100 100 90
转换sql如下:
select name, 'Math' , Math from tab_scores
union all
select name, 'English' , English from tab_scores
union all
select name, 'Chinese' , Chinese from tab_scores
查询结果如下
name subject score Tom Math 85 Tom Chinese 92 Tom English 88 Jack Math 92 Jack Chinese 86 Jack English 70 Korry Math 100 Korry Chinese 100 Korry English 90
2. 字段值进行分割,形成新的行或者列
2.1. 行转列:把多行转成一列(多行变一行)
需要用的函数
collect_set()/collect_list() 将一堆数据收集成数据集 collect_set( )函数只接受基本数据类型,作用是对参数字段进行去重汇总,返回array类型字段; collect_list()函数和collect_set( )作用一样,只是前者不去重,后者去重。 concat_ws() concat_ws(‘,’,Array),第一个参数是连接符,第二个是数据集、将一个数据集的数据按照给定的符号连接成字符串
数据如下,,数据表为hobbyInfo
name hobby rose eat rose sleep korry game korry basketball korry run
转换sql:
select name, concat_ws( ',' , collect_set( hobby) ) as hobby
from hobbyInfo group by name
查询结果:
name hobby rose eat,sleep korry game,baseketball,run
2.2. 列转行,把一列转成多行
需要用到的函数
split(): 将一个字符串按照指定字符分割,结果为一个array; split(str,‘,’),将字符串str按逗号进行分割得到一个数数组 explode(): 炸裂函数,将一列复杂的array或者map拆分为多行,它的参数必须为map或array; explode(arr) ,将数组arr拆分为多行的数据 lateral view: lateral view udtf(字段名)表别名/表临时名as列别名/列临时名。lateral view用于和split, explode等UDTF一起使用,它能够将一行数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF,UTDF会把一行拆分成一或者多行,lateralview再把结果组合,产生一个支持别名表的虚拟表。
数据如下:表hobbyInfo
name hobby rose eat,sleep korry game,baseketball,run
转换sql
select a. name, tabHobby. colHobby as hobby from hobbyInfo a
lateral view explode( split( a. hobby, ',' ) ) tabHobby as colHobby
查询结果如下
name hobby rose eat rose sleep korry game korry basketball korry run