PostgreSQL中基于多条件规则跨type去重的实现方法

测试数据建表SQL #

with t as (
    select *
    from (
        (
            values ('james', '801xxxxxxx', 'james@gmail.com', 'ca', 'a'),
            ('robert', '714xxxxxxx', '', 'ca', 'a'),
            ('william', '', 'william@gmail.com', '', 'a'),
            ('maria', '1234567890', 'maria@gmail.com', '', 'a'),
            ('richard', '', 'richard@gmail.com', '', 'a'),
            ('', '', 'james@gmail.com', '', 'b'),
            ('maria', '1234567890', '', '', 'b'),
            ('robert', '', '', 'ca', 'b')
        )
    ) t (first_name, phone, email, state, "type")
), a_t as (
    select *
    from t
    where "type" = 'a'
), b_t as (
    select *
    from t
    where "type" = 'b'
)
select *
from t

去重需求 #

需去除不同type间的重复数据，重复判定满足以下任一规则：

• 规则1：email字段匹配（空值不视为匹配）
• 规则2：phone与first_name字段同时匹配（两个字段均非空且对应相等）
• 规则3：state与first_name字段同时匹配（两个字段均非空且对应相等）

尝试过的无效方案 #

以下是多种尝试过的去重SQL，但均未得到预期结果：

with t as (
select *
    from (
        (
            values ('james', '801xxxxxxx', 'james@gmail.com', 'ca', 'a'),
            ('robert', '714xxxxxxx', '', 'ca', 'a'),
            ('william', '', 'william@gmail.com', '', 'a'),
            ('maria', '1234567890', 'maria@gmail.com', '', 'a'),
            ('richard', '', 'richard@gmail.com', '', 'a'),
            ('', '', 'james@gmail.com', '', 'b'),
            ('maria', '1234567890', '', '', 'b'),
            ('robert', '', '', 'ca', 'b')
        )
    ) t (first_name, phone, email, state, "type")
),
dedupe_one as (
    select distinct on (email)
        first_name, phone, email, state, "type"
    from t
),
dedupe_two as (
    select distinct on (phone, first_name)
        first_name, phone, email, state, "type"
    from t
),
dedupe_three as (
    select distinct on (state, first_name)
        first_name, phone, email, state, "type"
    from t
),
dedupe_four as (
    select distinct on (email) *
    from t
    union
    select distinct on (phone, first_name) *
    from t
    union
    select distinct on (state, first_name) *
    from t
),
dedupe_five as (
    select distinct on (email) *
    from (
        select distinct on (phone, first_name) *
        from (
            select distinct on (state, first_name) *
            from t
        ) foo2
    ) foo
)
select *
from dedupe_five

正确实现方案 #

核心思路：先识别跨type的重复记录组，为每组设置优先级（优先保留type='a'的记录），最后筛选出每组内优先级最高的记录。

具体SQL #

with t as (
    select *
    from (
        values 
            ('james', '801xxxxxxx', 'james@gmail.com', 'ca', 'a'),
            ('robert', '714xxxxxxx', '', 'ca', 'a'),
            ('william', '', 'william@gmail.com', '', 'a'),
            ('maria', '1234567890', 'maria@gmail.com', '', 'a'),
            ('richard', '', 'richard@gmail.com', '', 'a'),
            ('', '', 'james@gmail.com', '', 'b'),
            ('maria', '1234567890', '', '', 'b'),
            ('robert', '', '', 'ca', 'b')
    ) t (first_name, phone, email, state, "type")
),
-- 给每条记录生成唯一ID，方便关联匹配
record_ids as (
    select *, row_number() over () as rid
    from t
),
-- 找出所有跨type的重复记录对
duplicate_pairs as (
    select distinct
        r1.rid as rid1,
        r2.rid as rid2
    from record_ids r1
    join record_ids r2 on r1."type" != r2."type"
    where 
        -- 规则1：非空email匹配
        (r1.email != '' and r1.email = r2.email)
        -- 规则2：非空phone+first_name同时匹配
        or (r1.phone != '' and r1.first_name != '' and r1.phone = r2.phone and r1.first_name = r2.first_name)
        -- 规则3：非空state+first_name同时匹配
        or (r1.state != '' and r1.first_name != '' and r1.state = r2.state and r1.first_name = r2.first_name)
),
-- 递归找出所有关联的重复记录组
connected_components as (
    select rid as member, rid as group_id from record_ids
    union all
    select cc.member, dp.rid2 as group_id
    from connected_components cc
    join duplicate_pairs dp on cc.group_id = dp.rid1
    where cc.member != dp.rid2
),
-- 为每个记录确定最终的组ID（取组内最小rid作为组标识）
grouped_records as (
    select 
        t.*,
        min(group_id) over (partition by member) as final_group_id
    from record_ids t
    join connected_components cc on t.rid = cc.member
),
-- 为每组记录设置优先级，type='a'优先
ranked_records as (
    select 
        *,
        row_number() over (
            partition by final_group_id 
            order by case when "type" = 'a' then 1 else 2 end
        ) as rn
    from grouped_records
)
-- 筛选每组中优先级最高的记录
select first_name, phone, email, state, "type"
from ranked_records
where rn = 1
order by "type", first_name;

预期结果 #

执行后将得到以下结果：

所有type='b'的重复记录均被去除，因为它们与type='a'的记录满足任一去重规则。

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