Google BigQuery中的“糟糕的双重价值”

I'm working in Google BigQuery (not using LegacySQL), and I'm currently trying to cast() a string as a float64. Each time I get the error "Bad double value". I've also tried safe_cast() but it completely eliminates some of my id's (Ex: if one customer repeats 3 times for 3 different dates, and only has 'null' for a single "Height" entry, that customer is completely eliminated after I do safe_cast(), not just the row that had the 'null' value). I don't have any weird string value in my data, just whole or rational numbers or null entries.

我正在使用Google BigQuery（不使用LegacySQL），我目前正在尝试将一个字符串转换为float64。每次我收到错误“Bad double value”。我也尝试过safe_cast（）但它完全消除了我的一些id（例如：如果一个客户在3个不同的日期重复3次，并且只有'null'用于单个“Height”条目，那么客户在完全消除后我执行safe_cast（），而不仅仅是具有'null'值的行）。我的数据中没有任何奇怪的字符串值，只有整数或有理数或空条目。

Here's my current code:

这是我目前的代码：

select id, date,        
       cast(height as float64) as height,    
       cast(weight as float64) as weight   
from (select id, date, max(height) as height, max(weight) as weight   
      from table    
      group by 1,2
     )
group by 1, 2

1 个解决方案

#1

Of course safe_cast() returns NULL values. That is because you have inappropriate values in the data.

当然safe_cast（）返回NULL值。那是因为您在数据中有不适当的值。

You can find these by doing:

您可以通过以下方式找到这些：

select height, weight
from table
where safe_cast(height) is null or safe_cast(weight) is null;

Once you understand what the values are, fix the values or adjust the logic of the query.

一旦了解了值，请修复值或调整查询的逻辑。

If you just want the max of values are are properly numeric, then cast before the aggregation:

如果您只想要最大值是正确的数字，那么在聚合之前进行转换：

select id, date,
       max(safe_cast(height as float64)) as height,
       max(safe_cast(weight as float64)) as weight   
from table    
group by 1, 2;

A subquery doesn't seem necessary or desirable for your query.

查询似乎不需要或不需要子查询。

#1