sql - 在 postgresql 中搜索跨字段重复项并带回匹配的对

Question

我有一个联系人表。该表包含一个 mobile_phone 列和一个 home_phone 列。我想获取所有重复的联系人对，其中一对是共享电话号码的两个联系人。

请注意，如果联系人 A 的 mobile_phone 与联系人 B 的 home_phone 匹配，这也是重复的。这是应该匹配的三个联系人的示例。

contact_id|mobile_phone|home_phone|other columns such as email.......|...
-------------------------------------------------------------------------
111       |9748777777  |1112312312|..................................|...
112       |1112312312  |null      |..................................|...
113       |9748777777  |0001112222|..................................|...

具体来说，我想带回一个表，其中每一行都包含两个匹配联系人的contact_ids。例如，

||contact_id_a|contact_id_b||
||-------------------------||
||   145155   |   145999   ||
||   145158   |   145141   ||

在@Erwin here enter link description here的帮助下，我能够编写一个接近我想要实现的查询的查询，带回列表中与列表中的其他联系人共享电话号码的所有联系人的 contact_ids 列表。

SELECT c.contact_id
FROM   contacts c
WHERE  EXISTS (
   SELECT FROM contacts x
   WHERE (x.data->>'mobile_phone' is not null and x.data->>'mobile_phone' IN (c.data->>'mobile_phone', c.data->>'home_phone'))
       OR (x.data->>'home_phone' is not null and x.data->>'home_phone'   IN (c.data->>'mobile_phone', c.data->>'home_phone'))
   AND x.contact_id <> c.contact_id  -- except self
   );

输出只包含这样的contact_ids ...

||contact_id||
--------------
||  2341514 ||
||  345141  ||

如上所示，我想将匹配联系人的contact_ids 带回一行。

标签： sqlpostgresqlduplicates

Answer 1

一个简单的查询是使用ARRAY 重叠运算符&&：

SELECT c1.contact_id AS a, c2.contact_id AS b
FROM   contacts c1
JOIN   contacts c2 ON c1.contact_id < c2.contact_id
WHERE  ARRAY [c1.mobile_phone, c1.home_phone] && ARRAY[c2.mobile_phone, c2.home_phone];

该条件c1.contact_id < c2.contact_id不包括自加入和切换重复项。

但是，如果许多联系人以某种方式共享相同的号码，这种表示很快就会失控。

撇开：条件[INNER] JOIN和WHERE条件完全一样，而join_collapse_limit只涉及连接。看：

• 依靠条件连接大表的速度很慢