sql - BigQuery 的问题不尊重 WHEN 语句
问题描述
如果我尝试解析和更正字符串结构中的某些日期，我有以下查询（小节），9999 是指循环日期，而 9996 是指仅对闰年有效的循环日期。
将 9999 年更改为当前周期的 FIX_YEAR 函数应该忽略任何 9996 年输入，但是当我使用示例字符串运行查询时，它不会被忽略。
任何想法可能是什么问题？
谢谢！
CREATE TEMP FUNCTION
FIX_YEAR(val STRUCT<after DATE,
before DATE>) AS (
CASE
WHEN (EXTRACT(YEAR FROM val.after) = 9999) AND (EXTRACT(YEAR FROM val.before) = 9999) THEN ( CASE
WHEN DATE(EXTRACT(YEAR
FROM
CURRENT_DATE), EXTRACT(MONTH
FROM
val.after), EXTRACT(DAY
FROM
val.after)) <= DATE(EXTRACT(YEAR
FROM
CURRENT_DATE), EXTRACT(MONTH
FROM
val.before), EXTRACT(DAY
FROM
val.before)) THEN (
CASE
WHEN DATE(EXTRACT(YEAR FROM CURRENT_DATE), EXTRACT(MONTH FROM val.before), EXTRACT(DAY FROM val.before)) < CURRENT_DATE THEN ( [STRUCT ( DATE(EXTRACT(YEAR FROM CURRENT_DATE) + 1, EXTRACT(MONTH FROM val.after), EXTRACT(DAY FROM val.after)) AS after, DATE(EXTRACT(YEAR FROM CURRENT_DATE) + 1, EXTRACT(MONTH FROM val.before), EXTRACT(DAY FROM val.before)) AS before )] )
WHEN DATE_DIFF( DATE(EXTRACT(YEAR
FROM
CURRENT_DATE) + 1, EXTRACT(MONTH
FROM
val.after), EXTRACT(DAY
FROM
val.after)), CURRENT_DATE, DAY) <= 330 THEN ( [STRUCT ( DATE(EXTRACT(YEAR
FROM
CURRENT_DATE) + 1, EXTRACT(MONTH
FROM
val.after), EXTRACT(DAY
FROM
val.after)) AS after,
DATE(EXTRACT(YEAR
FROM
CURRENT_DATE) + 1, EXTRACT(MONTH
FROM
val.before), EXTRACT(DAY
FROM
val.before)) AS before ),
STRUCT ( DATE(EXTRACT(YEAR
FROM
CURRENT_DATE), EXTRACT(MONTH
FROM
val.after), EXTRACT(DAY
FROM
val.after)) AS after,
DATE(EXTRACT(YEAR
FROM
CURRENT_DATE), EXTRACT(MONTH
FROM
val.before), EXTRACT(DAY
FROM
val.before)) AS before )] )
ELSE
( [STRUCT ( DATE(EXTRACT(YEAR
FROM
CURRENT_DATE), EXTRACT(MONTH
FROM
val.after), EXTRACT(DAY
FROM
val.after)) AS after,
DATE(EXTRACT(YEAR
FROM
CURRENT_DATE), EXTRACT(MONTH
FROM
val.before), EXTRACT(DAY
FROM
val.before)) AS before )] )
END
)
ELSE
[val]
END
)
ELSE
[val]
END
);
CREATE TEMP FUNCTION
EXTRACT_SEASONAL_DATES(season_text STRING) AS ( FIX_YEAR ( STRUCT ( PARSE_DATE('%Y-%m-%d',
COALESCE(REGEXP_EXTRACT(season_text, r'^(\d{4}-\d{2}-\d{2})@'),
'9999-12-31')) AS after,
PARSE_DATE('%Y-%m-%d',
COALESCE(REGEXP_EXTRACT(season_text, r'@(\d{4}-\d{2}-\d{2})$'),
'9999-12-31')) AS before ) ) );
SELECT
EXTRACT_SEASONAL_DATES(season_inbound) season_inbound
FROM (
SELECT
"9999-02-01@9999-02-02,9999-02-09@9999-02-09,9999-02-15@9999-02-17,9999-02-22@9999-02-24,9996-02-29@9999-03-02,9999-04-08@9999-04-13,9999-05-03@9999-05-04,9999-05-24@9999-05-25,9999-07-01@9999-07-06,9999-07-15@9999-07-17,9999-07-19@9999-07-20,9999-07-24@9999-07-29,9999-08-16@9999-08-17,9999-09-12@9999-09-23,9999-11-02@9999-11-02" AS season_inbound)
CROSS JOIN
UNNEST(SPLIT(season_inbound, ',')) season_inbound
解决方案
我认为代码运行良好。
正如@FelipeHoffa 在第一条评论中提到的那样，该问题与无效日期的错误有关，对于您的情况“9996-02-29@9999-03-02”，这是一个无效日期，因为您的函数将其转换为“2019 -02-29”该日期不存在，因此 BigQuery 会引发错误消息“输入计算为无效日期：2019-02-29”。
我已将此日期修改为“9996-02-28@9999-03-02”，一切正常。
CREATE TEMP FUNCTION
FIX_YEAR(val STRUCT<after DATE,
before DATE>) AS (
CASE
WHEN (EXTRACT(YEAR FROM val.after) = 9999) AND (EXTRACT(YEAR FROM val.before) = 9999) THEN ( CASE
WHEN DATE(EXTRACT(YEAR
FROM
CURRENT_DATE), EXTRACT(MONTH
FROM
val.after), EXTRACT(DAY
FROM
val.after)) <= DATE(EXTRACT(YEAR
FROM
CURRENT_DATE), EXTRACT(MONTH
FROM
val.before), EXTRACT(DAY
FROM
val.before)) THEN (
CASE
WHEN DATE(EXTRACT(YEAR FROM CURRENT_DATE), EXTRACT(MONTH FROM val.before), EXTRACT(DAY FROM val.before)) < CURRENT_DATE THEN ( [STRUCT ( DATE(EXTRACT(YEAR FROM CURRENT_DATE) + 1, EXTRACT(MONTH FROM val.after), EXTRACT(DAY FROM val.after)) AS after, DATE(EXTRACT(YEAR FROM CURRENT_DATE) + 1, EXTRACT(MONTH FROM val.before), EXTRACT(DAY FROM val.before)) AS before )] )
WHEN DATE_DIFF( DATE(EXTRACT(YEAR
FROM
CURRENT_DATE) + 1, EXTRACT(MONTH
FROM
val.after), EXTRACT(DAY
FROM
val.after)), CURRENT_DATE, DAY) <= 330 THEN ( [STRUCT ( DATE(EXTRACT(YEAR
FROM
CURRENT_DATE) + 1, EXTRACT(MONTH
FROM
val.after), EXTRACT(DAY
FROM
val.after)) AS after,
DATE(EXTRACT(YEAR
FROM
CURRENT_DATE) + 1, EXTRACT(MONTH
FROM
val.before), EXTRACT(DAY
FROM
val.before)) AS before ),
STRUCT ( DATE(EXTRACT(YEAR
FROM
CURRENT_DATE), EXTRACT(MONTH
FROM
val.after), EXTRACT(DAY
FROM
val.after)) AS after,
DATE(EXTRACT(YEAR
FROM
CURRENT_DATE), EXTRACT(MONTH
FROM
val.before), EXTRACT(DAY
FROM
val.before)) AS before )] )
ELSE
( [STRUCT ( DATE(EXTRACT(YEAR
FROM
CURRENT_DATE), EXTRACT(MONTH
FROM
val.after), EXTRACT(DAY
FROM
val.after)) AS after,
DATE(EXTRACT(YEAR
FROM
CURRENT_DATE), EXTRACT(MONTH
FROM
val.before), EXTRACT(DAY
FROM
val.before)) AS before )] )
END
)
ELSE
[val]
END
)
ELSE
[val]
END
);
CREATE TEMP FUNCTION
EXTRACT_SEASONAL_DATES(season_text STRING) AS ( FIX_YEAR ( STRUCT ( PARSE_DATE('%Y-%m-%d',
COALESCE(REGEXP_EXTRACT(season_text, r'^(\d{4}-\d{2}-\d{2})@'),
'9999-12-31')) AS after,
PARSE_DATE('%Y-%m-%d',
COALESCE(REGEXP_EXTRACT(season_text, r'@(\d{4}-\d{2}-\d{2})$'),
'9999-12-31')) AS before ) ) );
SELECT
EXTRACT_SEASONAL_DATES(season_inbound) season_inbound
FROM (
SELECT
"9999-02-01@9999-02-02,9999-02-09@9999-02-09,9999-02-15@9999-02-17,9999-02-22@9999-02-24,9996-02-28@9999-03-02,9999-04-08@9999-04-13,9999-05-03@9999-05-04,9999-05-24@9999-05-25,9999-07-01@9999-07-06,9999-07-15@9999-07-17,9999-07-19@9999-07-20,9999-07-24@9999-07-29,9999-08-16@9999-08-17,9999-09-12@9999-09-23,9999-11-02@9999-11-02" AS season_inbound)
CROSS JOIN
UNNEST(SPLIT(season_inbound, ',')) season_inbound
得到这个结果：
推荐阅读
- css - 如何绑定由构成颜色名称、角度等的动态变量组成的线性渐变背景属性？
- json - 我正在尝试加载无法加载的 json 文件
- c++ - C++ 中 golang 样式延迟范围保护的性能
- python - 如何使用 re.sub 用自定义文本替换特定的正则表达式组？
- laravel-backpack - 是否可以过滤掉 Laravel 的背包重新排序中的一些元素？
- css - CSS 为新元素创建选择器
- kubernetes - Kubernetes服务选择器用于选择另一个服务而不是部署？
- c++ - POCO C++ 加密/解密
- c - 为什么不直接使用命令'CFLAGS += -fno-stack-protector'？
- c# - 在负载均衡器（梭子鱼）后面的 IIS 中托管时，Asp.Net SignalR 无法正常工作