1、原来数据
label_value 字段类型为 array[bigint]
2、目标数据
label_value 字段类型为 string
3、代码
select
concat('[',label_value,']') as label_value
,user_id,device_id,dt,label_name
from (
select
concat_ws(',',collect_list(label_values)) as label_value,
user_id,device_id,dt,label_name
from (
select
label_name,
cast(label_values as string) as label_values,
user_id,
device_id,
dt
from new_ads.ads_user_label_array
LATERAL VIEW explode(label_value) adTable AS label_values
) o2
group by label_name,user_id,device_id,dt
) o3
用现有函数array_join ,array转string
SELECT label_name
,array_join(label_value,',') as label_value
,user_id
,device_id
,dt
FROM new_ads.ads_user_label_array
;