使用PostgreSQL为一组符合条件的记录生成集群字段

Question

我在PostgreSQL 9.4中有一个表，其中包含以下字段ID（主键）、客户名称、手机和电子邮件。ID列对于记录是唯一的，但不一定标识唯一的个人。一个客户可以有多个不同姓名和/或不同手机或电子邮件的记录，每次都有一个唯一的ID链接。

我有一个要求，我需要一个新的计算列（使用SQL查询），其名称Cluster_ID将根据名称或移动或电子邮件的匹配唯一地识别客户，即如果一个记录的名称、移动或电子邮件中的任何一个与另一个记录匹配，那么这些记录应该分配相同的Cluster_ID。该Cluster_ID对于匹配记录集应该是唯一的，并且每次执行查询时最好是相同的。

我为Postgres创建了一个示例DDL（可以在SQL上使用fiddle.com进行测试）：

CREATE TABLE Customer (
    ID  integer,
    Name varchar(30),
    Mobile  varchar(20),
    Email  varchar(50)
);

INSERT INTO Customer (ID, Name, Mobile, Email) VALUES
    (1, 'Tim', '9876728382', '[e-mail]'),
    (2, 'John', '9845323453', '[e-mail]'),
    (3, 'Tim', '8265748319', '[e-mail]'),
    (4, 'John Snow', '9845323453', NULL),
    (5, 'Timmothy', '8265748319', '[e-mail]'),
    (6, 'John', '8345908112', '[e-mail]'),
    (7, 'Tim M. Jacob', NULL, '[e-mail]'),
    (8, 'John P. Snow', '8345908112', NULL),
    (9, 'Rack', '7654783949', '[e-mail]'),
    (10, 'Racky Dsouza', '9934364837', '[e-mail]'),
    (11, 'Rock M. Dsouza', '9934364837', '[e-mail]'),
    (12, 'John Snowden', '8463865392', '[e-mail]')
;

查看下面的链接，了解SQL查询的预期输出。请注意，我突出显示了与不同记录的其他值相匹配的值（背景为淡黄色）。

https://docs.google.com/spreadsheets/d/1IjLfCuyKmizw0ywvDpGO_e08ATlSnlPr__UBWUsVCV0/pubhtml?gid=0

为一组记录分配的群集ID最好是相同的，这些记录具有来自Name、Mobile或email的匹配值之一。

Answer 1

实际上，你正在尝试将一个集合划分成不相交的集合一个想法是使用集合的代表对表进行分区，并实现Find（element）函数，为给定的表元素（行）确定一个不同的集合代表有关详细信息，请参见此链接：不相交集合数据结构

一种常见的方法是从每个集合中选择一个固定的元素，称为其代表，以代表整个集合。然后，Find（x）返回x所属集合的代表

假设我们将给定不相交子集的代表定义为该子集中所有ID元素的最小ID值。这个代表值将成为我们的集群id在这种情况下，find（x）函数可以通过这种方式使用带有查询（公共表表达式）的PostgreSQL来实现（下面的示例确定了一个不相交的子集，代表id=5的行）：

with recursive xxx( id, name, mobile, email ) AS(
    select *
    from customer
    where id = 5
    union 
    select c.* 
    from customer  c
    join xxx x
    on c.name = x.name or c.mobile = x.mobile or c.email = x.email
)
select min(id) from xxx

min |
----|
1   |

上述查询可以用作子查询，以确定表中所有行的set代表，方式如下：

select q.*,
      ( 
        with recursive xxx( id, name, mobile, email ) AS(
            select *
            from customer
            where id = q.id
            union 
            select c.* 
            from customer  c
            join xxx x
            on c.name = x.name or c.mobile = x.mobile or c.email = x.email
        )
        select min( id ) 
        from xxx
       ) as cluster_id
from customer q
order by cluster_id, id;

id |name           |mobile     |email                   |cluster_id |
---|---------------|-----------|------------------------|-----------|
1  |Tim            |9876728382 |[e-mail]           |1          |
3  |Tim            |8265748319 |[e-mail]            |1          |
5  |Timmothy       |8265748319 |[e-mail]   |1          |
7  |Tim M. Jacob   |           |[e-mail]   |1          |
2  |John           |9845323453 |[e-mail]          |2          |
4  |John Snow      |9845323453 |                        |2          |
6  |John           |8345908112 |[e-mail] |2          |
8  |John P. Snow   |8345908112 |                        |2          |
9  |Rack           |7654783949 |[e-mail]         |9          |
10 |Racky Dsouza   |9934364837 |[e-mail]         |9          |
11 |Rock M. Dsouza |9934364837 |[e-mail]    |9          |
12 |John Snowden   |8463865392 |[e-mail]      |12         |

这可能适用于小型数据集，但如果您的表有许多记录，那么这种查询的速度可能会很糟糕。

如何改进这个算法或实现一个更好的算法的一些技巧你可以在这里找到：分区细化，这很可能需要实现适当的数据结构（双链表或数组，取决于算法），在这种情况下SQL表不是最佳选择。