求助：BigQuery中仅移除数组相邻重复元素的实现方案

要实现只移除数组中相邻重复元素的需求，可以借助UNNEST、OFFSET()和窗口函数完成，核心思路是追踪元素位置并对比当前元素与前一个元素，过滤重复项后重新聚合数组。

示例实现 #

单条数据测试

WITH sample_data AS (
  SELECT ['a', 'a', 'b', 'a', 'a', 'c', 'b', 'b'] AS arr
)
WITH unnested AS (
  SELECT
    element,
    offset,
    LAG(element) OVER (ORDER BY offset) AS prev_element
  FROM sample_data,
  UNNEST(arr) AS element WITH OFFSET AS offset
)
SELECT
  ARRAY_AGG(element ORDER BY offset) AS deduplicated_arr
FROM unnested
WHERE prev_element IS NULL OR element != prev_element

执行后会输出期望结果：['a', 'b', 'a', 'c', 'b']

批量处理表数据

如果数据存储在表中（比如表名为your_table，包含id主键和arr数组字段），可调整为：

WITH unnested_data AS (
  SELECT
    id,
    element,
    offset,
    LAG(element) OVER (PARTITION BY id ORDER BY offset) AS prev_element
  FROM your_table,
  UNNEST(arr) AS element WITH OFFSET AS offset
)
SELECT
  id,
  ARRAY_AGG(element ORDER BY offset) AS deduplicated_arr
FROM unnested_data
WHERE prev_element IS NULL OR element != prev_element
GROUP BY id

逻辑说明 #

1. UNNEST(arr) WITH OFFSET AS offset：将数组拆分为单个元素，同时保留每个元素在原数组中的位置索引
2. LAG(element) OVER (...)：通过窗口函数获取当前元素的前一个元素（严格遵循原数组顺序）
3. 过滤条件：保留第一个元素（prev_element IS NULL对应数组的起始元素），以及与前一个元素不相同的元素
4. ARRAY_AGG(element ORDER BY offset)：按原数组顺序重新聚合过滤后的元素，得到去重后的目标数组

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