sql - 如果任何字段匹配，如何对 postgres 表的结果进行分组？

Question

我有一个 postgresql 记录表，其中每条记录在该表中都有一条记录与三个可能字段中的至少一个匹配。

id |   name   |       email       | phone | product
----------------------------------------------------
 1 |  Rob A   | foo@bar.com       | 123   | 102
 2 |  Rob B   | foo@bar.com       | 323   | 102
 3 |  Rob C   | foo@bcr.com       | 123   | 102
 4 |  Rob A   | foo@bdr.com       | 523   | 102
 5 |  Rob A   | foo@bar.com       | 123   | 104
 6 |  Cat A   | liz@bar.com       | 999   | 102
 7 |  Cat B   | lid@bar.com       | 999   | 102
 8 |  Cat A   | lib@bar.com       | 991   | 102

我想对表格进行分组，其中“产品”匹配的任何表格以及其他三个字段(姓名、电子邮件、电话)中的任何。所以这些团体最终会看起来像

id |   name   |       email       | phone | product
----------------------------------------------------
 1 |  Rob A   | foo@bar.com       | 123   | 102
 2 |  Rob B   | foo@bar.com       | 323   | 102
 3 |  Rob C   | foo@bcr.com       | 123   | 102
 4 |  Rob A   | foo@bdr.com       | 523   | 102

 5 |  Rob A   | foo@bar.com       | 123   | 104

 6 |  Cat A   | liz@bar.com       | 999   | 102
 7 |  Cat B   | lid@bar.com       | 999   | 102
 8 |  Cat A   | lib@bar.com       | 991   | 102

有什么办法吗？

Answer 1

如果我们INNER JOIN 表本身就像

SELECT t1.id id1,
       t2.id id2
       FROM elbat t1
            INNER JOIN elbat t2
                       ON t2.product = t1.product
                          AND (t2.name = t1.name
                                OR t2.email = t1.email
                                 OR t2.phone = t1.phone)
                          AND t2.id > t1.id;

我们将在 id1 中有超过一行的“组”的最低 ID。对于每个 id1，“组”的其他成员都在 id2 中。

也就是说，我们可以连接上面查询的结果，以便为每一行连接“组”的最低 ID。由于在其自身上构建“组”的行或已经具有“组”最低 ID 的行将找不到合作伙伴行，因此我们必须 LEFT JOIN。我们现在可以使用加入的最低 ID，或者行本身的 ID(如果没有加入任何伙伴行)作为使用 coalesce() 的“组”ID。

SELECT coalesce(x.id1, t.id) groupid,
       t.*
       FROM elbat t
            LEFT JOIN (SELECT t1.id id1,
                              t2.id id2
                              FROM elbat t1
                                   INNER JOIN elbat t2
                                              ON t2.product = t1.product
                                                 AND (t2.name = t1.name
                                                       OR t2.email = t1.email
                                                        OR t2.phone = t1.phone)
                                                 AND t2.id > t1.id) x
                      ON x.id2 = t.id
       ORDER BY coalesce(x.id1, t.id);

由于我们也按“组”ID 排序，我们可以在任何应用程序中按顺序遍历结果，并且知道，如果“组”ID 发生变化，我们正在读取新“组”的第一行。

db<>fiddle