结构化查询语言/消除重复数据

挑战

有时人们会发现他们的数据库中存在损坏或不需要的数据，例如：他们在创建表时忘记创建主键，并且在主键列中插入了相同的值，或者他们发现一个或多个列的组合中的值不是唯一的 - 违反了业务规则。这种情况通常在他们发出 ALTER TABLE ... ADD PRIMARY KEY (...) 或 CREATE INDEX index_1 ON table_1 (col_1, col_2) 命令时被发现。

在这种情况下，必须纠正数据，或者必须删除一些行。由于第一种情况高度依赖于具体情况，因此我们将重点介绍第二种操作。一般来说，SQL 命令将由两部分组成：DELETE 命令和第二部分，用于识别待删除的行。在复杂的情况下，可能需要使用多个 SQL 命令（根据定义始终是声明性的） - 可能是带有循环遍历受影响行的游标，以及根据不同列中的值进行额外的操作。

DELETE              -- the DELETE command needs no additional specification
FROM   mytable
WHERE ...           -- identify the unwanted rows
;

我们在这里讨论的解决方案与结构化查询语言/检索每个组的前 N 行中的解释密切相关。在那里，我们在组中定位特定的行。这里也必须这样做，因为我们只想删除专门的行。每个受影响的组中必须保留至少一行。

我们在本页使用相同的表 product。我们将消除除了其中产品价格与同一产品组中任何其他价格相同的一行以外的所有行。目标是使每一行都具有产品组和价格的唯一组合。

识别受影响的行

针对这种情况的第一种方法可能是使用 GROUP BY 子句对数据进行“嗅探”，以列出可能受影响的行。

SELECT product_group, prize, COUNT(*)
FROM   product
GROUP BY product_group, prize      -- create groups
HAVING COUNT(*) > 1;               -- count the number of rows within each group

 product_group | prize  | count
---------------+--------+-------
 Laptop        | 675    |     2

-- Count the number of groups where such a problem exists
SELECT COUNT(*) FROM
  (SELECT product_group, prize, COUNT(*)
   FROM   product
   GROUP BY product_group, prize
   HAVING COUNT(*) > 1
  ) tmp;

 count
-------
     1

但 GROUP BY 子句并不太有用，因为它无法显示除分组列和其他一些系统函数（如 COUNT()）的结果以外的列（在极少数情况下，对时间戳的排序以及 MAX(id) 会有所帮助）。问题是：我们如何识别“正确”和“错误”的行？我们需要访问行的其他列来识别它们。在最好的情况下，我们可以访问行的 ID。

为了查看这些细节，我们将 GROUP BY 子句替换为窗口函数（这不是唯一可能的解决方案）。以下 SQL 命令使用相同的两个列 product_group 和 prize 进行分组。它还使用类似的方式来计算受影响的行数。主要区别在于我们看到了并可以访问所有行的所有列。

SELECT product.*,
       COUNT(*) OVER (PARTITION BY product_group, prize ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as cnt
FROM   product;

 id |      name       | product_group | prize  | cnt
----+-----------------+---------------+--------+-----
  8 | Workstation ONE | Desktop       | 499    |   1
  1 | Big Fat One     | Desktop       | 545    |   1
  5 | Solid           | Desktop       | 565    |   1
 11 | Tripple-A       | Desktop       | 580    |   1
 10 | Rusty           | Laptop        | 390    |   1
  3 | Angle           | Laptop        | 398    |   1
  9 | Air             | Laptop        | 450    |   1
  7 | WhiteHorse      | Laptop        | 675    |   2
  2 | SmartAndElegant | Laptop        | 675    |   2
 13 | AllDay Basic    | Smartphone    |  75    |   1
  4 | Wizzard 7       | Smartphone    | 380    |   1
 12 | Oxygen 8        | Smartphone    | 450    |   1
  6 | AllRounder      | Smartphone    | 535    |   1

这个 SELECT 提供了我们所需的一切：最后一列 cnt 统计了唯一的 product_group/prize 组合的数量。而 id 列让我们可以访问每一行。

在下一步中，我们将扩展此查询并将其转换为子查询（窗口函数不能在 WHERE 子句中使用，只能使用它们的结果）。计数器为“1”的行与我们无关，我们将它们从进一步处理中删除，以确定性方式对剩余的行进行排序，并计算一个用于每个组中位置的附加列。

SELECT tmp.*
FROM   (
  SELECT product.*,
         COUNT(*)     OVER (PARTITION BY product_group, prize ORDER BY id
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as cnt,
         ROW_NUMBER() OVER (PARTITION BY product_group, prize ORDER BY id) as position_within_group
  FROM   product
  ) tmp
WHERE  tmp.cnt > 1;

 id |      name       | product_group | prize  | cnt | position_within_group
----+-----------------+---------------+--------+-----+-----------------------
  2 | SmartAndElegant | Laptop        | 675    |   2 |                     1
  7 | WhiteHorse      | Laptop        | 675    |   2 |                     2

到目前为止，我们识别问题行的算法很简单、清晰，对所有用例都是一样的：使用 PARTITION BY 子句创建感兴趣列上的分组，计算每个组中行数，并删除计数器为“1”的组。但现在我们必须决定哪一行应该保留，哪一行应该删除（或修改）？答案高度依赖于业务逻辑、数据添加到表的方式、客户的期望等等。所以你必须自己做出决定。

在本页中，我们选择一个简单的解决方案：ID 最小的行将保留；所有其他行将被删除。出于测试目的，我们将检索我们打算删除的行，即位置大于 1 的行。

SELECT tmp.*
FROM  
  (SELECT product.*,
          COUNT(*)     OVER (PARTITION BY product_group, prize ORDER BY id
                             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as cnt,
          ROW_NUMBER() OVER (PARTITION BY product_group, prize ORDER BY id) as position_within_group
   FROM   product
  ) tmp
WHERE  tmp.cnt > 1
AND    tmp.position_within_group > 1;

 id |    name    | product_group | prize  | cnt | position_within_group
----+------------+---------------+--------+-----+-----------------------
  7 | WhiteHorse | Laptop        | 675    |   2 |                     2

-- or retrieve the rows which will survive:
...
AND    tmp.position_within_group = 1;

删除行

如果这就是你期望的，你可以在最后一步删除这些行。将上面的命令简化为只检索 ID，将其转换为子查询，并使用其结果作为 DELETE 命令的输入。

BEGIN TRANSACTION;

DELETE
FROM   PRODUCT
WHERE  id IN
  (SELECT tmp.id
   FROM
     (SELECT product.*,
             COUNT(*)     OVER (PARTITION BY product_group, prize ORDER BY id
                                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as cnt,
             ROW_NUMBER() OVER (PARTITION BY product_group, prize ORDER BY id) as position_within_group
      FROM   product
     ) tmp
   WHERE  tmp.cnt > 1
   AND    tmp.position_within_group > 1
  );

COMMIT;       -- or: ROLLBACK;

检索每个组的前 N 行

结构化查询语言
消除重复数据

SQLSTATE