google-bigquery - Bigquery STRING 数组到 INT 数组

Question

我正在尝试提取 INT64 的数组s 在 BigQuery来自一列的标准 SQL，该列是由逗号分隔的一长串数字(例如， 2013,1625,1297,7634 )。我可以通过以下方式轻松拉出一系列字符串:

SELECT
  SPLIT(string_col,",")
FROM
  table

但是，我想返回一个数组 INT64 s，不是字符串数组。我怎样才能做到这一点？我试过了

CAST(SPLIT(string_col,",") AS ARRAY<INT64>) 

但这不起作用。

Answer 1

下面是 BigQuery 标准 SQL

#standardSQL
WITH yourTable AS (
  SELECT 1 AS id, '2013,1625,1297,7634' AS string_col UNION ALL
  SELECT 2, '1,2,3,4,5'
)
SELECT id, 
  (SELECT ARRAY_AGG(CAST(num AS INT64)) 
    FROM UNNEST(SPLIT(string_col)) AS num
  ) AS num,
  ARRAY(SELECT CAST(num AS INT64) 
    FROM UNNEST(SPLIT(string_col)) AS num
  ) AS num_2
FROM yourTable