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

目录

一、行方向

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'); -- nullselect 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'); -- nullselect 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 namefrom 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 coursesfrom table_1group by name; -- Math,English-- 指定分隔符合并select name      ,group_concat(course, '_') as coursesfrom table_1group 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_1group by department; -- ['John','Jack']-- concat_ws 按照指定分隔符连接列表的值select department      ,concat_ws(',', collect_list(name)) as name_listfrom table_1group 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_1group by department; -- ['John','Jack']-- concat_ws 按照指定分隔符连接列表的值select department      ,concat_ws(',', collect_set(name)) as name_listfrom table_1group 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是虚拟表名