基于USPS标准地址数据解析组合地址字段的效率优化问询

背景与需求 #

现有address_temp表结构如下：

ADDR varchar,
CONTACT varchar, 
REFER varchar,
TEMP_FK number

• TEMP_FK（外键）+ REFER作为唯一输出标识，关联数据关系
• CONTACT字段保持不变
• ADDR字段格式为「1234 Main St Apt 12 City State ZIP-4ZIP」（-4ZIP可选），需解析为：

  Line1: "1234 Main St"
  Line2: "Apt 12"
  City: "City"
  State: "State Abbreviation"
  ZIP: "5ZIP"
  

已借助USPS提供的ZIP_USPS表（含城市/州/邮编组合）实现了除Line1/Line2拆分外的逻辑，但查询效率极低：40条样本数据耗时约80秒。

当前实现SQL #

select *
from(
select  substr(ADDR, 1, instr(ADDR, ZP.CITY)-1) as LINES_1_2,
        CONTACT,
        ZP.CITY,
        ZP.ST,
        ZP.ZIP,
        ADDR,
        row_number() over (partition by ADDR order by ZP.CITY DESC) as ADD_UNIQ,
        row_number() over (partition by TEMP_FK || REFER order by TEMP_FK DESC) as KEY_UNIQ,
        TEMP_FK,
        REFER
from address_temp
cross apply(
    select  physical_city as CITY,
            physical_state_abv as ST,
            physical_zip as ZIP
            from ZIP_USPS
            where   ADDR LIKE ('%' || ZIP_USPS.PHYSICAL_CITY ||  '%')
            AND (ADDR LIKE ('%' || ZIP_USPS.PHYSICAL_STATE_ABV || '%')
            OR ADDR LIKE ('%' || ZIP_USPS.PHYSICAL_STATE || '%'))
            AND ADDR LIKE ('%' || ZIP_USPS.PHYSICAL_ZIP || '%')
)ZP
)
where KEY_UNIQ = 1

• KEY_UNIQ：解决ZIP_USPS因邮编后4位导致的城市/州/邮编组合重复问题
• ADD_UNIQ：为地址分组提供唯一标识，用于后续插值
• 已将inner join替换为cross apply提升速度，但多模糊匹配（ZIP_USPS共44045行）仍是性能瓶颈，尝试distinct后速度反而下降0.66秒

约束条件 #

• 样本数据不规范，无法仅通过邮编查询
• 需避免大城市邮编跨城市的匹配误差，兼顾准确性与效率

优化方案 #

1. 提取ADDR末尾的邮编前缀缩小匹配范围 #

美国邮编以5位数字开头，先从ADDR中提取末尾的5位邮编前缀，仅匹配ZIP_USPS中physical_zip以该前缀开头的记录，大幅减少匹配行数：

select *
from(
select  substr(AT.ADDR, 1, instr(AT.ADDR, ZP.CITY)-1) as LINES_1_2,
        AT.CONTACT,
        ZP.CITY,
        ZP.ST,
        ZP.ZIP,
        AT.ADDR,
        row_number() over (partition by AT.ADDR order by ZP.CITY DESC) as ADD_UNIQ,
        row_number() over (partition by AT.TEMP_FK || AT.REFER order by AT.TEMP_FK DESC) as KEY_UNIQ,
        AT.TEMP_FK,
        AT.REFER
from (
    select *, 
           -- 提取ADDR末尾的5位邮编前缀（兼容带-4ZIP的格式）
           substr(regexp_substr(ADDR, '\d{5}(-\d{4})?$', 1, 1, 'c'), 1, 5) as ZIP_5DIGIT
    from address_temp
) AT
cross apply(
    select  physical_city as CITY,
            physical_state_abv as ST,
            physical_zip as ZIP
            from ZIP_USPS
            where   substr(ZIP_USPS.physical_zip, 1, 5) = AT.ZIP_5DIGIT
            AND AT.ADDR LIKE ('%' || ZIP_USPS.PHYSICAL_CITY ||  '%')
            AND (AT.ADDR LIKE ('%' || ZIP_USPS.PHYSICAL_STATE_ABV || '%')
            OR AT.ADDR LIKE ('%' || ZIP_USPS.PHYSICAL_STATE || '%'))
)ZP
)
where KEY_UNIQ = 1

2. 为ZIP_USPS表创建复合索引 #

针对筛选条件创建索引，加速匹配逻辑：

-- 创建包含邮编前缀、城市、州缩写、州名称的复合索引
CREATE INDEX idx_zip_usps_city_state_zip ON ZIP_USPS 
(substr(physical_zip, 1, 5), physical_city, physical_state_abv, physical_state);

若数据库支持函数索引，直接基于函数创建索引效率更高：

CREATE INDEX idx_zip_usps_zip5_city_state ON ZIP_USPS 
(REGEXP_SUBSTR(physical_zip, '^\d{5}'), physical_city, physical_state_abv, physical_state);

3. 预聚合ZIP_USPS数据 #

由于ZIP_USPS存在同一城市/州/5位邮编的重复行，提前聚合去重，减少cross apply的匹配行数：

-- 先创建预聚合视图或临时表
CREATE OR REPLACE VIEW ZIP_USPS_AGG AS
SELECT DISTINCT 
       substr(physical_zip, 1, 5) as ZIP_5DIGIT,
       physical_city as CITY,
       physical_state_abv as ST,
       physical_state as STATE
FROM ZIP_USPS;

-- 优化后的查询使用预聚合视图
select *
from(
select  substr(AT.ADDR, 1, instr(AT.ADDR, ZP.CITY)-1) as LINES_1_2,
        AT.CONTACT,
        ZP.CITY,
        ZP.ST,
        AT.ZIP_5DIGIT as ZIP,
        AT.ADDR,
        row_number() over (partition by AT.ADDR order by ZP.CITY DESC) as ADD_UNIQ,
        row_number() over (partition by AT.TEMP_FK || AT.REFER order by AT.TEMP_FK DESC) as KEY_UNIQ,
        AT.TEMP_FK,
        AT.REFER
from (
    select *, 
           substr(regexp_substr(ADDR, '\d{5}(-\d{4})?$', 1, 1, 'c'), 1, 5) as ZIP_5DIGIT
    from address_temp
) AT
cross apply(
    select  CITY, ST
            from ZIP_USPS_AGG
            where   ZIP_USPS_AGG.ZIP_5DIGIT = AT.ZIP_5DIGIT
            AND AT.ADDR LIKE ('%' || ZIP_USPS_AGG.CITY ||  '%')
            AND (AT.ADDR LIKE ('%' || ZIP_USPS_AGG.ST || '%')
            OR AT.ADDR LIKE ('%' || ZIP_USPS_AGG.STATE || '%'))
)ZP
)
where KEY_UNIQ = 1

4. 改进城市匹配逻辑 #

利用地址中「城市位于州之前」的规律，先定位州的位置，再提取前面的城市，避免全模糊匹配：

select *
from(
select  substr(AT.ADDR, 1, instr(AT.ADDR, AT.CANDIDATE_CITY)-1) as LINES_1_2,
        AT.CONTACT,
        AT.CANDIDATE_CITY as CITY,
        ZP.ST,
        AT.ZIP_5DIGIT as ZIP,
        AT.ADDR,
        row_number() over (partition by AT.ADDR order by AT.CANDIDATE_CITY DESC) as ADD_UNIQ,
        row_number() over (partition by AT.TEMP_FK || AT.REFER order by AT.TEMP_FK DESC) as KEY_UNIQ,
        AT.TEMP_FK,
        AT.REFER
from (
    select *, 
           substr(regexp_substr(ADDR, '\d{5}(-\d{4})?$', 1, 1, 'c'), 1, 5) as ZIP_5DIGIT,
           -- 提取州缩写/全称前面的内容作为候选城市
           trim(regexp_substr(ADDR, '(\w+\s*)+' || '(?=\s+' || regexp_substr(ADDR, '([A-Z]{2}|[A-Za-z\s]+)$', 1, 1, 'c') || '\s+\d{5})', 1, 1, 'c')) as CANDIDATE_CITY
    from address_temp
) AT
join ZIP_USPS_AGG ZP
  on ZP.ZIP_5DIGIT = AT.ZIP_5DIGIT
  and ZP.CITY = AT.CANDIDATE_CITY
  and (ZP.ST = regexp_substr(AT.ADDR, '[A-Z]{2}(?=\s+\d{5})', 1, 1, 'c')
       or ZP.STATE = regexp_substr(AT.ADDR, '[A-Za-z\s]+(?=\s+\d{5})', 1, 1, 'c'))
)
where KEY_UNIQ = 1

样本数据 #

样本输出 #

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