如何简化BigQuery中嵌套多个regex_replace的地址标准化操作？

在BigQuery里处理地址标准化时，嵌套一堆REGEXP_REPLACE确实繁琐，这里给你几个更简洁易维护的实现方式：

方法一：单条REGEXP_REPLACE+分支匹配 #

把所有要替换的关键词整合到一个正则里，用CASE WHEN映射对应的缩写，一次调用就能完成所有替换：

SELECT
  REGEXP_REPLACE(
    UPPER(address),
    r'\b(STREET|ROAD|AVENUE|BOULEVARD|DRIVE)\b',
    CASE
      WHEN REGEXP_EXTRACT(UPPER(address), r'\bSTREET\b') IS NOT NULL THEN 'ST'
      WHEN REGEXP_EXTRACT(UPPER(address), r'\bROAD\b') IS NOT NULL THEN 'RD'
      WHEN REGEXP_EXTRACT(UPPER(address), r'\bAVENUE\b') IS NOT NULL THEN 'AVE'
      WHEN REGEXP_EXTRACT(UPPER(address), r'\bBOULEVARD\b') IS NOT NULL THEN 'BLVD'
      WHEN REGEXP_EXTRACT(UPPER(address), r'\bDRIVE\b') IS NOT NULL THEN 'DR'
    END
  ) AS standardized_address
FROM your_table

这里的\b是单词边界，避免把类似"STREETNAME"这种包含关键词的长单词误替换。

方法二：数组存规则+AGGREGATE批量应用 #

这种方式最适合规则较多的场景，所有替换规则集中管理，新增或修改规则只需调整数组内容，不用改核心逻辑：

WITH replace_rules AS (
  -- 集中定义所有替换规则：搜索词和对应的缩写
  SELECT ARRAY_ZIP(
    ['STREET', 'ROAD', 'AVENUE', 'BOULEVARD', 'DRIVE'],
    ['ST', 'RD', 'AVE', 'BLVD', 'DR']
  ) AS rule_pairs
),
address_list AS (
  SELECT address FROM your_table
)
SELECT
  AGGREGATE(
    rule,
    (current_addr, r) -> REGEXP_REPLACE(current_addr, r'\b' || r[OFFSET(0)] || r'\b', r[OFFSET(1)]),
    UPPER(address)
  ) AS standardized_address
FROM address_list, replace_rules, UNNEST(rule_pairs) AS rule
GROUP BY address

AGGREGATE函数会遍历所有规则，依次对地址应用替换操作，效果和嵌套REGEXP_REPLACE完全一致，但可读性和维护性强太多。

注意事项 #

• 如果需要替换的是大小写混合的地址，先通过UPPER()或LOWER()统一格式，避免漏匹配；
• 始终用\b单词边界，防止部分匹配导致的错误替换；
• 若规则特别多，方法二的扩展性最好，不用反复嵌套函数。

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