BigQuery含IF/While脚本开发：正则匹配空值兼容需求

Got it, let's tackle this problem step by step. Your original script breaks because null values in eventAction or eventLabel from table_1 mess up the REGEXP_CONTAINS checks (since it can't handle null patterns). Plus, we can clean up that redundant case when I=I logic while we're at it.

方案1：按需求实现带条件判断的循环脚本 #

这个版本严格遵循你想要的逐行循环逻辑，新增了空值判断分支来适配不同场景：

DECLARE N1 INT64;
DECLARE I INT64;
-- 声明变量存储当前行的字段值，避免重复查询table_1
DECLARE current_eventAction STRING;
DECLARE current_eventLabel STRING;
DECLARE current_eventCategory STRING;
DECLARE current_Rotulo STRING;
DECLARE current_Produto STRING;

SET N1 = (SELECT COUNT(*) FROM `table_1`);
SET I = 1;

WHILE I <= N1 DO
  -- 一次性获取当前Cod对应的所有字段值
  SELECT eventAction, eventLabel, eventCategory, Rotulo, Produto
  INTO current_eventAction, current_eventLabel, current_eventCategory, current_Rotulo, current_Produto
  FROM `table_1` WHERE Cod = I;

  -- 根据空值情况执行不同的插入逻辑
  IF current_eventAction IS NULL THEN
    -- 已知eventAction为空时eventLabel必为空，只匹配eventCategory
    INSERT `table_2` (eventAction, eventCategory, eventLabel, Rotulo, Produto)
    SELECT DISTINCT
      h.eventInfo.eventAction,
      h.eventInfo.eventCategory,
      h.eventInfo.eventLabel,
      current_Rotulo AS Rotulo,
      current_Produto AS Produto
    FROM `table_3` t, UNNEST(t.hits) AS h
    WHERE h.eventInfo.eventCategory = current_eventCategory;
  ELSIF current_eventLabel IS NULL THEN
    -- eventAction非空，eventLabel为空，匹配eventAction和eventCategory
    INSERT `table_2` (eventAction, eventCategory, eventLabel, Rotulo, Produto)
    SELECT DISTINCT
      h.eventInfo.eventAction,
      h.eventInfo.eventCategory,
      h.eventInfo.eventLabel,
      current_Rotulo AS Rotulo,
      current_Produto AS Produto
    FROM `table_3` t, UNNEST(t.hits) AS h
    WHERE REGEXP_CONTAINS(h.eventInfo.eventAction, current_eventAction)
      AND h.eventInfo.eventCategory = current_eventCategory;
  ELSE
    -- 所有字段非空，匹配全部三个条件
    INSERT `table_2` (eventAction, eventCategory, eventLabel, Rotulo, Produto)
    SELECT DISTINCT
      h.eventInfo.eventAction,
      h.eventInfo.eventCategory,
      h.eventInfo.eventLabel,
      current_Rotulo AS Rotulo,
      current_Produto AS Produto
    FROM `table_3` t, UNNEST(t.hits) AS h
    WHERE REGEXP_CONTAINS(h.eventInfo.eventAction, current_eventAction)
      AND h.eventInfo.eventCategory = current_eventCategory
      AND REGEXP_CONTAINS(h.eventInfo.eventLabel, current_eventLabel);
  END IF;

  SET I = I + 1;
END WHILE;

关键改进点： #

• 用SELECT INTO一次性获取当前行的所有字段，避免多次重复查询table_1，提升效率
• 利用你提到的column2为空时column3必为空的规则，简化第一个分支的判断逻辑
• 移除了无意义的case when I=I语句，直接使用变量赋值Rotulo和Produto

方案2：推荐的批量处理优化版本 #

BigQuery是为大规模批量数据处理设计的，逐行循环在数据量大时会非常低效。下面这个版本用JOIN替代循环，一次完成所有插入操作：

INSERT `table_2` (eventAction, eventCategory, eventLabel, Rotulo, Produto)
SELECT DISTINCT
  h.eventInfo.eventAction,
  h.eventInfo.eventCategory,
  h.eventInfo.eventLabel,
  t1.Rotulo,
  t1.Produto
FROM `table_1` t1
CROSS JOIN `table_3` t3
UNNEST(t3.hits) AS h
WHERE
  -- 分三个场景匹配条件，和循环版本逻辑完全一致
  (t1.eventAction IS NULL AND h.eventInfo.eventCategory = t1.eventCategory)
  OR
  (t1.eventAction IS NOT NULL AND t1.eventLabel IS NULL 
   AND REGEXP_CONTAINS(h.eventInfo.eventAction, t1.eventAction)
   AND h.eventInfo.eventCategory = t1.eventCategory)
  OR
  (t1.eventAction IS NOT NULL AND t1.eventLabel IS NOT NULL
   AND REGEXP_CONTAINS(h.eventInfo.eventAction, t1.eventAction)
   AND h.eventInfo.eventCategory = t1.eventCategory
   AND REGEXP_CONTAINS(h.eventInfo.eventLabel, t1.eventLabel));

优势： #

• 避免了循环带来的额外开销，处理速度更快
• 代码更简洁，易读易维护
• 符合BigQuery的分布式处理模型，能更好地利用资源

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