主页 / dba / 问题 / 226901
Accepted
Charlie Clark
Asked: 2019-01-12 05:26:05 +0800 CST

如何从表中删除部分重复项

  • 772

我有一个数据库，其中包含一些错误的结果，这些结果本质上是重复的，但在技术上并不重复。结构是这样的：

id_page (PK), id_site, label, create_date

每个标签的每个站点应该只有一个条目，但事实证明我有一些重复的site和label，其中id_page和create_date是不同的。我想删除最低的行create_date。

我想我已经想出了一个解决方案，但我会很感激反馈。

WITH duplicates as (
   SELECT id_page, id_site, count(id_site) over (partition by id_site) as ct, 
    min("create_date") over (partition by id_site) as dt
   from pages
   where label = '2018-12-15'
   )
DELETE from pages
where id_page in (
   select p.id_page
   from duplicates as d
   join pages as p on (p.id_page = d.id_page
                   and p.create_date = d.dt)
WHERE ct = 2
);
postgresql delete

2 个回答

  1. Best Answer
    WITH duplicates as 
(
    SELECT id_page, id_site,
           row_number() over (partition by id_site order by id_site, create_date) rn
    FROM   pages
    WHERE  label = '2018-12-15'
)
DELETE FROM pages 
WHERE  id_page IN (SELECT id_page 
                   FROM   duplicates
                   WHERE  rn > 1);
    • 2

  2. 对于给定的信息，假设所有列NOT NULL：

    DELETE FROM pages p
WHERE  label = '2018-12-15'
AND    EXISTS (
   SELECT FROM page
   WHERE  label       = p.label
   AND    id_site     = p.id_site
   AND    create_date > p.create_date
   );

    逻辑，用简单的英语：

    删除具有给定label的行，其中具有相同但后来label存在的行。id_sitecreate_date

    这使该行保留每组对等点的最新日期（删除 0-n 个重复项）。

    如果可以有相同的重复项create_date：

    DELETE FROM pages p
WHERE  label = '2018-12-15'
AND    EXISTS (
   SELECT FROM page
   WHERE  label       =  p.label
   AND    id_site     =  p.id_site
   AND    (create_date, id_page) > (p.create_date, p.id_page) --!
   );

    ...从关系上create_date保持与更大的行id_page。

    注意行值比较！看，解释：

    由于id_page是 PK（正如评论中所揭示的），这是明确的。如果有疑问，第二个查询是安全的选择，只是稍微慢一点。

    为了加快速度，索引将(label, id_site)是完美的——按此顺序使用索引表达式。（可能不需要为一次性操作创建它。）

    快速无索引

    DELETE FROM pages p
USING (
   SELECT id_page
        , row_number() OVER (PARTITION BY id_site
                             ORDER BY create_date DESC, id_page DESC) AS rn
   FROM   pages
   WHERE  label = '2018-12-15'
   ) del
WHERE  p.id_page = del.id_page 
AND    del.rn > 1;

    与 McNets 查询类似，但create_date像上面的第二个查询一样打破了可能的关系。

    • 2

相关问题