根据多个条件从表中删除重复项并保留到其他表

admin

根据多个条件从表中删除重复项并保留到其他表

sql

我有一个taccounts表列的喜欢account_id(PK)，login_name，password，last_login。现在，我必须根据新的业务逻辑删除一些重复的条目。因此，重复的帐户将使用相同email
或相同（login_name＆password）。具有最新登录名的帐户必须保留。

这是我的尝试（某些电子邮件值为null和空白）

DELETE
FROM taccounts
WHERE email is not null and char_length(trim(both ' ' from email))>0 and last_login NOT IN
(
SELECT MAX(last_login)
FROM taccounts
WHERE email is not null and char_length(trim(both ' ' from email))>0 
GROUP BY lower(trim(both ' ' from email)))

与login_name和类似password

DELETE
FROM taccounts
WHERE last_login NOT IN
(
SELECT MAX(last_login)
FROM taccounts
GROUP BY login_name, password)

有没有更好的方法或任何方式来组合这两个单独的查询？

还有一些其他表具有account_id外键。 如何更新这些表的更改？ `我正在使用PostgreSQL 9.2.1

编辑
：某些电子邮件值为null，其中一些为blank（’‘）。因此，如果两个帐户具有不同的login_name和密码，并且其电子邮件为空或空白，则必须将它们视为两个不同的帐户。

阅读 160

2021-06-07

共1个答案

admin

幸运的是，您正在运行PostgreSQL。DISTINCT ON应该使这相对容易：

由于您将删除大多数行（约90％重复），并且表很可能很容易放入RAM，因此我选择了以下方法：

SELECT 将尚存的行放入临时表中。
重新路由引用列。
DELETE 基表中的所有行。
重新INSERT幸存者。

蒸馏剩余的行

CREATE TEMP TABLE tmp AS
SELECT DISTINCT ON (login_name, password) *
FROM  (
   SELECT DISTINCT ON (email) *
   FROM   taccounts
   ORDER  BY email, last_login DESC
   ) sub
ORDER  BY login_name, password, last_login DESC;

有关更多DISTINCT ON：

在每个GROUP BY组中选择第一行？

要删除两个不同条件的重复项，我只使用一个子查询，一个接一个地应用这两个规则。第一步是使用最新的保留帐户last_login，因此这是“可序列化的”。

检查结果并测试合理性。

SELECT * FROM tmp;

在会话结束时会自动删除一个临时表。在pgAdmin（您似乎正在使用）中，会话一直存在，只要您在其中创建了临时表的编辑器窗口中打开即可。

替代查询以更新“重复项”的定义

SELECT *
FROM   taccounts t
WHERE  NOT EXISTS (
   SELECT 1
   FROM   taccounts t1
   WHERE (
           NULLIF(t1.email, '') = t.email OR 
           (NULLIF(t1.login_name, ''), NULLIF(t1.password, ''))
         = (t.login_name, t.password)
         )
   AND   (t1.last_login, t1.account_id) > (t.last_login, t.account_id)
   );

这不会在任何“重复”列NULL中将字符串（''）视为空白或将其视为空。

行表达式(t1.last_login,t1.account_id)考虑了两个重复可能共享相同的可能性last_login。account_id在这种情况下，我会选择更大的一个-这是独特的，因为它是PK。

如何识别所有传入的FK

SELECT c.confrelid::regclass::text AS referenced_table
      ,c.conname AS fk_name
      ,pg_get_constraintdef(c.oid) AS fk_definition
FROM   pg_attribute a 
JOIN   pg_constraint c ON (c.conrelid, c.conkey[1]) = (a.attrelid, a.attnum)
WHERE  c.confrelid = 'taccounts '::regclass   -- (schema-qualified) table name
AND    c.contype  = 'f'
ORDER  BY 1, contype DESC;

或者，您可以Dependents在选择pgAdmin的对象浏览器的右侧窗口中检查骑手taccounts。

重新路由到新的母版

如果你有表引用taccounts（ 传入的 外键来 taccounts），你将要更新所有这些领域，之前你删除受骗者。
将它们全部重新路由到新的主行：

UPDATE referencing_tbl r
SET    referencing_column = tmp.reference_column
FROM   tmp
JOIN   taccounts t1 USING (email)
WHERE  r.referencing_column = t1.referencing_column
AND    referencing_column IS DISTINCT FROM tmp.reference_column;

UPDATE referencing_tbl r
SET    referencing_column = tmp.reference_column
FROM   tmp
JOIN   taccounts t2 USING (login_name, password)
WHERE  r.referencing_column = t1.referencing_column
AND    referencing_column IS DISTINCT FROM tmp.reference_column;

进去杀人

现在，这些骗子不再有链接。进去杀人。

ALTER TABLE taccounts DISABLE TRIGGER ALL;
DELETE FROM taccounts;
VACUUM taccounts;
INSERT INTO taccounts
SELECT * FROM tmp;
ALTER TABLE taccounts ENABLE TRIGGER ALL;

我在操作过程中禁用了所有触发器。这样可以避免在操作过程中检查参照完整性。重新激活触发器后，一切都应该正常。我们已经处理了上面所有 传入的
FK。由于您没有并发访问权限并且所有值以前都在那里，因此保证传出 FK声音良好。

2021-06-07