基于Email与Fruit列匹配重叠重复记录的SQL查询优化需求

需求说明 #

现有包含email、fruits、ID字段的数据库表，需查询所有满足同一email下存在水果内容重叠的关联记录：对同一email的记录两两比对，只要任意两条的fruits有共同水果值（如"banana"与"apple;banana"共享"banana"），就返回该email下所有相关的关联记录。

示例场景 #

• 场景1：john@gmail.com的3条记录（fruits分别为apple、banana、apple;banana）需全部返回；
• 场景2：john@gmail.com的apple和banana两条记录需返回；
• 场景3：smith@gmail.com的apple和banana两条无重叠值的记录不应返回。

原SQL问题 #

当前使用的SQL无法处理场景3的边缘情况，仅通过分组计数筛选会误判无重叠的email记录：

select a.primaryemail, a.fruits
from default.customerdetails a
    inner join (select sub.primaryemail, count(sub.primaryemail)
    from default.customerdetails sub
    group by sub.primaryemail, sub.fruits
    having count(*) >1 ) sub on a.primaryemail=sub.primaryemail

优化后的SQL（适配Hive环境） #

WITH split_fruits AS (
    -- 将每条记录的分号分隔水果拆分为数组
    SELECT 
        primaryemail,
        fruits AS original_fruits,
        ID,
        split(fruits, ';') AS fruit_array
    FROM default.customerdetails
),
exploded_fruits AS (
    -- 展开数组，每条记录对应单个水果行
    SELECT 
        primaryemail,
        original_fruits,
        ID,
        fruit
    FROM split_fruits
    LATERAL VIEW explode(fruit_array) exploded AS fruit
),
shared_fruits AS (
    -- 筛选同一email下被多个不同记录共享的水果
    SELECT 
        primaryemail,
        fruit
    FROM exploded_fruits
    GROUP BY primaryemail, fruit
    HAVING COUNT(DISTINCT ID) > 1
)
-- 关联返回所有涉及共享水果的原记录
SELECT DISTINCT
    c.primaryemail,
    c.fruits
FROM default.customerdetails c
JOIN exploded_fruits ef
    ON c.primaryemail = ef.primaryemail
    AND c.ID = ef.ID
JOIN shared_fruits sf
    ON ef.primaryemail = sf.primaryemail
    AND ef.fruit = sf.fruit;

逻辑说明 #

1. 拆分与展开水果：先将分号分隔的fruits字段拆分为数组，再展开为单水果行，方便后续匹配；
2. 识别共享水果：通过分组统计，找出同一email下被多个不同ID记录使用的水果，这些水果就是重叠的标识；
3. 关联返回目标记录：将原表与展开后的水果表、共享水果表关联，确保仅返回存在水果重叠的email对应的所有相关记录，自动排除无重叠的场景（如场景3）。

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