如何在BigQuery中用正则提取多组cancellation_amount到不同列？

问题分析 #

你当前使用的REGEXP_EXTRACT只会返回正则匹配到的第一个结果，所以只能拿到602000。要提取所有匹配值并拆分到不同列，可以用以下两种方案：

方案1：使用REGEXP_EXTRACT_ALL生成数组后拆分（推荐） #

先通过REGEXP_EXTRACT_ALL提取所有cancellation_amount的数值到数组，再通过数组索引分别取对应值到不同列：

SELECT
  file,
  -- 提取第一个cancellation_amount
  cancellation_amounts[OFFSET(0)] AS cancellation_amount_1,
  -- 提取第二个cancellation_amount，若不存在则返回NULL（可按需用IFNULL替换默认值）
  IFNULL(cancellation_amounts[OFFSET(1)], 0) AS cancellation_amount_2
FROM (
  SELECT
    file,
    -- 匹配所有cancellation_amount后的数字
    REGEXP_EXTRACT_ALL(file, r'cancellation_amount:\s*(\d+)') AS cancellation_amounts
  FROM your_table -- 替换为你的表名
)

方案2：直接用REGEXP_EXTRACT分别匹配不同位置的结果 #

如果确定只有两个cancellation_amount，也可以直接写正则分别匹配第一个和第二个：

SELECT
  file,
  -- 匹配第一个cancellation_amount
  REGEXP_EXTRACT(file, r'cancellation_amount:\s*(\d+)') AS cancellation_amount_1,
  -- 匹配第二个cancellation_amount（跳过第一个匹配项）
  REGEXP_EXTRACT(file, r'(?:.*cancellation_amount:\s*\d+\s*)+cancellation_amount:\s*(\d+)') AS cancellation_amount_2
FROM your_table -- 替换为你的表名

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