Oracle正则提取后关联Lookup结果异常问题排查

原始数据 #

MY_TABLE中的记录如下：

INSERT INTO MY_TABLE(JOB_DEFINITION) VALUES ('JobDefinition://company/apps/team/crmCommon/workMgmt/assignmentMgr/ObjectShareBatchAssignRequest');
INSERT INTO MY_TABLE(JOB_DEFINITION) VALUES ('JobDefinition://company/apps/team/financials/commonModules/metrics/GenerateFinMetrics');
INSERT INTO MY_TABLE(JOB_DEFINITION) VALUES ('JobDefinition://company/apps/team/financials/subledgerAccounting/shared/XLAOTEC');
INSERT INTO MY_TABLE(JOB_DEFINITION) VALUES ('JobDefinition://company/apps/team/fnd/applcore/FndIDCSSyncNotifyServiceJob');
INSERT INTO MY_TABLE(JOB_DEFINITION) VALUES ('JobDefinition://company/apps/team/fnd/applcore/FndOSCSAttachmentIngestJob');
INSERT INTO MY_TABLE(JOB_DEFINITION) VALUES ('JobDefinition://company.apps.team.biccc/BICloudConnectorJobDefinition');
INSERT INTO MY_TABLE(JOB_DEFINITION) VALUES ('JobDefinition://company/apps/team/cdm/dataStewardship/batchEnrichmeąntProcteam/ScheduledAccountEnrichmentJob');
INSERT INTO MY_TABLE(JOB_DEFINITION) VALUES ('JobDefinition://company/apps/team/hcm/batchProcesses/core/ArchiveWriteJob');

正则提取结果（正常） #

执行以下正则查询提取ep字段：

select JOB_DEFINITION, 
regexp_replace(JOB_DEFINITION, 'Job.*://company[/\.][a-z]+[/\.]team[/\.]([a-zA-Z_]+)/?(.*)$', '\1') as ep
from MY_TABLE;

得到正确结果：

Lookup映射异常 #

执行以下Lookup关联查询时出现错误：

select met.job_definition, regex.ep,lookup.family
from (select NVL(regexp_replace(JOB_DEFINITION, 'Job.*://company[/\.][a-z]+[/\.]team[/\.]([a-zA-Z_]+)/?(.*)$', '\1'),'TBD') as ep
      from MY_TABLE) regex,
     (select 'cdm' as ExtractedProduct, 'CRM' as Family
      from dual
      union all
      select 'crm' as ExtractedProduct, 'CRM' as Family
      from dual
      union all
      select 'crmCommon' as ExtractedProduct, 'CRM' as Family
      from dual
      union all
      select 'financials' as ExtractedProduct, 'ERP' as Family
      from dual
      union all
      select 'custom' as ExtractedProduct, 'Custom' as Family
      from dual
      union all
      select 'fnd' as ExtractedProduct, 'ApplCore' as Family
      from dual
      union all
      select 'hcm' as ExtractedProduct, 'HCM' as Family
      from dual
      union all
      select 'incentiveCompensation' as ExtractedProduct, 'ERP' as Family
      from dual
      union all
      select 'marketing' as ExtractedProduct, 'CRM' as Family
      from dual
      union all
      select 'prc' as ExtractedProduct, 'SCM' as Family
      from dual
      union all
      select 'projects' as ExtractedProduct, 'ERP' as Family
      from dual
      union all
      select 'sales' as ExtractedProduct, 'CRM' as Family
      from dual
      union all
      select 'scm' as ExtractedProduct, 'SCM' as Family
      from dual
      union all
      select 'search' as ExtractedProduct, 'ApplCore' as Family
      from dual
      union all
      select 'psc' as ExtractedProduct, 'ERP' as Family
      from dual
      union all
      select 'contracts' as ExtractedProduct, 'CRM' as Family
      from dual
      union all
      select 'extn' as ExtractedProduct, 'Custom' as Family
      from dual) lookup,
     MY_TABLE met
where regex.ep = lookup.ExtractedProduct

错误结果：

显然该记录的ep应为hcm，对应Family为HCM，结果匹配错误。

问题原因及解决方案 #

原因 #

原查询中，regex子查询、lookup表和MY_TABLE之间没有建立正确的关联关系，导致三者形成笛卡尔积：当regex.ep与lookup.ExtractedProduct匹配时，会将所有MY_TABLE的记录与该匹配组合关联，从而出现错误的对应关系。

解决方案 #

需要确保每条原始记录的ep提取结果与自身关联后，再匹配lookup表。以下是两种修正后的查询方式：

方式一：直接在主查询中提取ep并关联

select 
    met.JOB_DEFINITION, 
    regexp_replace(met.JOB_DEFINITION, 'Job.*://company[/\.][a-z]+[/\.]team[/\.]([a-zA-Z_]+)/?(.*)$', '\1') as ep,
    lookup.Family
from MY_TABLE met
left join (
    select 'cdm' as ExtractedProduct, 'CRM' as Family from dual union all
    select 'crm' as ExtractedProduct, 'CRM' as Family from dual union all
    select 'crmCommon' as ExtractedProduct, 'CRM' as Family from dual union all
    select 'financials' as ExtractedProduct, 'ERP' as Family from dual union all
    select 'custom' as ExtractedProduct, 'Custom' as Family from dual union all
    select 'fnd' as ExtractedProduct, 'ApplCore' as Family from dual union all
    select 'hcm' as ExtractedProduct, 'HCM' as Family from dual union all
    select 'incentiveCompensation' as ExtractedProduct, 'ERP' as Family from dual union all
    select 'marketing' as ExtractedProduct, 'CRM' as Family from dual union all
    select 'prc' as ExtractedProduct, 'SCM' as Family from dual union all
    select 'projects' as ExtractedProduct, 'ERP' as Family from dual union all
    select 'sales' as ExtractedProduct, 'CRM' as Family from dual union all
    select 'scm' as ExtractedProduct, 'SCM' as Family from dual union all
    select 'search' as ExtractedProduct, 'ApplCore' as Family from dual union all
    select 'psc' as ExtractedProduct, 'ERP' as Family from dual union all
    select 'contracts' as ExtractedProduct, 'CRM' as Family from dual union all
    select 'extn' as ExtractedProduct, 'Custom' as Family from dual
) lookup on regexp_replace(met.JOB_DEFINITION, 'Job.*://company[/\.][a-z]+[/\.]team[/\.]([a-zA-Z_]+)/?(.*)$', '\1') = lookup.ExtractedProduct

方式二：使用CTE先提取ep再关联

with regex_extract as (
    select 
        JOB_DEFINITION,
        NVL(regexp_replace(JOB_DEFINITION, 'Job.*://company[/\.][a-z]+[/\.]team[/\.]([a-zA-Z_]+)/?(.*)$', '\1'), 'TBD') as ep
    from MY_TABLE
)
select 
    re.JOB_DEFINITION,
    re.ep,
    lookup.Family
from regex_extract re
left join (
    select 'cdm' as ExtractedProduct, 'CRM' as Family from dual union all
    select 'crm' as ExtractedProduct, 'CRM' as Family from dual union all
    select 'crmCommon' as ExtractedProduct, 'CRM' as Family from dual union all
    select 'financials' as ExtractedProduct, 'ERP' as Family from dual union all
    select 'custom' as ExtractedProduct, 'Custom' as Family from dual union all
    select 'fnd' as ExtractedProduct, 'ApplCore' as Family from dual union all
    select 'hcm' as ExtractedProduct, 'HCM' as Family from dual union all
    select 'incentiveCompensation' as ExtractedProduct, 'ERP' as Family from dual union all
    select 'marketing' as ExtractedProduct, 'CRM' as Family from dual union all
    select 'prc' as ExtractedProduct, 'SCM' as Family from dual union all
    select 'projects' as ExtractedProduct, 'ERP' as Family from dual union all
    select 'sales' as ExtractedProduct, 'CRM' as Family from dual union all
    select 'scm' as ExtractedProduct, 'SCM' as Family from dual union all
    select 'search' as ExtractedProduct, 'ApplCore' as Family from dual union all
    select 'psc' as ExtractedProduct, 'ERP' as Family from dual union all
    select 'contracts' as ExtractedProduct, 'CRM' as Family from dual union all
    select 'extn' as ExtractedProduct, 'Custom' as Family from dual
) lookup on re.ep = lookup.ExtractedProduct

这两种方式都能保证每条原始记录的ep与自身正确绑定，再匹配对应的Family值，避免笛卡尔积导致的错误关联。

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