結構化查詢語言/消除重複資料

挑戰

有時人們會發現他們的資料庫中存在損壞或不需要的資料，例如：他們在建立表時忘記建立主鍵，並且在主鍵列中插入了相同的值，或者他們發現一個或多個列的組合中的值不是唯一的 - 違反了業務規則。這種情況通常在他們發出 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