BigQuery中合并两数组列并保留唯一值的实现方法

需求：现有查询输出包含cId、dId、N1、N2列，需新增N3列，将N1和N2两个数组列的元素去重后合并为一个新数组。

原查询语句：

SELECT  rd.cId  
        , rd.dId 
        ,ARRAY_AGG(ei.IncentiveName IGNORE NULLS) AS N1
        ,ARRAY_AGG(ai.IncentiveName IGNORE NULLS) AS N2

FROM rd
join ei on...
join ai on ...
GROUP BY rd.cId, rd.dId

修改后的查询语句（适用于支持数组操作的SQL方言，如BigQuery、PostgreSQL）：

SELECT  rd.cId  
        , rd.dId 
        ,ARRAY_AGG(ei.IncentiveName IGNORE NULLS) AS N1
        ,ARRAY_AGG(ai.IncentiveName IGNORE NULLS) AS N2
        -- 合并N1和N2并去重得到N3
        ,ARRAY(SELECT DISTINCT UNNEST(ARRAY_CONCAT(N1, N2))) AS N3

FROM rd
join ei on...
join ai on ...
GROUP BY rd.cId, rd.dId

关键逻辑说明 #

• ARRAY_CONCAT(N1, N2)：将N1和N2两个数组合并成一个包含所有元素的临时数组
• UNNEST(...)：把数组拆分成单行的元素集合
• SELECT DISTINCT ...：对拆分后的元素去重
• ARRAY(...)：将去重后的单行元素重新组合成数组，即得到N3

内容的提问来源于stack exchange，提问作者Zoey.L