在BigQuery中基于增删事件行构建数组的技术方案问询

嘿，我完全懂你的需求——给BigQuery表的每一行加一个数组列，用来存储截至当前行的无重复值状态集合，而且因为BigQuery不支持递归CTE，之前的思路卡壳了对吧？别慌，咱们用窗口函数结合数组操作就能完美解决这个问题！

先明确场景示例 #

假设你的原始表结构是这样的（我用row_num来标记行的顺序，你可以换成自己的排序字段，比如时间戳）：

CREATE TABLE your_table (
  row_num INT64,
  value STRING
);

-- 插入测试数据
INSERT INTO your_table VALUES
(1, 'a'),
(2, 'b'),
(3, 'a'),
(4, 'c'),
(5, 'b');

你想要的预期结果是：

解决方案SQL #

直接用窗口函数+嵌套数组去重的方式创建视图：

CREATE OR REPLACE VIEW your_target_view AS
SELECT
  *,
  -- 核心逻辑：先聚合当前行及之前的所有值，再去重排序
  ARRAY(
    SELECT DISTINCT val
    FROM UNNEST(ARRAY_AGG(value OVER (ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))) AS val
    ORDER BY val -- 可选：如果需要固定数组内的顺序
  ) AS current_unique_array
FROM your_table
ORDER BY row_num;

逻辑拆解 #

1. ARRAY_AGG(...) OVER (...)：这一步会把从第一行到当前行的所有value聚合进一个数组，此时数组里会包含重复值（比如第三行的数组会是['a','b','a']）；
2. UNNEST + SELECT DISTINCT：把聚合后的数组拆成单行，去重后再重新组合成数组，就得到了截至当前行的无重复状态集合；
3. ORDER BY val：可选步骤，用来固定数组内元素的顺序，避免因为BigQuery的无序性导致数组内容顺序波动。

扩展：按分组维护状态 #

如果你的表需要按某个字段（比如group_id）分组，每个分组独立维护自己的无重复数组状态，只需要给窗口函数加上PARTITION BY即可：

CREATE OR REPLACE VIEW your_grouped_view AS
SELECT
  *,
  ARRAY(
    SELECT DISTINCT val
    FROM UNNEST(ARRAY_AGG(value OVER (PARTITION BY group_id ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))) AS val
    ORDER BY val
  ) AS current_unique_array
FROM your_table
ORDER BY group_id, row_num;

注意事项 #

如果你的原始表没有明确的排序字段（比如row_num或时间戳），可以先用ROW_NUMBER()生成一个顺序标识：

WITH ordered_table AS (
  SELECT
    *,
    ROW_NUMBER() OVER (ORDER BY your_sort_column) AS row_num -- 替换成你的排序字段，比如create_time
  FROM your_table
)
SELECT
  *,
  ARRAY(
    SELECT DISTINCT val
    FROM UNNEST(ARRAY_AGG(value OVER (ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))) AS val
    ORDER BY val
  ) AS current_unique_array
FROM ordered_table
ORDER BY row_num;

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