sql - 合并在与另一个表的关系中使用的重复表行

Question

我有以下表结构：

table_a
id | customer_id | product_id
---+-------------+------
 1 | c1          | p1
 2 | c1          | p1
 3 | c2          | p1

table_b
id | table_a_id  | attribute
---+-------------+------
 99 | 1          | a1
 98 | 2          | a2
 97 | 3          | a3

如您所见table_a，有重复值，我想合并它们。不幸的table_a PK是，也用于table_b.

最终结果应该是：

table_a
id | customer_id | product_id
---+-------------+------
 1 | c1          | p1
 3 | c2          | p1

table_b
id | table_a_id  | attribute
---+-------------+------
 99 | 1          | a1
 98 | 1          | a2
 97 | 3          | a3

我必须更新与 的table_b关系table_a，然后清除所有未使用的键table_a。

不幸的是，我想到的唯一查询真的很重，并且可以完成之前的数据库超时。table_a拥有 200k+ 条记录，并且table_b至少是其两倍。

我的想法是：

• 加入table_a,table_b以获得：(table_b_id, table_a_customer_id, table_a_product_id)
• 获取table_a. （为了获得我刚刚使用的id权利table_amin("id")
• 内连接以上两个并使用结果进行更新table_b。

标签： sqlpostgresqlduplicatessql-updatesql-delete

Answer 1

这是使用公用表表达式的一种选择：

with 
    ta as (
        select ta.*, min(id) over(partition by customer_id, product_id) min_id
        from table_a ta
    ),
    upd as (
        update table_b tb
        set table_a_id = ta.min_id
        from ta
        where tb.table_a_id = ta.id and ta.id <> ta.min_id
    )
delete from table_a ta1
using ta
where 
    ta1.customer_id = ta.customer_id
    and ta1.product_id = ta.product_id
    and ta1.id > ta.id

第一个 CTE 将目标id与table_a. 然后，我们使用该信息来更新table_b. 最后，我们删除 中的重复行table_a，只保留最早的id。