请考虑下面提到的配置单元表。
user_id interest_array
tom [a,b,c,d,g,w]
bob [e,d,s,d,g,w,s]
cat [a]
harry []
peter NULL
我想按顺序选择每行"interest_array"中的前3个元素,并将其作为数组返回,输出结果如下
user_id output_array
tom [a,b,c]
bob [e,d,s]
cat [a]
harry []
peter NULL
PS:最后两行并不重要,它们只是角大小写,如果需要,我可以将它们设置为空。
1。简单的方法,但如果初始数组可以包含较少的元素(结果数组将包含NULL(,它将无法正常工作。
with mydata as(
select array('a','b','c','d','g','w') as original_array
)
select original_array, array(original_array[0], original_array[1], original_array[2]) as first_3_array
from mydata
结果:
original_array first_3_array
["a","b","c","d","g","w"] ["a","b","c"]
2.还有一种使用爆炸的方法,适用于任何阵列:
使用posexplode分解数组,过滤器位置<2、再次采集阵列:
with mydata as(
select array('a','b','c','d','g','w') as original_array
)
select original_array, collect_list(e.element) as first_3_array
from mydata
lateral view outer posexplode(original_array) e as pos, element
where pos<=2
group by original_array
结果:
original_array first_3_array
["a","b","c","d","g","w"] ["a","b","c"]
3.更有效的方法,无需爆炸:用逗号分隔符连接数组,使用regexp提取最多包含3个第一元素的子字符串,再次拆分:
with mydata as(
select array('a') as original_array
)
select original_array, split(regexp_replace(regexp_extract(concat_ws(',', original_array),
'^(([^,]*,?){1,3})',1),
',$','') --remove last delimiter
,',') as first_3_array
from mydata