如何在BigQuery中对多列数组类型执行聚合操作

需求说明 #

已实现单列数组的聚合操作，现需扩展至两列及更多数组列：按name分组，对数组相同位置的元素求和，最终输出指定格式的JSON结果。

测试数据 #

WITH test_data AS (
  SELECT '2024-02-26 10:00:00' added, '2024-02-26 10:15:00' finished, [1,2,3,4] array1, [2,4,2,3] array2, 'testname1' name
  UNION ALL
  SELECT '2024-02-26 10:30:00' added, '2024-02-26 10:45:00' finished, [4,4,6,3] array1, [4,5,7,9] array2, 'testname1' name
  UNION ALL 
  SELECT '2024-02-26 10:45:00' added, '2024-02-26 11:00:00' finished, [2,4,5,2] array1, [8,7,4,3] array2, 'testname1' name
  UNION ALL
  SELECT '2024-02-26 10:00:00' added, '2024-02-26 10:15:00' finished, [2,3,1,1] array1, [2,1,1,3] array2, 'testname2' name
  UNION ALL
  SELECT '2024-02-26 10:30:00' added, '2024-02-26 10:45:00' finished, [6,6,6,6] array1, [2,1,1,3] array2, 'testname2' name
  UNION ALL
  SELECT '2024-02-26 10:45:00' added, '2024-02-26 11:00:00' finished, [2,4,7,6] array1, [3,2,5,3] array2, 'testname2' name
)

多列数组聚合SQL实现 #

WITH test_data AS (
  SELECT '2024-02-26 10:00:00' added, '2024-02-26 10:15:00' finished, [1,2,3,4] array1, [2,4,2,3] array2, 'testname1' name
  UNION ALL
  SELECT '2024-02-26 10:30:00' added, '2024-02-26 10:45:00' finished, [4,4,6,3] array1, [4,5,7,9] array2, 'testname1' name
  UNION ALL 
  SELECT '2024-02-26 10:45:00' added, '2024-02-26 11:00:00' finished, [2,4,5,2] array1, [8,7,4,3] array2, 'testname1' name
  UNION ALL
  SELECT '2024-02-26 10:00:00' added, '2024-02-26 10:15:00' finished, [2,3,1,1] array1, [2,1,1,3] array2, 'testname2' name
  UNION ALL
  SELECT '2024-02-26 10:30:00' added, '2024-02-26 10:45:00' finished, [6,6,6,6] array1, [2,1,1,3] array2, 'testname2' name
  UNION ALL
  SELECT '2024-02-26 10:45:00' added, '2024-02-26 11:00:00' finished, [2,4,7,6] array1, [3,2,5,3] array2, 'testname2' name
),
aggregated_by_index AS (
  SELECT
    name,
    idx,
    SUM(arr1_val) AS sum_arr1,
    SUM(arr2_val) AS sum_arr2,
    MIN(CAST(added AS TIMESTAMP)) AS min_added,
    MAX(CAST(finished AS TIMESTAMP)) AS max_finished
  FROM test_data,
    UNNEST(array1) AS arr1_val WITH OFFSET idx,
    UNNEST(array2) AS arr2_val WITH OFFSET idx
  WHERE added >= '2024-02-26 10:00:00' AND finished <= '2024-02-26 11:00:00'
  GROUP BY name, idx
)
SELECT
  FORMAT_JSON_ARRAY(
    ARRAY_AGG(
      TO_JSON_STRING(
        STRUCT(
          min_added AS added,
          max_finished AS finished,
          name,
          ARRAY_AGG(sum_arr1 ORDER BY idx) AS array1,
          ARRAY_AGG(sum_arr2 ORDER BY idx) AS array2
        )
      )
    )
  ) AS result_json
FROM aggregated_by_index
GROUP BY name, min_added, max_finished;

核心逻辑 #

• 用UNNEST同时展开所有需要聚合的数组，绑定同一个OFFSET idx，保证数组元素按位置一一对应
• 按name和idx分组，对每个位置的元素分别求和
• 外层按name聚合，把每个位置的求和结果按idx顺序重新拼接成数组
• 通过TO_JSON_STRING和FORMAT_JSON_ARRAY直接生成符合要求的JSON结构

预期输出 #

[{
  "added": "2024-02-26 10:00:00.000000 UTC",
  "finished": "2024-02-26 11:00:00.000000 UTC",
  "name": "testname1",
  "array1": ["7", "10", "14", "9"],
  "array2": ["14", "16", "13", "15"]
}, {
  "added": "2024-02-26 10:00:00.000000 UTC",
  "finished": "2024-02-26 11:00:00.000000 UTC",
  "name": "testname2",
  "array1": ["10", "13", "14", "13"],
  "array2": ["7", "4", "7", "9"]
}]

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