列转行（一列转多行）

常用的列转行函数有：




lateral view explode(array或map类型的字段) tableAlias AS columnAlias




可以理解为，一个select子句只能获得一个explode产生的视图，但是炸裂后的返回视图无法与其它字段一同输出，如果想要显示多个列，就需要将多个视图合并，lateral view 侧视图就解决了这个问题。首先通过explode函数将array或map类型的字段炸裂为多行，接着将炸裂后的多行数据与原表的每行数据进行一个笛卡尔积的join，将结果形成一个虚拟表，同时可以对udtf炸裂后的字段设置别名

• 
  
  hive内置的UDTF函数
  

• 
  
  LATERAL VIEW简介
  

语法

：LATERAL VIEW udtf(expression) tableAlias AS columnAlias (‘,’ columnAlias)*

用法

：和UDTF函数一起使用，为原始表的每行数据调用UDTF函数炸裂为多行数据，lateral view 将原始表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表，可以对UDTF产生的记录设置字段名称。

作用

：主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF，不能包含其它字段以及多个UDTF的情况，可以扩展原来的表数据。UDTF炸裂后的字段可以使用在 GROUP BY 、 CLUSTER BY 、 DISTRIBUTE BY 、 SORT BY 等语句中，无需再嵌套一层子查询。

• 
  
  UDTF函数的限制
  

1、在SELECT子句中不支持UDTF函数和其他字段同时出现，只能获得一个udtf产生的视图。


如explode函数炸裂后的返回视图无法与其它字段一同输出，只能单独使用explode炸裂后的返回视图。

select  
 'zs' as  uid,
 explode(array('chinese','math','english')) as info;
 
Error: Error while compiling statement: FAILED: SemanticException [Error 10081]: 
UDTFs are not supported outside the SELECT clause, nor nested in expressions (state=42000,code=10081)

解决：


如果希望得到UDTF和其他字段的结果，需要结合lateral view使用

select 
 uid,info 
from  
 (select 'zs' as uid) a 
lateral view  explode(array('chinese','math','english')) tb as info;

2、在SELECT子句中只支持单个UDTF表达式，无法同时使用多个UDTF函数

 select 
   explode(array(1,2,3)) as id,
   explode(array('zs','ls','ww')) as name ;

Error: Error while compiling statement: FAILED: SemanticException 1:35 Only a single expression in the SELECT clause is supported with UDTFs. Error encountered near token 'name' (state=42000,code=40000)

解决：


结合lateral view使用,可以同时使用多个UDTF函数

select 
 test,id,name
from 
 (select 'test' as test) a
lateral view explode(array(1,2,3)) t1 as id
lateral view explode(array('zs','ls','ww')) t2 as name ;

3、不支持UDTF函数间的嵌套

select explode(explode(array(array(66,666),array(88,888),array(99,999))))

Error: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTFs are not supported outside the SELECT clause, nor nested in expressions (state=42000,code=10081)

解决:


UDTF函数间不支持嵌套，结合lateral view逐层嵌套子查询

select
 uid,num
from
(
 select 
   uid,nums
 from
   (select 'gd' as uid)a
 lateral view explode(array(array(66,666),array(88,888),array(99,999))) t as nums
)b
lateral view explode(nums) t as num

4、在SELECT子句中UDTF不支持GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY语句

select 
 explode(uinfo) as (name,info)
from
 (
  select 
     8 as class,
     map('zs',66,'ls',99,'ww',55) as uinfo
   union all
   select 
     2 as class,
     map('gd',888,'eg',77,'xq',66) as uinfo
 ) a
sort by
 class

Error: Error while compiling statement: FAILED: SemanticException [Error 10078]: SORT BY is not supported with a UDTF in the SELECT clause (state=42000,code=10078)

解决：


结合lateral view使用，UDTF炸裂后的字段可以使用在 GROUP BY 、 CLUSTER BY 、 DISTRIBUTE BY 、 SORT BY 等语句中，无需再嵌套一层子查询。

select 
 class,name,score
from
 (
   select 
     8 as class,
     map('zs',66,'ls',99,'ww',55) as uinfo
   union all
   select 
     2 as class,
     map('gd',888,'eg',77,'xq',66) as uinfo
 ) a
lateral view explode(uinfo) t as name,score
sort by
 class

行转列(多行转一行)

将某列中的多行数据合并为一行，常用的行转列函数主要有：

collect_list()

与

collect_set()

。两者主要的区别是是否去重，通常结合group by使用，返回值都是array类型

collect_list()函数不去重

select
 class,
 collect_list(concat_ws(':',type,p_num)) cinfo
from
(
 select
   '7-1'        as class,
   'excellent'  as type,
   '30'         as p_num
 union all
 select
   '7-1'        as class,
   'excellent'  as type,
   '30'         as p_num
 union all
 select
   '7-1'        as class,
   'good'       as type,
   '38'         as p_num
 union all
 select
   '7-1'        as class,
   'bad'        as type,
   '25'         as p_num
)a
group by
 class;

collect_set()函数去重


set集合是无序的，可以结合sort_array()函数进行排序

select
 class,
 collect_set(concat_ws(':',type,p_num)) cinfo
 --sort_array(collect_set(concat_ws(':',type,p_num))) cinfo
from
(
 select
   '7-1'        as class,
   'excellent'  as type,
   '30'         as p_num
 union all
 select
   '7-1'        as class,
   'excellent'  as type,
   '30'         as p_num
 union all
 select
   '7-1'        as class,
   'good'       as type,
   '38'         as p_num
 union all
 select
   '7-1'        as class,
   'bad'        as type,
   '25'         as p_num
)a
group by
 class;

拼接函数

常用的拼接函数有

concat()

、

concat_ws


常用于对表中的多列数据进行拼接处理合并，或对一列数据进行处理转换

concat()函数


可以输入任意个参数，主要可以用作字段间的拼接。返回类型为字符串。

concat函数

会跳过空字符串

， 但是需要注意的是，该函数

不会跳过null值

，也就是说，拼接时只要其中有

一个参数为null，则返回结果就为NULL


以下sql为例：

select concat('a','-',null,'b');

--返回结果为null，因为该函数不会跳过null值

正常情况的拼接：

select concat('a','-','','b');

--返回结果为 a-b,因为在拼接时会跳过空字符串

concat_ws(‘分割符’,str1,str2…)

• 可以理解为是特殊类型的concat函数，
  
  第一个参数为参数间的分隔符
  
  ，之后的是带拼接的参数，将各个字符串通过一个指定的分隔符进行拼接。
• 若分隔符为null，返回结果也将为NULL。但是该函数会跳过除分隔符外的所有NULL以及空字符串参数，不进行拼接。
• 在hive中，concat_ws()函数只接受string 或 array<string>类型的参数。
  

---

当分隔符为null时，返回结果为null

select concat_ws(null,'a','b');

--返回结果为 null,因为分隔符为null，因此返回结果为null

---

参数中有null时，会跳过null值

select concat_ws('-','a',null,'b',null);

--返回结果为 a-b,不同于concat函数，会跳过除分隔符外的null值

---

拼接array<String>类型时，会对数组中的元素逐个拼接

select concat_ws('&',array('1','2'),'a');

--返回结果为 1&2&a，会对数组中的元素逐个拼接