hivesql行转列和列转行

对于行专列和列转行，有两种不同的理解，这里将两中不同的理解都整理出来，供大家参考。

1. 字段值不发生改变，只是改变行列的分布

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

2. 转换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

3. 查询结果如下：

name	Math	English	Chinese
Tom	85	92	88
Jack	92	86	70
Korry	100	100	90

1.2. 列转行

1. 数据如下

name	Math	English	Chinese
Tom	85	92	88
Jack	92	86	70
Korry	100	100	90

2. 转换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

3. 查询结果如下

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. 行转列：把多行转成一列（多行变一行）

1. 需要用的函数

• collect_set()/collect_list()
  将一堆数据收集成数据集
  collect_set( )函数只接受基本数据类型，作用是对参数字段进行去重汇总，返回array类型字段;
  collect_list()函数和collect_set( )作用一样，只是前者不去重,后者去重。
• concat_ws()
  concat_ws(‘,’,Array)，第一个参数是连接符，第二个是数据集、将一个数据集的数据按照给定的符号连接成字符串

2. 数据如下,，数据表为hobbyInfo

name	hobby
rose	eat
rose	sleep
korry	game
korry	basketball
korry	run

3. 转换sql：

select name,concat_ws(',',collect_set(hobby)) as hobby 
from hobbyInfo group by name

4. 查询结果：

name	hobby
rose	eat,sleep
korry	game,baseketball,run

2.2. 列转行，把一列转成多行

1. 需要用到的函数

• 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再把结果组合，产生一个支持别名表的虚拟表。

2. 数据如下：表hobbyInfo

name	hobby
rose	eat,sleep
korry	game,baseketball,run

3. 转换sql

select a.name,tabHobby.colHobby as hobby from hobbyInfo a
lateral view explode(split(a.hobby,',')) tabHobby as colHobby
-- tabHobby是侧视图的别名，colHobby是解析出来的字段名，#注意，as不能省略

4. 查询结果如下

name	hobby
rose	eat
rose	sleep
korry	game
korry	basketball
korry	run