如何在BigQuery中比较两行REPEATED(String)列并找出元素增减？

由于BigQuery不支持ARRAY_DIFF、ARRAY_INTERSECT这类数组差集/交集函数，我们可以通过拆分数组成单行元素，再用关系型查询的方式实现对比需求。

基础数据准备 #

首先定义你的事件表数据：

WITH event_data AS (
  SELECT 222 as event_id, ["200", "300"] as arr, '2023-01-02' as date_string
  UNION ALL 
  SELECT 111 as event_id, ["100", "200"] as arr, '2023-01-01' as date_string
)

方案一：使用NOT IN子查询对比 #

通过UNNEST将数组拆分为单行元素，再用NOT IN筛选出两边独有的元素：

WITH event_data AS (
  SELECT 222 as event_id, ["200", "300"] as arr, '2023-01-02' as date_string
  UNION ALL 
  SELECT 111 as event_id, ["100", "200"] as arr, '2023-01-01' as date_string
),
latest_elements AS (
  -- 拆分最新事件（event_id=222）的数组元素，DISTINCT避免重复元素干扰
  SELECT DISTINCT element FROM event_data, UNNEST(arr) AS element WHERE event_id = 222
),
old_elements AS (
  -- 拆分旧事件（event_id=111）的数组元素
  SELECT DISTINCT element FROM event_data, UNNEST(arr) AS element WHERE event_id = 111
)
-- 找出新增元素：最新数组有，旧数组无
SELECT '新增' AS change_type, element FROM latest_elements 
WHERE element NOT IN (SELECT element FROM old_elements)
UNION ALL
-- 找出删除元素：旧数组有，最新数组无
SELECT '删除' AS change_type, element FROM old_elements 
WHERE element NOT IN (SELECT element FROM latest_elements)

方案二：使用LEFT JOIN + IS NULL对比 #

另一种更高效的方式是用左连接，通过判断NULL值来筛选独有的元素：

WITH event_data AS (
  SELECT 222 as event_id, ["200", "300"] as arr, '2023-01-02' as date_string
  UNION ALL 
  SELECT 111 as event_id, ["100", "200"] as arr, '2023-01-01' as date_string
),
latest_elements AS (
  SELECT DISTINCT element FROM event_data, UNNEST(arr) AS element WHERE event_id = 222
),
old_elements AS (
  SELECT DISTINCT element FROM event_data, UNNEST(arr) AS element WHERE event_id = 111
)
-- 新增元素：左连接旧元素后无匹配
SELECT '新增' AS change_type, le.element
FROM latest_elements le
LEFT JOIN old_elements oe ON le.element = oe.element
WHERE oe.element IS NULL
UNION ALL
-- 删除元素：左连接新元素后无匹配
SELECT '删除' AS change_type, oe.element
FROM old_elements oe
LEFT JOIN latest_elements le ON oe.element = le.element
WHERE le.element IS NULL

结果说明 #

两种方案都会输出如下结果：

注意事项 #

• 如果数组中存在重复元素，一定要添加DISTINCT去重，避免结果出现重复行。
• 若需要对比的事件不是固定ID，可通过date_string排序获取最新和前序事件，比如用ROW_NUMBER()标记事件顺序后再关联对比。

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