PostgreSQL中跨两张表查找相似行的技术问询

嘿，我来帮你搞定这个不同表之间行相似性查找的问题！先梳理下你的表结构和测试数据，再基于常见的相似性规则给出具体的实现方案：

你的表结构与测试数据 #

首先把你提供的DDL整理成代码块方便查看：

CREATE TABLE a ( id int, fname text, lname text, email text, phone text );
INSERT INTO a VALUES 
    (1, 'john', 'doe', 'john@gmail.com', null), 
    (2, 'peter', 'green', 'peter@gmail.com', null);

CREATE TABLE b ( id int, fname text, lname text, email text, phone text );
INSERT INTO b VALUES 
    (null, 'peter', 'glover', 'bob@gmail.com', '777'), 
    (null, null, 'green', 'peter@gmail.com', '666');

常见相似性规则与实现 #

假设我们采用以下典型的相似性规则（你可以根据实际需求调整）：

• 高度相似：两行的email完全匹配（非空时）
• 相似：fname完全匹配（非空时），或者lname完全匹配（非空时）
• 空字段不参与匹配判断

实现SQL查询 #

下面的SQL会按照上述规则匹配表a和表b的行，并标记相似性等级：

-- 第一部分：匹配高度相似（email完全一致）的行
SELECT 
    a.id AS a_id, 
    a.fname AS a_fname, 
    a.lname AS a_lname, 
    a.email AS a_email,
    b.id AS b_id, 
    b.fname AS b_fname, 
    b.lname AS b_lname, 
    b.email AS b_email,
    '高度相似（email匹配）' AS similarity_level
FROM a
JOIN b ON a.email = b.email
WHERE a.email IS NOT NULL AND b.email IS NOT NULL

UNION ALL

-- 第二部分：匹配相似（姓名字段匹配）的行，排除已被高度匹配的行
SELECT 
    a.id AS a_id, 
    a.fname AS a_fname, 
    a.lname AS a_lname, 
    a.email AS a_email,
    b.id AS b_id, 
    b.fname AS b_fname, 
    b.lname AS b_lname, 
    b.email AS b_email,
    '相似（姓名匹配）' AS similarity_level
FROM a
JOIN b ON 
    (a.fname = b.fname AND b.fname IS NOT NULL) 
    OR (a.lname = b.lname AND b.lname IS NOT NULL)
WHERE 
    -- 排除已经通过email匹配的行，避免重复
    NOT EXISTS (
        SELECT 1 FROM b b_match WHERE b_match.email = a.email
    )
    AND (a.fname IS NOT NULL OR a.lname IS NOT NULL);

查询结果说明 #

执行上述SQL后，你会得到以下结果：

1. 表a中id=2的peter green会和表b中第二行匹配，标记为高度相似（email匹配）
2. 如果有其他姓名匹配但email不匹配的行，也会被筛选出来标记为相似

扩展优化建议 #

如果需要更灵活的相似性判断（比如模糊匹配姓名、计算匹配得分），可以做以下调整：

• 使用LIKE进行模糊匹配，比如匹配姓氏的部分字符串：

  JOIN b ON a.lname LIKE CONCAT('%', b.lname, '%') OR b.lname LIKE CONCAT('%', a.lname, '%')
  

• 给不同匹配规则设置权重，计算综合相似得分：

  SELECT 
      *,
      (
          CASE WHEN a.email = b.email THEN 3 ELSE 0 END +
          CASE WHEN a.fname = b.fname THEN 2 ELSE 0 END +
          CASE WHEN a.lname = b.lname THEN 2 ELSE 0 END
      ) AS similarity_score
  FROM a, b
  ORDER BY similarity_score DESC;
  

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