BigQuery帮助-如何转换并转换为浮点和日期格式 [英] BigQuery Help - How to cast and convert to float and date format

问题描述

我正在尝试在BigQuery中做两件事，但是这样做很困难.

I'm trying to do two things in BigQuery, but I'm having difficulties doing so.

我想做两件事:

1. 将我的日期列转换为日期格式(当前为 int64 ，以 43379 为例)
2. 将我的列 Delivered_Cost 和 Actual_Cost 转换为 float (它们当前为字符串类型)-当存在空值时，是-而不是 0 .当您转换为 float 时，这些-会自动更改为 0 还是我必须先对其进行更新?

1. Convert my date columns to Date format (it is currently in int64, with 43379 as an example)
2. Cast my columns Delivered_Cost and Actual_Cost to float (they are currently with string type) - As when there is a null value, there is - instead of 0. When you cast to float, do these -s automatically change to 0 or do I have to update that first?

我对此没有太多经验，而且在网上寻找解决方案时遇到了困难，所以我很乐意提供帮助！我在同时投射和显示表中的数据时遇到了困难.

I don't have much experience this, and I've been having difficulties looking for a solution online so I'd love any help! I'm having difficulties casting and displaying data from my table at the same time.

谢谢！

SELECT * FROM TABLE1
CAST(Delivered_Cost as float) 

推荐答案

1. 将我的日期列转换为日期格式(当前为int64，以43379为例)

使用函数 PARSE_DATE() :

PARSE_DATE(Delivered_Date, '%Y-%m-%d')

The following doc lists the supported formats.

1. 将我的列"Delivered_Cost"和"Actual_Cost"浮动(当前为字符串类型)

您使用 CASE()的语法是可以的；您还可以使用快捷方式方法 FLOAT().但是，如果您的字符串没有成功映射到浮点数(例如单独的-)，则会发生运行时错误.您可以使用 SAFE_CAST() 来忽略转换错误，但这也可能导致忽略相关错误.因此，您最好使用 REPLACE().

Your syntax with CASE() is OK ; you could also use shortcut method FLOAT(). However if your string does not successfully maps to a float (like - alone), a runtime error will occur. You could use SAFE_CAST() to ignore conversion error, but that might also lead to ignoring relevant errors. Hence, you would better use REPLACE().

这是您的查询:

SELECT
    PARSE_DATE(Delivered_Date, '%Y-%m-%d') AS Delivered_Date,
    FLOAT(REPLACE(Delivered_Cost, '-', '0')) AS Delivered_Cost,
    FLOAT(REPLACE(Actual_Cost, '-', '0')) AS Actual_Cost  
FROM MYTABLE


FLOAT(Delivered_Cost)

这篇关于BigQuery帮助-如何转换并转换为浮点和日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！