从BigQuery表中获取数组的前N个元素

Question

我有一个数组列，并且我想要获取其中的前N个元素（保留数组数据类型）。有没有某种好的方法来实现这一点？最好不用将其展开、排名和再次聚合成数组。

我也可以使用以下方法来获取前两个元素：

WITH data AS
(
  SELECT 1001 as id, ['a', 'b', 'c'] as array_1
  UNION ALL
  SELECT 1002 as id, ['d', 'e', 'f', 'g'] as array_1
  UNION ALL
  SELECT 1003 as id, ['h', 'i'] as array_1
)
select *,
       [array_1[SAFE_OFFSET(0)], array_1[SAFE_OFFSET(1)]] as my_result
from data

但是很明显这不是一个好的解决方案，因为当某个数组只有1个元素时，它会失败。

- matt525252

---

你可以使用 ARRAY_LENGTH(array_1)。这将给出数组的长度。 - Sebastian Siemens

Answer 1

下面是一个通用解决方案，使用用户自定义函数(UDF)来处理任何类型的数组：

CREATE TEMP FUNCTION TopN(arr ANY TYPE, n INT64) AS (
  ARRAY(SELECT x FROM UNNEST(arr) AS x WITH OFFSET off WHERE off < n ORDER BY off)
);

WITH data AS
(
  SELECT 1001 as id, ['a', 'b', 'c'] as array_1
  UNION ALL
  SELECT 1002 as id, ['d', 'e', 'f', 'g'] as array_1
  UNION ALL
  SELECT 1003 as id, ['h', 'i'] as array_1
)
select *, TopN(array_1, 2) AS my_result
from data

它使用了unnest和array函数，听起来您可能不想使用它们，但它具有足够的通用性，可以将任何数组传递给它。

- Elliott Brossard

---

我必须说，我希望这可以在没有UDF的情况下完成，但这肯定有效。 - matt525252

Answer 2

BigQuery标准SQL（带JS UDF）的另一个选择

#standardSQL
CREATE TEMP FUNCTION FirstN(arr ARRAY<STRING>, N FLOAT64)
RETURNS ARRAY<STRING> LANGUAGE js AS """ 
  return arr.slice(0, N);
""";
SELECT *, 
  FirstN(array_1, 3) AS my_result
FROM data   

- Mikhail Berlyant

---

也可以，谢谢提示！我选择了另一个答案作为被接受的答案，因为它不需要 JavaScript，并且完全保持在 SQL 中。 - matt525252