[T-SQL] 配合 EXISTS 刪除資料庫重複資料


資料庫裡面有超過兩億筆的資料,每次跑 DELETE 一筆資料,就要跑好久,所以想說把問題資料一次刪除。

流程步驟大概是這樣:

  1. 匯出重複資料到暫存資料庫
  2. 刪除重複資料
  3. 從暫存資料庫匯入不重複資料 (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 測試 EXISTS
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<=’日期’ 的條件,避免有即時備份重複資料時,有即時最新的重複資料沒備份到,而執行刪除重複資料時,卻被刪除到。

Categories: 工作點滴, 技術分享 | 標籤: | 發表留言

文章分頁導航

發表留言

在 WordPress.com 建立免費網站或網誌.