Hive学习笔记-行列互转

行转列

问题：

当某一维度或多维度组合下出现多行数据，如何将其聚合至一行进行保存？

实例：

表名：tbl_student_score
字段：stu_id：学号；stu_name：姓名；subject：学科；score：分数；

学号	姓名	学科	分数
1001	张三	数学	99
1001	张三	语文	60
1001	张三	英语	70
1002	李四	数学	100
1002	李四	语文	99
1002	李四	英语	98
1003	王五	数学	95
1003	王五	语文	80
1003	王五	英语	99

最终结果展示：

学号	姓名	学科	分数
1001	张三	数学,语文,英语	99,60,70
1002	李四	数学,语文,英语	100,99,98
1003	王五	数学,语文,英语	95,80,99

解决方式：

方法1：

select
	stu_id,
	stu_name,
	concat_ws(',', collect_set(subject)) as subject,
	concat_ws(',', collect_set(score)) as score
from tbl_student_score
group by
	stu_id,
	stu_name;

方法2：

select
	stu_id,
	stu_name,
	concat_ws(',', collect_list(subject)) as subject,
	concat_ws(',', collect_list(score)) as score
from tbl_student_score
group by
	stu_id,
	stu_name;

沉淀：

函数用法：

函数	用法	返回结果类型	作用
Collect_set	Collect_set(column)	Array	收集 group by 聚合字段，返回去重后数组（集合）
Concat_ws	Concat_ws(separator, columnA, columnB,…)	String	以指定分隔符（separator）连接多个字符串
Collect_list	Collect_list(column)	Array	收集 group by 聚合字段，返回不去重后数组（集合）

列转行

问题：

当某一行数据中的某一列或某些列数据聚合多个数据，如何将其拆解并分行保存？

实例：

表名：tbl_student_score_collect
字段：stu_id：学号；stu_name：姓名；subject：学科；score：分数；

学号	姓名	学科	分数
1001	张三	数学,语文,英语	99,60,70
1002	李四	数学,语文,英语	100,99,98
1003	王五	数学,语文,英语	95,80,99

最终结果展示：
结果1：

学号	姓名	学科
1001	张三	数学
1001	张三	语文
1001	张三	英语
1002	李四	数学
1002	李四	语文
1002	李四	英语
1003	王五	数学
1003	王五	语文
1003	王五	英语

结果2：

学号	姓名	学科	分数
1001	张三	数学	99
1001	张三	语文	60
1001	张三	英语	70
1002	李四	数学	100
1002	李四	语文	99
1002	李四	英语	98
1003	王五	数学	95
1003	王五	语文	80
1003	王五	英语	99

解决方式：

方法1：

Select
	Stu_id,
	Stu_name,
	Subject_explode
From tbl_student_score_collect
Lateral view explode(split(subject,',')) sbj as Subject_explode;

方法2：

Select
	Stu_id,
	Stu_name,
	Subject_explode,
	Score_explode
From tbl_student_score_collect
Lateral view posexplode(split(subject,',')) sbj as pos, Subject_explode
Lateral view posexplode(split(socre,',')) sc as pos, Score_explode
Where sbj.pos = sc.pos;

沉淀：

函数用法：

函数	用法	返回结果类型	作用
Explode	Explode(Array)	String	将列中复杂的array数据拆分成多行元素
Posexplode	Posexplode(Array)	String	将列中复杂的array数据拆分成多行元素，同时返回元素在数据中的索引位置

Lateral view：

基本描述：

Lateral view一般与UDTF（用户自定义生成函数）结合使用，如，explode()，split()；将UDTF应用于表中的每一行，然后将结果输出行连接到输入行，以形成具有提供表别名的虚拟表。

基本语法：

Select
	Xxx,
	Xxxx,
	ColumnAlias
From tbl_name
Lateral view udtf(expression) tableAlias AS columnAlias (',' columnAlias)*