BigQuery SQL-将0更改为null,将数字更改为字符串类型和分析DateTime(单查询)

发布于 01-20 23:53 字数 693 浏览 3 评论 0原文

我有一个要求当前类型是数字的,但我需要它是日期时间,我目前正在与数据所有者合作以使他们转换数据类型,因此我不必执行这些步骤。

如上所述,数字类型需要更改为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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

心碎的声音2025-01-27 23:53:49

您可以将 case 语句包装在 CAST 和 PARSE_DATETIME 中,但也可以只使用 nullif:

SELECT

MATERIAL,DESC,NUMBER,

PARSE_DATETIME('%Y%m%d%H%M%S', CAST(NULLIF(START_ACTUAL,0) AS STRING)) AS START_ACTUAL,
PARSE_DATETIME('%Y%m%d%H%M%S', CAST(NULLIF(END_ACTUAL,0) AS STRING)) AS END_ACTUAL,

FROM `SAMPLEFILE1_VIEW`

You could wrap your case statements in the CAST and PARSE_DATETIME, but you could just use nullif:

SELECT

MATERIAL,DESC,NUMBER,

PARSE_DATETIME('%Y%m%d%H%M%S', CAST(NULLIF(START_ACTUAL,0) AS STRING)) AS START_ACTUAL,
PARSE_DATETIME('%Y%m%d%H%M%S', CAST(NULLIF(END_ACTUAL,0) AS STRING)) AS END_ACTUAL,

FROM `SAMPLEFILE1_VIEW`
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文