说一说行列变换的latetal view、explode、collect_set、concat_ws等函数用法

更多文章，可关注微信公众号：excelwork

   “ 我们在使用SQL查询的数据，经常会遇到数据存到同一个字段下，而我们分析数据需要转成多行或多列等，这种问题就需要用到下面用到的函数了。既然写到这个，索性就细致介绍下。”

    比如我们拿到了这样一份数据，把数据放在表table中：

 

01 行拆分成多行

1.1 sale_district字段中的内容拆分成多行显示：explode

• explode支持对map中每个键值对或array类型中每个元素生成各生成一行；

• 不支持group by、不支持嵌套

select sku,sale_city,explode(split(sale_district,',')) from  table  t

结果如下：

1.2 lateral view outer explode避免空值默认不输出

    不过我们会发现，拆分的字段为空时，对应的行数据出现丢失，如果解决这个问题，我们这时用到lateral view outer explode组合。

• 当使用lateral view时，where条件要放在后面；

• 如果需要拆分两个字段，则连着写两个lateral 结构即可。

select sku,sale_city,sale_districtsfrom table  t LATERAL view  outer explode(split(sale_district,',')) t as sale_districts

结果如下：

1.3 posexplode函数输出元素在数组中所在的位置

select sku,sale_city,posexplode(split(sale_district,',')) from  table t

结果如下：

 

02 多行转一行

2.1 多行按其他字段汇总到一行：collect_set

    意思就是，把类似上面的结果汇总到最开始的数据样式。

• 支持切片，比如collect_set(sale_districts)[0]取出合并后字段的第一项等。

select sku,sale_city,collect_set(sale_districts)from(select sku,sale_city,sale_districts  from table t   LATERAL view outer explode(split(sale_district,',')) t as sale_districts)group by sku,sale_city

结果如下：

    结果为数组类型，我们继续通过concat_ws函数，将数据样式合并成最开始的样式。

select sku,sale_city,concat_ws(',',collect_set(sale_districts))from(select sku,sale_city,sale_districts  from table t   LATERAL view outer explode(split(sale_district,',')) t as sale_districts)group by sku,sale_city

本例使用的collect_set有去重功能，而使用collect_list则允许重复项存在。