BigQuery数组的STRING到INT数组

本文介绍了BigQuery数组的STRING到INT数组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我试图从一个列中获取 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数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！