BigQuery Standard SQL 如何将STRING转换为ARRAY<FLOAT64>类型

错误原因 #

你原写法中在FROM子句中对两个拆分后的数组做交叉连接展开，会生成两个数组元素数乘积的多行笛卡尔积结果，自然会产生大量重复数据。

正确实现方案 #

直接使用ARRAY()函数封装元素转换逻辑，无需将数组展开到主查询的行维度，就能直接得到每行对应一条、字段为数组类型的结果：

with tbl as (
  select "id1" as id, 
  "10000\t10001\t10002\t10003\t10004" col1_str, 
  "10000\t10001.1\t10002\t10003\t10004" col2_str 
)
select 
  id,
  ARRAY(SELECT CAST(elem AS FLOAT64) FROM UNNEST(SPLIT(col1_str, "\t")) elem) AS col1_floatarray,
  ARRAY(SELECT CAST(elem AS FLOAT64) FROM UNNEST(SPLIT(col2_str, "\t")) elem) AS col2_floatarray
from tbl

逻辑说明 #

1. 先用SPLIT(col_str, "\t")将制表符分隔的字符串拆分为STRING类型的数组
2. 用UNNEST()将数组拆分为单行元素后逐个做FLOAT64类型转换
3. 最外层用ARRAY()将转换后的单行元素重新聚合为FLOAT64数组，全程不会改变主查询的行数

内容的提问来源于stack exchange，提问作者Will