SQL :字符串合并拆分与行列转换：concat_ws()、LATERAL view explode、collect_set()|collect_list()函数

目录

1、字符串合并：concat()和concat_ws()使用对比

1.1、不带分隔符时，两种写法结果一致

1.2、需要加分隔符时，concat_ws的写法较为简单

1.3、存在空值时

2、列转行：一列拆分多行

2.1 ：将字符串拆分为多行记录

2.2、存在NULL值时的处理方法

 3、列转行

hiveSQL实现列转行步骤拆解（对比是否去重|是否排序的不同写法）

---

1、字符串合并：concat()和concat_ws()使用对比

1.1、不带分隔符时，两种写法结果一致

select concat('11','22','33')--112233
select concat_ws('11','22','33') --112233

1.2、需要加分隔符时，concat_ws的写法较为简单

--用concat连接多个字符串，如果需要,分隔的话，需要在每两个字段之间加上","
select concat('11',',','22',',','33')--11,22,33
--用concat_ws连接多个字符串，可以将分隔符写在最前面
select concat_ws(',','11','22','33') --11,22,33

1.3、存在空值时

--如果链接的字符串中有null,则concat关联结果也是null
select concat('11','22',NULL)--NULL
--如果字符串中有null,concat_ws的结果会忽略null
select concat_ws(',','11','22',NULL)--11,22

2、列转行：一列拆分多行

2.1 ：将字符串拆分为多行记录

原数据格式，secondary_work_cates列是一个字符串类型数据，存储了一些编码文本组成的字符串（以，分隔），

*注意：secondary_work_cates这里存储的数据格式比较乱，空值有的存储成NULL，有的存储为空

aunt_id	work_cate	secondary_work_cates
5578964785788592129	13332	NULL
5535555969383649280	13329
5601885974332952576	13329	13330,13331
5632206525702086656	13333	13334

想得到如下结果

aunt_id	work_cate	secondary_work_cates
5578964785788592129	13332
5535555969383649280	13329
5601885974332952576	13329	13330
5601885974332952576	13329	13331
5632206525702086656	13333	13334

把secondary_work_cates列拆开为多行，有如下两种写法

select 
aunt_id
,work_cate
,secondary_work_cates
,explode(split(secondary_work_cates,','))
from table_name

SELECT
aunt_id
,work_cate
,secondary_work_cates
,second_cate_split
from 
(select 
aunt_id
,work_cate
,secondary_work_cates
from table_name
) as a LATERAL view explode(split(secondary_work_cates,',')) new_table as second_cate_split

以下两种写法，分别在hive SQL引擎下和Spark SQL引擎下的结果

SQL引擎	语法	结果
hive	explode()	报错 Error：SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions
hive	lateral view explode()	运行成功
spark	explode()	运行成功
spark	lateral view explode()	运行成功

得到结果如下

2.2、存在NULL值时的处理方法

虽然运行成功，但是两种写法下，记录中存储NULL的行数据都被自动过滤掉了，所以为了保证数据不丢失，需要把NULL先转换成空值再进行处理

使用explode时

select 
aunt_id
,work_cate
,explode(split(secondary_work_cates,','))
from 
	(select 
    aunt_id
    ,work_cate
    ,case when secondary_work_cates is NULL then '' else secondary_work_cates end as secondary_work_cates
	from table_name)

使用lateral view explode时

SELECT
aunt_id
,work_cate
,secondary_work_cates
,second_cate_split
from 
(select 
aunt_id
,work_cate
,case when secondary_work_cates is NULL then '' else secondary_work_cates end as secondary_work_cates
from table_name
) as a LATERAL view explode(split(secondary_work_cates,',')) new_table as second_cate_split

对比之前的数据结果，可以发现记录为NULL的数据才出现

 3、列转行

原数据记录了用户每日下单记录

现在想将其转换为

要想实现上面的效果，MySQL和HiveSQL的语法是完全不一样的 ，这里把两种方法都列下来。不过日常使用中，Hive SQL的使用比较常见，这里重点学习，MySQL稍作了解即可：

MySQL

--mySQL函数
SELECT cuser_id,
--取客户所有的下单日期，如：20220101;20220103;20220102;20220101
group_concat(date SEPARATOR ';') as r1,
--取客户所有的下单日期去重，正序排列，如：20220101;20220102;20220103
group_concat(distinct date order by asc date SEPARATOR ';') as r2
FROM 
  (SELECT cuser_id,pay_order_id,date
  FROM table_name 
  where statdate = '${#date(0,0,-1):yyyyMMdd#}'
  and date>'20210801'
  )a
group by cuser_id

HiveSQL 

--HiveSQL函数
SELECT
cuser_id,
concat_ws(',',collect_set(date)),--去重
concat_ws(',',collect_list(date)),--不去重
concat_ws(',',collect_list(cast(rn as string)))--排序

from 
(select cuser_id,date,
 ROW_NUMBER()over(partition by cuser_id order by date asc) as rn--不加这段不会排序
from hdp_lbg_data_mart.case1103)a
group by cuser_id

hiveSQL实现列转行步骤拆解（对比是否去重|是否排序的不同写法）

1、先使用窗口函数得到日期的排序，如果不需要排序，此步骤可以省略

2、按cuser_id分组，使用collect_set(列字段名)或者collect_list得到每个用户有消费记录的日期，这里是否排序和去重有三种写法

去重：collect_set(列名)

不去重：collect_list(列名)   

排序：collect_list(cast(排序字段 as string))

汇总方式	写法	结果
去重	concat_ws(',',collect_set(date))	20220101,20220103,20220102
不去重	concat_ws(',',collect_list(date))	20220101,20220103,20220102,20220101
排序去重	concat_ws(',',collect_set(cast(rn as string))) 结合窗口函数使用 ROW_NUMBER()over(partition by cuser_id order by date asc)	20220101,20220102,20220103 1,2,3
排序不去重	concat_ws(',',collect_list(cast(rn as string))) 结合窗口函数使用 ROW_NUMBER()over(partition by cuser_id order by date asc)	20220101,20220101,20220102,20220103 1,2,3,4

3、使用concat_ws()函数将其组合为一个字符串，日期之间用","分割