如何仅用Oracle SQL的SELECT和WITH语法实现跨实体客户匹配？

需要解决不同实体间的客户匹配问题，仅允许使用SQL的SELECT和WITH语法，禁止使用PL/SQL。匹配规则及要求如下：

• 匹配逻辑：基于P1字段直接匹配，或P2与P3跨字段校验匹配
• 具体客户匹配规则：
  • David：仅存在于实体1、2，两者P1均为100，统一分配组ID G1
  • Lloyd：存在于实体1、3，通过P2=P3跨字段匹配，统一分配组ID G2
  • Mark：存在于三个实体，实体1和3通过P1匹配，实体2通过与实体1的P2跨字段校验匹配，三条记录统一分配组ID G3
• 组ID为客户唯一标识，同一客户的不同实体记录组ID相同，不同客户组ID不同

示例数据表及数据：

with 
sample as (
select 1 as entity, 'DAVID' as customer, 100 as P1, 'hjk' as P2, null as P3 from dual
union all
select 2 as entity, 'DAVID' as customer, 100 as P1, 'heeee' as P2, null as P3 from dual
union all
select 1 as entity, 'Lloyd' as customer, null as P1, 'dfe' as P2, null as P3 from dual
union all
select 3 as entity, 'LLOYD' as customer, null as P1, null as P2, 'dfe' as P3 from dual
union all
select 1 as entity, 'MARK' as customer, 300 as P1, 'abc' as P2, null as P3 from dual
union all
select 2 as entity, 'MARC' as customer, 0 as P1, null as P2, 'abc' as P3 from dual
union all
select 3 as entity, 'Mark' as customer, 300 as P1, 'texttt' as P2, null as P3 from dual
)
select * from sample;

通过WITH子句构建匹配关系、连通组，最终为每个客户分配唯一组ID，SQL代码如下：

with 
sample as (
select 1 as entity, 'DAVID' as customer, 100 as P1, 'hjk' as P2, null as P3 from dual
union all
select 2 as entity, 'DAVID' as customer, 100 as P1, 'heeee' as P2, null as P3 from dual
union all
select 1 as entity, 'Lloyd' as customer, null as P1, 'dfe' as P2, null as P3 from dual
union all
select 3 as entity, 'LLOYD' as customer, null as P1, null as P2, 'dfe' as P3 from dual
union all
select 1 as entity, 'MARK' as customer, 300 as P1, 'abc' as P2, null as P3 from dual
union all
select 2 as entity, 'MARC' as customer, 0 as P1, null as P2, 'abc' as P3 from dual
union all
select 3 as entity, 'Mark' as customer, 300 as P1, 'texttt' as P2, null as P3 from dual
),
-- 为每条记录生成唯一标识，避免因客户名大小写、字段空值导致的关联问题
record_ids as (
    select s.*, row_number() over () as rec_id from sample s
),
-- 构建所有有效匹配关系对：P1相同的记录、P2与P3匹配的记录
matches as (
    -- P1字段匹配的记录对
    select a.rec_id as rec_id1, b.rec_id as rec_id2
    from record_ids a
    join record_ids b on a.P1 = b.P1 and a.rec_id < b.rec_id
    where a.P1 is not null
    union all
    -- P2与P3跨字段匹配的记录对
    select a.rec_id as rec_id1, b.rec_id as rec_id2
    from record_ids a
    join record_ids b on a.P2 = b.P3 and a.rec_id < b.rec_id
    where a.P2 is not null and b.P3 is not null
),
-- 递归找出所有连通的记录组，将同一客户的所有记录归到同一根标识下
connected_groups(rec_id, root_id) as (
    select rec_id, rec_id as root_id from record_ids
    union all
    select c.rec_id, g.root_id
    from connected_groups c
    join matches m on c.rec_id = m.rec_id2
    join connected_groups g on g.rec_id = m.rec_id1
),
-- 为每个连通组分配唯一组ID
group_assignments as (
    select distinct rec_id, 
           'G' || dense_rank() over (order by root_id) as group_id
    from connected_groups
)
-- 关联原数据，输出带组ID的最终结果
select s.entity, s.customer, s.P1, s.P2, s.P3, ga.group_id
from sample s
join record_ids r on s.entity = r.entity 
                  and s.customer = r.customer 
                  and nvl(s.P1, -1) = nvl(r.P1, -1) 
                  and nvl(s.P2, '') = nvl(r.P2, '') 
                  and nvl(s.P3, '') = nvl(r.P3, '')
join group_assignments ga on r.rec_id = ga.rec_id
order by ga.group_id, s.entity;

逻辑说明 #

1. record_ids：给每条记录生成唯一ID，解决客户名大小写、字段空值导致的无法直接关联问题。
2. matches：构建两种核心匹配关系，覆盖题目要求的P1直接匹配和P2-P3跨字段匹配场景。
3. connected_groups：通过递归CTE将所有连通的记录（即同一客户的不同实体记录）归到同一个根ID下，形成客户的完整连通组。
4. group_assignments：利用dense_rank()为每个连通组生成唯一的组ID（G1、G2、G3）。
5. 最终关联原表数据，输出包含组ID的匹配结果。

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