BigQuery中对游程编码数组执行数组聚合的实现方案

针对你的需求，这里提供一个支持任意数量负数的最优解码方案，能将RLE格式的数组展开为完整的零填充数组，之后可直接用于ARRAY_AGG等聚合操作。

核心SQL实现 #

假设你的表名为your_table，RLE格式的目标列为rle_array，其他列为col1、col2_to_col10，解码SQL如下：

WITH expanded_elements AS (
  SELECT
    col1,
    col2_to_col10,
    -- 对每个RLE元素进行展开：负数转对应长度的零数组，正数保留为单元素数组
    CASE
      WHEN x < 0 THEN ARRAY(SELECT 0 FROM UNNEST(GENERATE_ARRAY(1, -x)))
      ELSE [x]
    END AS expanded_part,
    -- 记录原数组中的元素位置，确保展开后顺序不混乱
    offset
  FROM your_table,
       UNNEST(rle_array) x WITH OFFSET
)
SELECT
  col1,
  col2_to_col10,
  -- 按原顺序合并所有展开的数组段，得到最终解码数组
  ARRAY_CONCAT_AGG(expanded_part ORDER BY offset) AS decoded_array
FROM expanded_elements
GROUP BY col1, col2_to_col10;

逻辑说明 #

1. 拆分RLE数组：通过UNNEST(rle_array) x WITH OFFSET将原数组拆分为单个元素，同时保留每个元素在原数组中的位置，保证前导零和元素顺序的准确性。
2. 生成展开段：
   • 若元素为负数，用GENERATE_ARRAY(1, -x)生成对应长度的序列，再将每个元素替换为0，得到指定长度的零数组。
   • 若元素为正数，直接生成仅包含该正数的单元素数组。
3. 合并数组：使用ARRAY_CONCAT_AGG按原元素位置排序，将所有展开的数组合并为完整的解码数组。

示例验证 #

对于输入RLE数组[-4, 3, -2, 1, -3, 9]，解码后会得到：
[0,0,0,0,3,0,0,1,0,0,0,9]，完全符合你的需求。

后续聚合示例 #

如果需要对解码后的数组进行ARRAY_AGG聚合（比如按col1分组求和），可基于上述结果继续处理：

WITH expanded_elements AS (
  SELECT
    col1,
    CASE
      WHEN x < 0 THEN ARRAY(SELECT 0 FROM UNNEST(GENERATE_ARRAY(1, -x)))
      ELSE [x]
    END AS expanded_part,
    offset
  FROM your_table,
       UNNEST(rle_array) x WITH OFFSET
),
decoded_arrays AS (
  SELECT
    col1,
    ARRAY_CONCAT_AGG(expanded_part ORDER BY offset) AS decoded_array
  FROM expanded_elements
  GROUP BY col1
)
SELECT
  col1,
  -- 按数组位置聚合求和
  ARRAY_AGG(SUM(val) ORDER BY pos) AS aggregated_array
FROM decoded_arrays,
     UNNEST(decoded_array) val WITH OFFSET pos
GROUP BY col1;

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