时间戳

首页 > 解决方案 > Postgres：通过通用电子邮件地址将帐户聚合为一个身份

sql - Postgres：通过通用电子邮件地址将帐户聚合为一个身份

问题描述

我正在建立一个用户目录，其中：

我想知道的是，如何通过常用的电子邮件地址将这些帐户聚合为单个身份？

例如，假设我有两个服务，A并且B. 对于每项服务，我都有一个将帐户与一个或多个电子邮件地址相关联的表格。

因此，如果服务A有这些帐户电子邮件地址：

account_id | email_address
-----------|--------------
1          | a@foo.com
1          | b@foo.com
2          | c@foo.com

并且服务B具有以下帐户电子邮件地址：

account_id | email_address
-----------|--------------
3          | a@foo.com
3          | a@bar.com
4          | d@foo.com

我想创建一个表格，将这些帐户的电子邮件地址聚合成一个用户身份：

user_id | email_address
--------|--------------
X       | a@foo.com
X       | b@foo.com
X       | a@bar.com
Y       | c@foo.com
Z       | d@foo.com

如您所见， account 1from serviceA和 account 2from serviceB已合并为一个 common user X，基于 common email address a@foo.com。这是一个动画视觉效果：

动画视觉

我能找到的最接近的答案是这个，我怀疑该解决方案是递归 CTE，但鉴于输入和引擎不同，我无法实现它。

澄清：我正在寻找一种可以处理任意数量的服务的解决方案，因此输入表可能会更好：

service_id | account_id | email_address
-----------|------------|--------------
A          | 1          | a@foo.com
A          | 1          | b@foo.com
A          | 2          | c@foo.com
B          | 3          | a@foo.com
B          | 3          | a@bar.com
B          | 4          | d@foo.com

标签： sqlpostgresqlgraph

解决方案

demo1:db<>fiddle , demo2:db <>fiddle

WITH combined AS (
    SELECT
        a.email as a_email,
        b.email as b_email,
        array_remove(ARRAY[a.id, b.id], NULL) as ids
    FROM 
        a
    FULL OUTER JOIN b ON (a.email = b.email)
), clustered AS (
    SELECT DISTINCT
        ids
    FROM (
        SELECT DISTINCT ON (unnest_ids) 
            *, 
            unnest(ids) as unnest_ids 
        FROM combined
        ORDER BY unnest_ids, array_length(ids, 1) DESC
    ) s
)
SELECT DISTINCT
    new_id, 
    unnest(array_cat) as email
FROM (
    SELECT
        array_cat(
            array_agg(a_email) FILTER (WHERE a_email IS NOT NULL), 
            array_agg(b_email) FILTER (WHERE b_email IS NOT NULL)
        ), 
        row_number() OVER () as new_id
    FROM combined co
    JOIN clustered cl
    ON co.ids <@ cl.ids
    GROUP BY cl.ids
) s

分步说明：

为了解释，我将使用这个数据集。这比你的要复杂一点。它可以更好地说明我的步骤。有些问题不会出现在你的较小的集合中。将字符视为电子邮件地址的变量。

表 A：

| id | email |
|----|-------|
|  1 |     a |
|  1 |     b |
|  2 |     c |
|  5 |     e |

表 B

| id | email |
|----|-------|
|  3 |     a |
|  3 |     d |
|  4 |     e |
|  4 |     f |
|  3 |     b |

热电偶combined：

加入同一电子邮件地址上的两个表以获得接触点。相同 ID 的 ID 将连接到一个数组中：

|   a_email |   b_email | ids |
|-----------|-----------|-----|
|    (null) | a@bar.com |   3 |
| a@foo.com | a@foo.com | 1,3 |
| b@foo.com |    (null) |   1 |
| c@foo.com |    (null) |   2 |
|    (null) | d@foo.com |   4 |

CTE clustered（对不起名字......）：

目标是将所有元素完全放在一个数组中。在combined您可以看到，例如，当前有更多带有元素的数组4:{5,4}和{4}.

首先按数组的长度对行进行排序，ids因为DISTINCT后者应该采用最长的数组（因为持有触摸点{5,4}而不是{4}）。

然后数组得到过滤的基础unnest。ids这结束于：

| a_email | b_email | ids | unnest_ids |
|---------|---------|-----|------------|
|       b |       b | 1,3 |          1 |
|       a |       a | 1,3 |          1 |
|       c |  (null) |   2 |          2 |
|       b |       b | 1,3 |          3 |
|       a |       a | 1,3 |          3 |
|  (null) |       d |   3 |          3 |
|       e |       e | 5,4 |          4 |
|  (null) |       f |   4 |          4 |
|       e |       e | 5,4 |          5 |

过滤后DISTINCT ON

| a_email | b_email | ids | unnest_ids |
|---------|---------|-----|------------|
|       b |       b | 1,3 |          1 |
|       c |  (null) |   2 |          2 |
|       b |       b | 1,3 |          3 |
|       e |       e | 5,4 |          4 |
|       e |       e | 5,4 |          5 |

我们只对ids具有生成的唯一 id 集群的列感兴趣。所以我们只需要一次。这是最后的工作DISTINCT。所以CTEclustered导致

| ids |
|-----|
|   2 |
| 1,3 |
| 5,4 |

现在我们知道哪些 id 被组合并应该共享它们的数据。ids现在我们根据原始表加入集群。由于我们在 CTE 中完成了此操作，因此combined我们可以重用这部分（这就是它被外包到单个 CTE 中的原因：在此步骤中我们不再需要两个表的另一个连接）。JOIN 运算符<@表示：如果 的“接触点”数组combined是 的 id 集群的子组，则加入clustered。这产生：

| a_email | b_email | ids | ids |
|---------|---------|-----|-----|
|       c |  (null) |   2 |   2 |
|       a |       a | 1,3 | 1,3 |
|       b |       b | 1,3 | 1,3 |
|  (null) |       d |   3 | 1,3 |
|       e |       e | 5,4 | 5,4 |
|  (null) |       f |   4 | 5,4 |

现在我们可以使用集群 ID（最右边的列）对电子邮件地址进行分组。

array_agg聚合一列array_cat的邮件，将两列的电子邮件数组连接成一个大电子邮件数组。

由于存在电子邮件所在的列，因此我们可以在使用该子句NULL进行聚类之前过滤掉这些值。FILTER (WHERE...)

到目前为止的结果：

| array_cat |
|-----------|
|         c |
| a,b,a,b,d |
|     e,e,f |

现在我们将所有电子邮件地址分组为一个 ID。我们必须生成新的唯一 ID。这就是窗口函数 row_number的用途。它只是将行数添加到表中：

| array_cat | new_id |
|-----------|--------|
|         c |      1 |
| a,b,a,b,d |      2 |
|     e,e,f |      3 |

最后一步是到unnest数组中获取每个电子邮件地址的一行。由于数组中仍有一些重复项，我们也可以在此步骤中使用 a 消除它们DISTINCT：

| new_id | email |
|--------|-------|
|      1 |     c |
|      2 |     a |
|      2 |     b |
|      2 |     d |
|      3 |     e |
|      3 |     f |

推荐阅读