如何在BigQuery中计算所有行的数组交集？

输入数据 #

期望输出 #

需求是找出所有行数组的公共元素，也就是计算多个数组的交集，以下是具体实现思路和示例：

通用核心逻辑 #

1. 以第一个数组为基础：把第一个数组转成集合（集合的查找和交集操作效率远高于数组），作为初始的交集结果。
2. 逐个合并交集：遍历剩下的每一个数组，转成集合后和当前的交集结果求交集，更新结果。如果中途交集变成空，可以直接终止循环，避免无用计算。
3. 格式转换：最后把最终的集合转回数组格式，得到目标结果。

Python 实现示例 #

# 模拟从表格中读取的数组数据
arrays = [[1,2,3], [1,2], [2,3]]

if not arrays:
    result = []
else:
    # 初始化交集为第一个数组的集合
    intersection = set(arrays[0])
    for arr in arrays[1:]:
        # 直接在原集合上更新交集
        intersection.intersection_update(set(arr))
        # 交集为空就提前结束
        if not intersection:
            break
    result = list(intersection)

print(result)  # 输出：[2]

JavaScript 实现示例 #

// 模拟输入数据
const arrays = [[1,2,3], [1,2], [2,3]];

let intersection = new Set(arrays[0]);
for (const arr of arrays.slice(1)) {
    const currentSet = new Set(arr);
    // 过滤出同时存在于两个集合的元素
    intersection = new Set([...intersection].filter(item => currentSet.has(item)));
    if (intersection.size === 0) break;
}

const result = Array.from(intersection);
console.log(result);  // 输出：[2]

数据库（PostgreSQL）实现示例 #

如果是在数据库中处理数组字段，可以用SQL实现：

WITH exploded_elements AS (
    -- 把每行的数组拆分成单个元素
    SELECT unnest(col1) AS num
    FROM your_table_name
)
-- 统计每个元素出现的次数，次数等于总行数的就是所有数组的公共元素
SELECT array_agg(num) AS ans
FROM exploded_elements
GROUP BY num
HAVING COUNT(*) = (SELECT COUNT(*) FROM your_table_name);

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