如何在BigQuery中从数组提取所有重叠有序三元组

需求说明 #

输入表格（elems列）：

elems
['a', 'b', 'c', 'd', 'e']
['v', 'w', 'x', 'y']

需要转换为输出表格（tuple列），提取每个数组中所有重叠的三元组：

tuple
['a', 'b', 'c']
['b', 'c', 'd']
['c', 'd', 'e']
['v', 'w', 'x']
['w', 'x', 'y']

现有方案的问题 #

当前尝试的SQL语句如下：

WITH foo AS (
  SELECT ['a', 'b', 'c', 'd', 'e'] AS elems UNION ALL
  SELECT ['v', 'w', 'x', 'y']),
single AS (
  SELECT * FROM
    foo,
    UNNEST(elems) elem
),
tuples AS (
  SELECT ARRAY_AGG(elem) OVER (ROWS BETWEEN 2 PRECEDING AND 0 FOLLOWING) AS tuple
  FROM single
)
SELECT * FROM tuples
WHERE ARRAY_LENGTH(tuple) >= 3

该方案存在两个核心问题：

• 生成跨原数组的无效行：比如['d', 'e', 'v']、['e', 'v', 'w']，原因是窗口函数没有按原数组分组，会跨数组聚合元素。
• 行序无保证：single表中元素的顺序仅在小示例中偶然一致，多数SQL引擎中，不带索引的UNNEST无法保证元素顺序和原数组完全匹配。

优化解决方案 #

要解决上述问题，需同时保证元素顺序、限制聚合范围在原数组内，以下提供两种通用写法：

写法1：分组聚合（适合多数SQL引擎） #

WITH foo AS (
  SELECT ['a', 'b', 'c', 'd', 'e'] AS elems UNION ALL
  SELECT ['v', 'w', 'x', 'y']
),
indexed_elements AS (
  SELECT 
    elems,
    elem,
    -- 用WITH ORDINALITY获取元素在原数组中的位置索引（PostgreSQL、BigQuery等支持）
    ordinality AS idx
  FROM foo, UNNEST(elems) WITH ORDINALITY AS t(elem, ordinality)
)
SELECT 
  ARRAY_AGG(elem ORDER BY idx) AS tuple
FROM indexed_elements
GROUP BY elems, idx - 2  -- 按三元组的起始位置分组（idx-2相同的为同一组）
HAVING COUNT(elem) = 3   -- 只保留包含3个元素的完整三元组

写法2：窗口函数（更直观） #

WITH foo AS (
  SELECT ['a', 'b', 'c', 'd', 'e'] AS elems UNION ALL
  SELECT ['v', 'w', 'x', 'y']
),
indexed_elements AS (
  SELECT 
    elems,
    elem,
    ordinality AS idx
  FROM foo, UNNEST(elems) WITH ORDINALITY AS t(elem, ordinality)
)
SELECT 
  ARRAY_AGG(elem ORDER BY idx) OVER (
    PARTITION BY elems 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS tuple
FROM indexed_elements
WHERE idx >= 3  -- 从第3个元素开始，确保窗口内有完整的3个元素

关键优化点 #

• 保证元素顺序：通过WITH ORDINALITY（或对应引擎的索引生成方式）获取元素在原数组中的位置，强制UNNEST后的顺序与原数组一致。
• 限制聚合范围：窗口函数添加PARTITION BY elems，让聚合操作仅在同一个原数组内执行，彻底避免跨数组的无效三元组。

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