如何聚集PostgreSQL表中与输入值或来自任何其他匹配行的值匹配的行？
我的PostgreSQL数据库中有一个如下所示的表
如果群集中的每个联系人与群集中的另一个联系人共享Contact_id_a或Contact_id_b值(或两者)，我如何带回该群集中的联系人？
在上面屏幕截图图像中的示例中，第1-6行将位于同一群集中，而第8行将不属于任何群集。
如何将SQL查询或SQL查询与Java代码结合使用来实现此目的？
对于上下文，此表列出了联系人列表中所有潜在的重复联系人。我们希望向列表所有者显示所有可能重复的联系人，以便用户可以手动管理这些重复项。以下是我的起始代码：
DuplicateCandidate firstDuplicate = db.sql("select * from duplicates where list_id = "+list_id+ " and ignore_duplicate is not true").first(DuplicateCandidate);
String sql = "select * from duplicates where list_id = "+list_id+ "and ignore_duplicate is not true "
+ "and (contact_id_a = ? or contact_id_b = ? or contact_id_a = ? or contact_id_b = ?";
List<DuplicateCandidate> groupOfDuplicates = db.sql(sql, firstDuplicate.contact_id_a,firstDuplicate.contact_id_a, firstDuplicate.contact_id_b, firstDuplicate.contact_id_b).results(DuplicateCandidate.class);
这将返回第一行和包含16247096或16247097的任何其他行，但不返回与第二个查询结果中的Contact_id匹配的其他重要行。
干杯。
解决方案
这样的群集是一个迭代过程，步骤数未知。我从未找到可以在递归查询中完成的解决方案。
我已经六年多没有从事CRM工作了，但是下面的函数类似于我们过去生成匹配组的方式。逐行执行此操作对于我们的工作负载来说执行得不够好，并且通过主机语言(例如使用JavaHashMap()和HashSet())来完成此操作，而倒排索引会造成非常混乱的代码。
假设此架构：
d contact_info
Table "public.contact_info"
Column | Type | Collation | Nullable | Default
------------------+---------+-----------+----------+---------
contact_id_a | bigint | | |
contact_id_b | bigint | | |
ignore_duplicate | boolean | | | false
list_id | integer | | | 496
select * from contact_info ;
contact_id_a | contact_id_b | ignore_duplicate | list_id
--------------+--------------+------------------+---------
16247096 | 16247097 | f | 496
16247096 | 16247098 | f | 496
16247096 | 16247099 | f | 496
16247097 | 16247098 | f | 496
16247097 | 16247099 | f | 496
16247098 | 16247099 | f | 496
16247094 | 16247095 | f | 496
(7 rows)
此函数创建两个临时表来保存中间群集，然后在不再可能进行群集时返回结果。
create or replace function cluster_contact()
returns table (clust_id bigint, contact_id bigint)
language plpgsql as $$
declare
last_count bigint := 1;
this_count bigint := 0;
begin
create temp table contact_match (clust_id bigint, contact_id bigint) on commit drop;
create index cm_1 on contact_match (contact_id, clust_id);
create index cm_2 on contact_match using hash (clust_id);
create temp table contact_hold (clust_id bigint, contact_id bigint) on commit drop;
with dedup as (
select distinct least(ci.contact_id_a) as clust_id,
greatest(ci.contact_id_b) as contact_id
from contact_info ci
where not ci.ignore_duplicate
)
insert into contact_match
select d.clust_id, d.clust_id from dedup d
union
select d.clust_id, d.contact_id from dedup d;
while last_count > this_count loop
if this_count = 0 then
select count(distinct cm.clust_id) into last_count from contact_match cm;
else
last_count := this_count;
end if;
with new_cid as (
select cm.contact_id as clust_id_old,
min(cm.clust_id) as clust_id_new
from contact_match cm
group by cm.contact_id
)
update contact_match
set clust_id = nc.clust_id_new
from new_cid nc
where contact_match.clust_id = nc.clust_id_old;
truncate table contact_hold;
insert into contact_hold
select distinct * from contact_match;
truncate table contact_match;
insert into contact_match
select * from contact_hold;
select count(distinct cm.clust_id) into this_count from contact_match cm;
end loop;
return query select * from contact_match order by clust_id, contact_id;
end $$;
我见过的开发人员面临的最大心理障碍之一是忽略包括contact_id与其自身的关系。这会导致不连续的处理和不必要地使左右两边复杂化的心理模型。
select * from cluster_contact();
clust_id | contact_id
----------+------------
16247094 | 16247094
16247094 | 16247095
16247096 | 16247096
16247096 | 16247097
16247096 | 16247098
16247096 | 16247099
(6 rows)
如果您需要澄清此解决方案中的任何步骤，或者它对您不起作用，请提供意见。
另外，请知道fuzzystrmatch中提供了Levenshtein，并且它工作正常。
如果您希望使用从1开始的顺序clust_id，请将函数中的return query更改为：
return query
select dense_rank() over (order by cm.clust_id) as clust_id,
cm.contact_id
from contact_match cm
order by clust_id, contact_id;
它将产生：
select * from cluster_contact();
clust_id | contact_id
----------+------------
1 | 16247094
1 | 16247095
2 | 16247096
2 | 16247097
2 | 16247098
2 | 16247099
(6 rows)
相关文章