sqlite - 将所有 dup-references 更改为 MAX(id) 的引用

Question

这是一个关于sqlite的问题。

customers (id INTEGER PRIMARY KEY, name TEXT)
orders (id INTEGER PRIMARY KEY, cid INTEGER)

我想删除 customers 中的所有重复条目(保留 MAX(id) 条目)。但在此之前，我想将 orders 中的所有引用更改为相应的 MAX(id) 值。

我认为这是正确的做法:

UPDATE orders
SET cid = (SELECT  MAX(c.id)
           FROM customers c
           JOIN (SELECT  name, id
                 FROM  customers
                 WHERE id = orders.cid) q
           ON c.name = q.name
           GROUP BY c.name);

DELETE FROM customers
WHERE id NOT IN (SELECT MAX(id)
                 FROM customers
                 GROUP BY name);

但是，对于大约 10,000 个客户来说，第一个查询尤其慢。有没有更快的方法来做到这一点？

一些数字:我们有 120,000 名客户，其中大约 120,000 名。 30,000 的 COUNT(*) > 1(当按名称分组时)。除此之外，我们还有 200,000 个订单。完成上述查询大约需要20分钟。

Answer 1

使用 ID 临时表可能会获得更好的性能。据我了解，SQLite 在执行此查询时遇到问题，因为每次更新和删除时，客户都会发生变化。请注意，这种方法最适合事务。

BEGIN Transaction;

<罢工> 创建临时表 TempCustomers As SELECT id, MAX(id) as MaxId 来自客户 按名称分组；

CREATE TEMP TABLE TempCustomers AS 
    SELECT k.id, q.MaxId 
    FROM customers k JOIN 
       (SELECT MAX(d.id) as MaxId, d.name FROM customers d GROUP BY d.name)
    q ON q.name = k.name;

UPDATE orders
    SET cid = (SELECT MaxId
       FROM TempCustomers c
       WHERE id = orders.cid);

DELETE FROM customers
    WHERE id NOT IN (SELECT MaxId
             FROM TempCustomers);

COMMIT;

当您断开连接时，您的临时表将从内存中删除。或者您可以使用 DROP Temp Table如果你想保持连接而不占用内存。

编辑:随着评论的发展而建议的 final方法。

首先，向orders.cid添加索引。然后使用主键创建临时表，并将 id 交换插入其中(而不是动态创建)。最后，执行清理。

BEGIN Transaction;

CREATE TEMP TABLE TempCustomers
   (Id Integer PRIMARY KEY,
    MaxId Integer);

INSERT INTO TempCustomers SELECT k.id, q.MaxId 
    FROM customers k JOIN 
       (SELECT MAX(d.id) as MaxId, d.name FROM customers d GROUP BY d.name)
    q ON q.name = k.name;

UPDATE orders
    SET cid = (SELECT MaxId
       FROM TempCustomers c
       WHERE id = orders.cid);

DELETE FROM customers
    WHERE id NOT IN (SELECT MaxId
             FROM TempCustomers);

DROP TABLE TempCustomers;

COMMIT;