十一、行转列、列转行函数

一、行转列

1、相关行数

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

2、数据准备

数据

[root@hadoop102 datas]$ vi person.txt

孙悟空  白羊座  A
大海    射手座  A
宋宋    白羊座  B
猪八戒  白羊座  A
凤姐    射手座  A

建表
create table person_info(
name string, 
constellation string, 
blood_type string) 
row format delimited fields terminated by "\t";

导入数据
load data local inpath '/opt/module/datas/person.txt' into table person_info;

3、concat函数使用

hive (default)> select name,concat(constellation,',',blood_type) hb from person_info;
OK
name    hb
孙悟空  白羊座,A
大海    射手座,A
宋宋    白羊座,B
猪八戒  白羊座,A
凤姐    射手座,A

4、collect_set 数据列去重聚合

select hb,collect_set(name) names 
from (
select name,concat(constellation,',',blood_type) hb from person_info
) t 
group by t.hb;

hb      names
射手座,A        ["大海","凤姐"]
白羊座,A        ["孙悟空","猪八戒"]
白羊座,B        ["宋宋"]

5、concat_ws 将arr数组列合并为字符串

select hb,concat_ws('|',collect_set(name)) namestr
from (
select name,concat(constellation,',',blood_type) hb from person_info
) t 
group by t.hb;

hb      namestr
射手座,A        大海|凤姐
白羊座,A        孙悟空|猪八戒
白羊座,B        宋宋

二、列转行

1、函数说明

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。

LATERAL VIEW用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

2、数据准备

将前面数据聚合行函数sql，新建表

create table test2 as
select hb,collect_set(name) names 
from (
select name,concat(constellation,',',blood_type) hb from person_info
) t 
group by t.hb;

#数据查看
hive (default)> select hb,names from test2;
OK
hb      names
射手座,A        ["大海","凤姐"]
白羊座,A        ["孙悟空","猪八戒"]
白羊座,B        ["宋宋"]

3、将names数据炸裂开变为行

hive (default)> select hb,name from test2 lateral view explode(names) table_tmp as name;
OK
hb      name
射手座,A        大海
射手座,A        凤姐
白羊座,A        孙悟空
白羊座,A        猪八戒
白羊座,B        宋宋