sql - hive collect_set 数组操作

Question

我正在处理大型数据集上的配置单元，我有一个包含列数组的表，列的内容如下。

["20190302Prod4"
"20190303Prod1"
"20190303Prod4"
"20190304Prod4"
"20190305Prod3"
"20190307Prod4"
"20190308Prod4"
"20190309Prod4"
"20190310Prod2"
"20190311Prod1"
"20190311Prod4"
"20190312Prod1"
"20190312Prod4"
"20190313Prod2"
"20190313Prod1"
"20190313Prod4"
"20190314Prod4"
"20190315Prod4"
"20190316Prod4"
"20190317Prod1"
"20190317Prod4"]

我需要根据产品的升序日期进行设置，例如我需要从 array 中修剪日期并应用 collect_set 以获得以下结果。

["Prod4",
"Prod1",
"Prod3",
"Prod2"]

Answer 1

分解数组，删除日期(字符串开头的数字)，使用collect_set聚合:

with mydata as (--use your table instead of this
select array(
"20190302Prod4",
"20190303Prod1",
"20190303Prod4",
"20190304Prod4",
"20190305Prod3",
"20190307Prod4",
"20190308Prod4",
"20190309Prod4",
"20190310Prod2",
"20190311Prod1",
"20190311Prod4",
"20190312Prod1",
"20190312Prod4",
"20190313Prod2",
"20190313Prod1",
"20190313Prod4",
"20190314Prod4",
"20190315Prod4",
"20190316Prod4",
"20190317Prod1",
"20190317Prod4"
) myarray
)

select collect_set(regexp_extract(elem,'^\\d*(.*?)$',1)) col_name 
  from mydata a --Use your table instead  
       lateral view outer explode(myarray) s as elem;

结果:

col_name    
["Prod4","Prod1","Prod3","Prod2"]   

另一种可能的方法是先连接数组，从字符串中删除日期，拆分得到一个数组。不幸的是，我们仍然需要展开以执行 collect_set 以删除重复项(示例使用相同的 WITH mydata CTE):

select collect_set(elem) col_name        
  from mydata a --Use your table instead 
       lateral view outer explode(split(regexp_replace(concat_ws(',',myarray),'(^|,)\\d{8}','$1'),',')) s as elem
;