我有一个数组列，我想获取N它的第一个元素（保持数组数据类型）。有一些不错的方法吗？理想情况下，无需取消嵌套，将array_agg排序并返回到array。
我也可以这样做（获取前两个元素）：
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个元素，它将失败。
这是带有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函数，听起来好像您不想使用它，但是它具有足够通用的优点，可以将任何数组传递给它。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句