BigQuery如何从订单字符串提取起止日期为独立列

问题原因 #

之前用REGEXP_EXTRACT_ALL返回的是ARRAY<STRING>数组类型结果：

• BigQuery控制台默认会对数组字段做隐式展开，导致单条订单被拆成多行
• 数组类型无法直接用LEFT/RIGHT等字符串处理函数，会触发类型不匹配报错

可行实现方案 #

方案1：直接用正则分别匹配两个日期（推荐） #

不用生成数组，直接通过定位方括号、to关键字的位置，分别提取起止日期，不会出现拆行、类型报错问题：

SELECT
  Orders,
  -- 匹配左方括号后、to关键字前的日期作为起始日期
  REGEXP_EXTRACT(Orders, r'\[(\d{4}-\d{2}-\d{2}) to ') AS date1,
  -- 匹配to关键字后、右方括号前的日期作为结束日期
  REGEXP_EXTRACT(Orders, r' to (\d{4}-\d{2}-\d{2})\]') AS date2
FROM `你的表名`

方案2：从正则提取的数组中按偏移量取值 #

如果要沿用REGEXP_EXTRACT_ALL的写法，直接通过数组下标取对应位置的元素即可，BigQuery数组默认从0开始计数：

SELECT
  Orders,
  REGEXP_EXTRACT_ALL(Orders, r'\d{4}-\d{2}-\d{2}')[OFFSET(0)] AS date1,
  REGEXP_EXTRACT_ALL(Orders, r'\d{4}-\d{2}-\d{2}')[OFFSET(1)] AS date2
FROM `你的表名`

补充说明 #

• 上述两种写法返回的date1/date2默认是字符串格式，如果需要日期类型，直接在外层套DATE()函数转换即可
• 所有字符串处理函数只能作用于字符串类型值，数组类型必须先通过OFFSET取到具体元素后，才能调用字符串函数处理

内容的提问来源于stack exchange，提问作者Justin