目录
一、行方向
1. 行方向的合并
1.1 concat 函数
1.2 concat_ws 函数
2. 行方向的拆分
二、列方向
1. 列方向的合并
1.1 group_concat 函数
1.2 collect_list 函数
1.3 collect_set 函数
2. 列方向的拆分
2.1 explode 函数
2.2 lateral view
一、行方向
1. 行方向的合并
将同一行某几列的数据以分隔符分隔，合并到同一列。 concat 与 concat_ws 函数
1.1 concat 函数
concat 函数用于将多个字符串连接成一个字符串。语法格式：concat(str1, str2, …) 。如有任何一个参数为 NULL ，则返回值为 NULL，可以考虑使用 nvl 函数将 NULL 替换为 '' 。
-- 简单合并
select concat(column1, column2, column3) as merged_column from table_1;
select concat('Hello', 'World'); -- HelloWorld
-- NULL值的处理
select concat(NULL, 'Hello', 'World'); -- null
select concat(nvl(olumn1, ''), column2, column3);
1.2 concat_ws 函数
concat_ws 代表 concat with separator（分隔符），是 concat 的特殊形式。语法格式：concat_ws(separator, str1, str2, …) 。如有任何一个参数为 NULL ，则返回值为 NULL，可以考虑使用 nvl 函数将 NULL 替换为 '' 。
-- 带分隔符合并
select concat_ws(',', column1, column2, column3) as merged_column from table_1;
select concat_ws('-', 'Hello', 'World'); -- Hello-World
-- NULL值的处理
select concat_ws('-', NULL, 'Hello', 'World'); -- null
select concat_ws(',', nvl(olumn1, ''), column2, column3);
2. 行方向的拆分
将一列的数据通过分隔符拆分成几列。split 函数split 函数用于切分数据，将一串字符串切割成一个数组。语法格式：split(string str, string pat) 。返回值：array。
-- 拆分成一个数组列
select split('wo shi xiao ming',' ') as new_column from table_1; -- ['wo','shi','xiao','ming']
-- 根据数组索引拆分成多列
select split('1_John','_')[0] as id
,split('1_John','_')[1] as name
from table_1; -- 1 John
二、列方向
1. 列方向的合并
将同一列某几行的数据合并到同一行。 group_concat、collect_list 和 collect_set 函数
1.1 group_concat 函数
group_concat 函数用于将同一组的多行值合并成一个字符串。语法格式：group_concat(expr[, sep]) 。expr 表示要合并的字段或表达式，sep 表示分隔符，默认为逗号。
-- 默认分隔符合并
select name
,group_concat(course) as courses
from table_1
group by name; -- Math,English
-- 指定分隔符合并
select name
,group_concat(course, '_') as courses
from table_1
group by name; -- Math_English
1.2 collect_list 函数
collect_list 函数用于将多个值收集到一个列表中，不去重。语法格式：collect_list(expr) 。expr 可以是任意数据类型。但是外层有 concat_ws 时必须 cast(expr as string)。
-- 合并为一个列表
select department
,collect_list(name) as name_list
from table_1
group by department; -- ['John','Jack']
-- concat_ws 按照指定分隔符连接列表的值
select department
,concat_ws(',', collect_list(name)) as name_list
from table_1
group by department; -- John,Jack
1.3 collect_set 函数
collect_set 函数用于将多个值收集到一个列表中，去重。语法格式：collect_set(expr) 。expr 可以是任意数据类型。但是外层有 concat_ws 时必须 cast(expr as string)。
-- 合并为一个列表
select department
,collect_set(name) as name_list -- 假设有三行数据为'John', 'Jack', 'John'
from table_1
group by department; -- ['John','Jack']
-- concat_ws 按照指定分隔符连接列表的值
select department
,concat_ws(',', collect_set(name)) as name_list
from table_1
group by department; -- John,Jack
2. 列方向的拆分
将一行的数据通过分隔符拆分成几行。 explode 函数
2.1 explode 函数
explode 函数用于打散行，将一行的数据拆分成一列。语法格式：explode(arr/map) 。explode 函数只接受 array 和 map 类型。如果要拆分 string 字符串类型，需要借助 split 函数把字段分割成一个数组。
-- 拆分 array 类型
select explode(array_col) as new_col from table_1; -- 拆分成一列多行
-- 拆分 map 类型（map 是 key-value 结构）
select explode(map_col) as (may_key_col, may_value_col) from table_1; -- 拆分成两列多行
-- 拆分 string 字符串类型
select explode(split(string_col, sep)) as new_col from table_name;
2.2 lateral view
explode 函数不能关联原有的表中的其他字段，无法将结果插回原表。
-- 错误示例
select other_col
,explode(array_col) as new_col
from table_1;
explode 函数不能与 group by、cluster by、distribute by、sort by 联用。select 后面只能获得一个 explode 函数产生的视图，如果还要获取其他列，则需要使用 lateral view 将多个视图合并。lateral view 用于和 UDTF 函数（explode、split）结合使用，主要解决在 select 使用 UDTF 做查询过程中，查询只能包含单个 UDTF，不能包含其他字段、以及多个 UDTF 的问题。首先通过 UDTF 函数拆分成多行，再将多行结果组合成一个支持别名的虚拟表，虚拟表相当于再和主表关联, 从而达到添加 UDTF 生成的字段以外字段的目的, 即主表里的字段或者主表运算后的字段。语法格式: lateral view UDTF(expression) table_view as new_column;
select source_column
,new_column
from table_1
lateral view explode(split(source_column, ',')) a as new_column; -- a是虚拟表名
精彩链接
您阅读本篇文章共花了：
发表评论