BigQuery SQL-将0更改为null,将数字更改为字符串类型和分析DateTime(单查询)
我有一个要求当前类型是数字的,但我需要它是日期时间,我目前正在与数据所有者合作以使他们转换数据类型,因此我不必执行这些步骤。
如上所述,数字类型需要更改为DateTime,但是我有一个存在零值的问题。我目前已经编写了两个SQL查询来实现结果,但是我希望能够在一个查询中做到这一点。
请参阅以下两个查询:
查询1
SELECT
MATERIAL,DESC,NUMBER,
CASE
WHEN START_ACTUAL = 0 THEN NULL
ELSE START_ACTUAL
END AS START_ACTUAL,
CASE
WHEN END_ACTUAL = 0 THEN NULL
ELSE END_ACTUAL
END AS END_ACTUAL,
FROM `SAMPLEFILE1`
2
SELECT
MATERIAL,DESC,NUMBER,
PARSE_DATETIME('%Y%m%d%H%M%S', CAST(START_ACTUAL AS STRING)) AS START_ACTUAL,
PARSE_DATETIME('%Y%m%d%H%M%S', CAST(END_ACTUAL AS STRING)) AS END_ACTUAL,
FROM `SAMPLEFILE1_VIEW`
我敢
查询
I have a requirement where the current type is NUMERIC but I need it to be DATETIME, I am currently working with the data owner to get them to convert the data type so I will not have to perform these steps.
As stated above, the NUMERIC type needs to be changed to DATETIME but I have an issue where zero values exist. I have currently written two SQL queries to achieve the result but I was hoping to be able to do this in a single query.
See the below two queries:
Query 1
SELECT
MATERIAL,DESC,NUMBER,
CASE
WHEN START_ACTUAL = 0 THEN NULL
ELSE START_ACTUAL
END AS START_ACTUAL,
CASE
WHEN END_ACTUAL = 0 THEN NULL
ELSE END_ACTUAL
END AS END_ACTUAL,
FROM `SAMPLEFILE1`
Query 2
SELECT
MATERIAL,DESC,NUMBER,
PARSE_DATETIME('%Y%m%d%H%M%S', CAST(START_ACTUAL AS STRING)) AS START_ACTUAL,
PARSE_DATETIME('%Y%m%d%H%M%S', CAST(END_ACTUAL AS STRING)) AS END_ACTUAL,
FROM `SAMPLEFILE1_VIEW`
I'm sure someone will put me right straightaway and it is very simple :)
Thanks in advance
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

您可以将 case 语句包装在 CAST 和 PARSE_DATETIME 中,但也可以只使用 nullif:
You could wrap your case statements in the CAST and PARSE_DATETIME, but you could just use nullif: