时间戳

首页 > 解决方案 > 具有意外相似重复的 postgres 用户表导致另一个表具有混乱的外键，如何修复和合并外键？

sql - 具有意外相似重复的 postgres 用户表导致另一个表具有混乱的外键，如何修复和合并外键？

问题描述

对于我的应用程序，使用 Postgres ...

问题

每个用户应该与 N 个案例相关联，定义一对多的关系，但是由于错误的应用程序逻辑，用户通常在数据库中重复，导致任何给定的人都有多个 id。

鉴于大多数用户的这些类型几乎重复，这导致每个用户几乎总是由表中的 Y 个 id 表示users。

在这种情况下，几乎重复意味着两行最相似。这是一个近乎重复的例子。

|  id | first_name | last_name | str_adrr      | 
------------------------------------------------
|  1  | Mary       | Doe       | 124 Main Ave  | 
|  2  | Mary       | Doe       | 124 Main St   |

目标是删除所有几乎重复的用户，除了一个用户，留下一个用户，同时将所有相关案例与该单个用户相关联。最终在用户和案例之间建立一对多的关系。

我的方法

第一步

我对用户进行模糊匹配，并按 cluster_id 作为标识符对其进行分组。其中cluster_id用于表示分组本身；所有具有 cluster_id 的行都1被视为彼此重复。

这是users表格的示例

|  id | first_name | last_name | str_adrr      | group                   | cluster_id
-------------------------------------------------------------------------------------
|  1  | Mary       | Doe       | 124 Main Ave  | Mary Doe 124 Main Ave   | 1
|  2  | Mary       | Doe       | 124 Main St   | Mary Doe 124 Main Ave   | 1
|  7  | Mary       | Doe       | 124 Main Ave  | Mary Doe 124 Main Ave   | 1
|  4  | Mary       | Does      | 124 Main Ave  | Mary Doe 124 Main Ave   | 1
|  5  | James      | Smith     | 14 Street NW  |James Smith 14 Street NW | 2
|  6  | James      | Smith     | 14 Street NW  |James Smith 14 Street NW | 2
| 10  | James      | Smth      | 14 Street NW  |James Smith 14 Street NW | 2
| 11  | Paula      | James     | 21 River SW   | Paula James21 River SW  | 3
| 45  | Paula      | James     | 21 River SW   | Paula James21 River SW  | 3

给定另一个名为cases. 以下是该表中相关列的示例：

|  id | user_id
---------------
|  1  | 1  # corresponds to mary
|  2  | 2  # corresponds to mary
|  3  | 4  # corresponds to mary
|  4  | 7  # corresponds to mary
|  5  | 10 # corresponds to james
|  6  | 11 # corresponds to paula
|  7  | 45 # corresponds to paula
|  8  | 1  # corresponds to mary
|  9  | 10 # corresponds to james
|  10 | 10 # corresponds to james
|  11 | 6  # corresponds to james

user_id此cases表中对应id于users表中

一个 user_id 可以有很多（最多几千个）案例。

第2步

我加入了users和cases表

以下是结果表的示例users_cases：

|cluster_id| user_id| case_id
----------------------------------
|  1       | 1      | 1
|  1       | 1      | 8
|  1       | 2      | 2
|  1       | 4      | 3
|  1       | 7      | 4
|  2       | 10     | 5
|  2       | 10     | 9
|  2       | 10     | 10
|  2       | 6      | 11
|  3       | 11     | 6
|  3       | 11     | 7

第 3 步

我需要确定user_id给定cluster_id分组中的哪些与表中最多的案例相关联user_cases。

我能够这样做并最终得到了max_cluster_user一张具有以下形状的桌子

|cluster_id| user_id| case_id_count
-------------------------------------
| 1        | 1      | 2 
| 2        | 10     | 3 
| 3        | 11     | 1

要翻译。第一行表示对于cluster_id值为 的1，user_id具有最多个案数的 是 ，个案2的数量由case_id_count值为 的表示2。

第 4 步：我需要帮助的地方

然后我需要更新user_cases表格（或制作一个具有相同形状的新表格），以使组user_id中的每一行都相同cluster_id。结果应该是这样的

|cluster_id| user_id| case_id
----------------------------------
|  1       | 1      | 1
|  1       | 1      | 8
|  1       | 1      | 2
|  1       | 1      | 3
|  1       | 1      | 4
|  2       | 10     | 5
|  2       | 10     | 9
|  2       | 10     | 10
|  2       | 10     | 11
|  3       | 11     | 6
|  3       | 11     | 7

我不知道如何做到这一点。约束是它必须通过与 Postgresql 兼容的 SQL 来完成。

第 4 步的程序代码解决方案

我确实将其草拟为代码以在程序上考虑它，这可能会有所帮助。虽然我知道这不是一个可行的解决方案，因为有 > 500k 的记录，但这种类型的逻辑需要数天才能按原样运行。

# max_cluster_user refers to the table of the same name
for cluster in max_cluster_user: 
    # get the users within a specific cluster
    cluster_users = [user for user in users if user['cluster_id'] == cluster['cluster_id']]
    # users refers to the table of the same name
    for user in cluster_users:
        # get the cases associated with the given id
        user_cases = [case for case in cases if case['user_id'] == user['id']
        for user_case in user_cases:
            # update the user_id for a case
            user_case['user_id = cluster['user_id']

提前致谢

标签： sqlpostgresqlduplicatesbigdata

解决方案

我认为您只需要update加入第 4 步：

update user_cases uc
    set user_id = mcu.user_id
    from max_cluster_user mcu
    where mcu.cluster_id = uc.cluster_id and
          uc.user_id <> mcu.user_id;

推荐阅读