資料庫裡面有超過兩億筆的資料,每次跑 DELETE 一筆資料,就要跑好久,所以想說把問題資料一次刪除。
流程步驟大概是這樣:
- 匯出重複資料到暫存資料庫
- 刪除重複資料
- 從暫存資料庫匯入不重複資料 (SELECT DISTINCT)
所以這篇要談的是上面步驟 2。
測試語法前,先產生一個測試資料表:
SN 是我的資料項目代號,如果不容易理解,可以換個想法,當作大學全校學生的考試成績,SN 是科目代號,DataTime 是考試時間,DataValue 是考試分數,Quality 是學生代號,大學生每個人選修科目都不同,所以不能直接做橫向列表來紀錄成績。
所以這個資料表沒有 PK ,只有 SN / DataValue / Quality 有可重複的索引值。
而在這個超過兩億筆的資料裡面,目前檢查有 29k 筆資料重複,如果逐筆刪除,會消耗大量時間,所以測試我不熟的 EXISTS 來刪除。
由於我的資料項目代號並不重複,我就省掉 Quality 的欄位,所以撈出重複資料的語法很單純:
SELECT SN, DataTime
FROM (
SELECT SN, DataTime, COUNT(SN) AS nCount
FROM Data_Temp
GROUP BY SN, DataTime
) AS Data_Limit
WHERE nCount > 1
我一直以為搭配 EXISTS 是類似子查詢,把上面語法嵌入即可,不過實際上不是,需要把 WHERE 條件合併,測試時,先用 SELECT 測:
SELECT *
FROM Data_Temp
WHERE EXISTS (
SELECT SN, DataTime
FROM (
SELECT SN, DataTime, COUNT(SN) AS nCount
FROM Data_Temp
GROUP BY SN, DataTime
) AS Data_Limit
WHERE nCount > 1 AND Data_Temp.SN=Data_Limit.SN AND Data_Temp.DataTime=Data_Limit.DataTime
)
;
如上面語法,其中,下底線加粗體字的條件,基本上就是 JOIN 在 ON 後面的條件,要一起組進去,組好測試無誤後,把 SELECT 改成 DELETE 即可。
最後測試完成的語法:
DELETE
FROM Data_Temp
WHERE EXISTS (
SELECT SN, DataTime
FROM (
SELECT SN, DataTime, COUNT(SN) AS nCount
FROM Data_Temp
GROUP BY SN, DataTime
) AS Data_Limit
WHERE nCount > 1 AND Data_Temp.SN=Data_Limit.SN AND Data_Temp.DataTime=Data_Limit.DataTime
)
;
實際在用的時候,要在 GROUP BY 之前再加入一個 WHERE DataTime<=’日期’ 的條件,避免有即時備份重複資料時,有即時最新的重複資料沒備份到,而執行刪除重複資料時,卻被刪除到。