在BigQuery中使用正则表达式提取URL路径

问题场景 #

处理以下URL，需要提取域名后的路径部分，去除UTM参数，得到预期结果：

原始URL示例：

URL
https://www.newssite.com/news/biden-rail-strike/12345/UTM=company-Library/
https://www.newssite.com/news/news-about-today-exiting/55690/
https://www.nytimes.com/2022/11/29/us/politics/biden-rail-strike.html
https://www.differentnewssite.com/news/news-about-yesterday-exiting/55690/UTM=facebook
https://www.newssite.com/opinion/opinion-about-today-sad-insightful/578932/

预期提取结果：

Path
news/biden-rail-strike/12345/
news/news-about-today-exiting/55690/
2022/11/29/us/politics/biden-rail-strike.html
news/news-about-yesterday-exiting/55690/
opinion/opinion-about-today-sad-insightful/578932

可行方案 #

方案1：正则表达式直接提取

通过REGEXP_EXTRACT精准匹配域名后到UTM参数前的内容，同时处理无UTM时的末尾冗余斜杠：

SELECT
  url,
  CASE
    WHEN REGEXP_CONTAINS(url, r'UTM=') THEN REGEXP_EXTRACT(url, r'https?://[^/]+/(.*?)UTM=')
    ELSE RTRIM(REGEXP_EXTRACT(url, r'https?://[^/]+/(.*)'), '/')
  END AS path
FROM
  your_table_name

方案2：结合内置函数与正则替换

先用BigQuery的PARSE_URL提取完整路径，再通过正则去除UTM参数和多余斜杠：

SELECT
  url,
  RTRIM(REGEXP_REPLACE(LTRIM(PARSE_URL(url, 'PATH'), '/'), r'UTM=.*$', ''), '/') AS path
FROM
  your_table_name

结果说明 #

两种方案都能完全匹配预期需求：

• 带UTM参数的URL会自动截断到UTM之前的路径，保留原始路径末尾的斜杠
• 无UTM参数的URL会去除末尾冗余斜杠（如第五个示例）
• 静态页面类的URL（如纽约时报的链接）完整保留原始路径结构

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