如何批量将两个数组对应位置元素分组合并（SQL实现）

需要将array1与array2对应位置的元素分组合并，由于数组长度较长，手动逐个构造子数组的方式（如下）效率极低且不合理，求更优实现方法：

ARRAY_CONSTRUCT(
      ARRAY_CONSTRUCT(array1[1], array2[1]),
      ARRAY_CONSTRUCT(array1[2], array2[2]),
      ARRAY_CONSTRUCT(array1[3], array2[3])
      ...
    )

示例数据 #

CREATE OR REPLACE TEMPORARY TABLE sample_table AS (
  SELECT
    TO_DATE('2022-01-01', 'YYYY-MM-DD') AS date_column,
    ARRAY_CONSTRUCT(1, 2, 3) AS array1,
    ARRAY_CONSTRUCT(10, 20, 30) AS array2
  UNION
  SELECT
    TO_DATE('2022-01-02', 'YYYY-MM-DD') AS date_column,
    ARRAY_CONSTRUCT(4, 5) AS array1,
    ARRAY_CONSTRUCT(30, 40) AS array2
  UNION
  SELECT
    TO_DATE('2022-01-03', 'YYYY-MM-DD') AS date_column,
    ARRAY_CONSTRUCT(6, 7, 8, 9) AS array1,
    ARRAY_CONSTRUCT(60, 70, 80, 90) AS array2
);

期望结果 #

CREATE OR REPLACE TEMPORARY TABLE sample_table_2 AS (
  SELECT
    TO_DATE('2022-01-01', 'YYYY-MM-DD') AS date_column,
    ARRAY_CONSTRUCT(1, 2, 3) AS array1,
    ARRAY_CONSTRUCT(10, 20, 30) AS array2,
    ARRAY_CONSTRUCT([1,10],[2,20],[3,30]) AS array3
  UNION
  SELECT
    TO_DATE('2022-01-02', 'YYYY-MM-DD') AS date_column,
    ARRAY_CONSTRUCT(4, 5) AS array1,
    ARRAY_CONSTRUCT(30, 40) AS array2,
    ARRAY_CONSTRUCT([4,30],[5,40]) AS array3
  UNION
  SELECT
    TO_DATE('2022-01-03', 'YYYY-MM-DD') AS date_column,
    ARRAY_CONSTRUCT(6, 7, 8, 9) AS array1,
    ARRAY_CONSTRUCT(60, 70, 80, 90) AS array2,
    ARRAY_CONSTRUCT([6,60],[7,70],[8,80],[9,90]) AS array3
);

利用Snowflake的FLATTEN函数展开数组并保留元素索引，通过索引关联两个数组的对应元素，最后用ARRAY_AGG聚合得到目标数组，具体SQL如下：

SELECT
  date_column,
  array1,
  array2,
  ARRAY_AGG(ARRAY_CONSTRUCT(f1.value, f2.value)) WITHIN GROUP (ORDER BY f1.index) AS array3
FROM sample_table
LEFT JOIN LATERAL FLATTEN(input => array1, outer => true) f1
LEFT JOIN LATERAL FLATTEN(input => array2, outer => true) f2
  ON f1.index = f2.index
GROUP BY date_column, array1, array2;

核心逻辑说明 #

1. FLATTEN函数将数组展开为行记录，同时保留每个元素在原数组中的索引位置
2. 通过索引字段关联两个展开后的结果，确保同一位置的元素配对
3. 用ARRAY_CONSTRUCT将配对元素组成子数组，再通过ARRAY_AGG按索引排序聚合，还原为目标数组
4. outer => true参数可兼容两个数组长度不一致的场景，避免丢失元素

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