如何用SQL或BigQuery SQL实现多列数组数据聚合去重合并？

假设你使用的是支持UNNEST函数的数据库（比如PostgreSQL），可以通过以下步骤实现需求：

• 先将每一列的数组拆分为单个元素的行
• 合并所有列的拆分结果，去除重复元素
• 将去重后的元素重新聚合为单个数组

示例SQL代码 #

假设你的表名为target_table，包含多个数组列（比如array_col1、array_col2、array_col3），执行以下语句即可得到合并后的单行去重数组：

SELECT ARRAY_AGG(DISTINCT elem ORDER BY elem) AS merged_unique_array
FROM (
    -- 拆分第一列数组
    SELECT unnest(array_col1) AS elem FROM target_table
    -- 合并第二列数组的拆分结果
    UNION ALL
    SELECT unnest(array_col2) AS elem FROM target_table
    -- 如果有更多数组列，继续添加UNION ALL和对应的拆分语句
    UNION ALL
    SELECT unnest(array_col3) AS elem FROM target_table
) AS all_elements;

关键说明 #

• UNION ALL用于合并所有列拆分后的元素行，避免遗漏任何数据
• DISTINCT在聚合时自动去除重复元素
• ORDER BY elem可选，用于让最终数组的元素按顺序排列

内容的提问来源于stack exchange，提问作者Hybrid