BigQuery中使用正则优化LIKE操作符提取URL内productid的方案咨询

你提到的需求在 BigQuery 中完全可以实现。

问题原因 #

你原有代码的报错根因是 LIKE '%-p%' 的匹配规则过于宽松：只要URL中出现任意-p字符就会进入第一个分支，但若这个-p后面没有紧跟数字和/，后续的REGEXP_EXTRACT会返回空值，也就出现了你遇到的-m开头ID的URL因为包含无关-p字符返回空的问题。

方案1：修复原有CASE WHEN逻辑 #

用 BigQuery 内置的 REGEXP_CONTAINS 函数替代 LIKE 做判断，直接匹配-p/m后接数字再加/的规则，同时优化提取逻辑，用正则捕获组直接拿到ID数字部分，省去REGEXP_REPLACE步骤：

CASE
   WHEN REGEXP_CONTAINS(MAX(hits.page.pagePath), r'-p\d+/') 
     THEN MAX(REGEXP_EXTRACT(hits.page.pagePath, r'-p(\d+)/'))                                
   WHEN REGEXP_CONTAINS(MAX(hits.page.pagePath), r'-m\d+/') 
     THEN MAX(REGEXP_EXTRACT(hits.page.pagePath, r'-m(\d+)/'))
   ELSE NULL            
END AS productId

方案2：简化为单条提取语句 #

因为两类ID仅前缀p/m不同，完全可以合并成一个正则提取语句，不需要写CASE WHEN，更简洁高效：

MAX(REGEXP_EXTRACT(hits.page.pagePath, r'-[pm](\d+)/')) AS productId

如果你的业务场景中存在同一个URL同时出现-p加数字和-m加数字的情况，可以根据优先级调整匹配规则，比如优先取p开头的ID可以调整为：

MAX(COALESCE(
  REGEXP_EXTRACT(hits.page.pagePath, r'-p(\d+)/'),
  REGEXP_EXTRACT(hits.page.pagePath, r'-m(\d+)/')
)) AS productId

内容的提问来源于stack exchange，提问作者Bendegúz Tunyogi