【SQL相关】Hive行列字符串的合并与拆分

一、行方向

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是虚拟表名