Bigquery STRING 数组到 INT 数组 [英] Bigquery array of STRINGs to array of INTs

问题描述

我正在尝试从一个列中提取 BigQuery 标准 SQL 中的 INT64 数组，该列是由逗号分隔的一长串数字(例如 <代码>2013,1625,1297,7634).我可以轻松地拉出一系列字符串:

I'm trying to pull an array of INT64 s in BigQuery standard SQL from a column which is a long string of numbers separated by commas (for example, 2013,1625,1297,7634). I can pull an array of strings easily with:

SELECT
  SPLIT(string_col,",")
FROM
  table

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

However, I want to return an array of INT64 s, not an array of strings. How can I do that? I've tried

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

但这不起作用.

推荐答案

以下为 BigQuery Standard SQL

Below is for BigQuery Standard 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

这篇关于Bigquery STRING 数组到 INT 数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！