BigQuery数组的STRING到INT数组 [英] Bigquery array of STRINGs to array of INTs
本文介绍了BigQuery数组的STRING到INT数组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图从一个列中获取 BigQuery
标准SQL中的 INT64
长字符串以逗号分隔(例如, 2013,1625,1297,7634
)。我可以通过以下方式轻松获取一组字符串:
SELECT
SPLIT(string_col,,)
FROM
table
但是,我想返回一个 INT64
s,不是一个字符串数组。我怎样才能做到这一点?我已经尝试了
CAST(SPLIT(string_col,,)AS ARRAY< INT64>)
code>
但这并不奏效。 div>
下面是针对BigQuery标准SQL的内容
$ b
#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
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
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>)
but that doesn't work.
解决方案
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屋!
查看全文