گاهی اوقات اتفاق می افتد که نمونه های
تکراری در یک جدول ذخیره شده باشند که در این حالت نیاز به حذف
آنها
وجود خواهد داشت
(بطور مثال داده های چند جدول را با یکدیگر ترکیب کرده باشیم). امروز شما را با روشهای مجموعه گرایی (set-based)
آشنا خواهم کرد که این مشکل را به شکل بسیار موثر و زیبا حل خواهند کرد.
سناریوی اول
زمانی که جدول دارای یک ستون از نوع
Identity بوده و این مقدار سطرها را از هم تفکیک می کند.
یعنی سطرها در یک ستون به صورت تکراری نیستند و بخش داده ها مقادیر تکراری دارد.
البته گاها نیاز می شود که از چند نمونه داده تنها یک نمونه باقی بماند، بطور مثال
می خواهیم فقط آخرین سفارش هر مشتری را نگهداری کنیم و مابقی سفارشات که قبلا
صورت گرفته را حذف کنیم در این حالت از این سناریو می شود استفاده کرد.
روش های مورد نظر:
-
Traditional DELETE syntax
-
DELETE based on JOIN: OUTER JOIN
-
DELETE based on JOIN: Self JOIN
-
ROW_NUMBER & Derived Table
فرض کنید جدولی داریم که شامل یک ستون
برای ذخیره ی داده ها و یک ستون به عنوان شمارنده می باشد. در صورتی که ستون
شمارنده، در جدول وجود نداشته باشد ناچاریم از تابع IDENTITY INTO
برای دادن شماره به سطرهای جدول استفاده کنیم که
به این تکنیک قبلا پرداخته
ام.
DECLARE @table TABLE
(id INT IDENTITY(1, 1),
data VARCHAR(20));
INSERT INTO @table
VALUES ('not duplicate row'),
('duplicate row'),
('duplicate row'),
('duplicate row'),
('second duplicate row'),
('second duplicate row');
عبارت DELETE استاندارد و سنتی
DELETE FROM t
FROM @table t
WHERE NOT EXISTS
(SELECT 1
FROM @table
WHERE data = t.data
HAVING MAX(id) = t.id);
--Correlated Version
DELETE FROM t
FROM @table AS t
WHERE id <
(SELECT MAX(id)
FROM @table
WHERE data = t.data);
--NOT IN() : Simple Version
DELETE FROM t
FROM @table AS t
WHERE id NOT IN
(SELECT MAX(id)
FROM @table
GROUP BY data);
این Query یک
روش استاندارد می باشد که در تمام حالات قابل استفاده خواهد بود. شرط شده است که
سطرهایی که ID اشان در گروه خودشان بیشترین نباشد حذف
خواهند شد (با کمک منطق correlated subquery)
در مثال فوق هم مثالی با NOT EXISTS آورده ام و هم NOT IN که مثال NOT IN ساده تر می باشد.
DELETE بر اساس JOIN
DELETE FROM @table
FROM @table o
LEFT OUTER JOIN ( SELECT [id] = MAX(id)
FROM @table
GROUP BY data
) g ON o.id = g.id
WHERE g.id IS NULL;
--OUTER APPLY : Correlated Version
DELETE FROM t
FROM @table AS t
OUTER APPLY (SELECT 1
FROM @table
WHERE data = t.data
HAVING MAX(id) = t.id) d(i)
WHERE d.i IS NULL;
عبارت SELECT
دارای یک syntax ای است که بر اساس JOIN
می باشد. البته نه تنها
از INNER JOIN بلکه از عملگرهای جدولی
دیگر حتی APPLY نیز پشتیبانی می کند. در query
اول همانطور که مشاهده می شود ابتدا بر اساس ستون data که
نیاز به فقط یک نمونه ی آن است گروه بندی شده سپس MAX ستون
ID را بدست می آوریم (داخل عبارت جدولی) سپس با جدول اتصال
خارجی می دهیم. در واقع ما با کمک LEFT JOIN سطرهایی را که
Match نشده اند را حذف می کنیم. سطرهایی که match
نشده اند به این معنا خواهند بود که ID اشان در گروهشان
بزرگترین نبوده اند و نیازی به وجود آنها نبوده است.
OUTER APPLY را می توانیم به جای LEFT OUTER JOIN
مورد استفاده قرار دهیم که وظیفه ی هر دوی این عملگر ها تفاضل دو مجموعه داده می
باشد. (که البته در مقاله ای به طور کامل به آن پرداخته ام)
SELF JOIN
DELETE FROM t1
FROM @table t1
INNER JOIN @table t2
ON t1.data = t2.data
AND t1.id < t2.id;
اگر دو جدول را با یکدیگر اتصال دهیم
با این شرط که داده ها برابر بوده ولی مقدار ID جدول سمت چپ
کوچکر از مقدار متناظر آن در جدول راست باشد، تمام سطرها به غیر از سطری که شامل
بزرگترین ID در گروه خودش هست انتخاب خواهند شد، و سپس حذف
می شوند.
ROW_NUMBER
DELETE D
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY data
ORDER BY id)
FROM @table) D(recID)
WHERE recID > 1;
--Delete based on CTE
;WITH CTE(recID) AS
(SELECT ROW_NUMBER() OVER(PARTITION BY data
ORDER BY id)
FROM @table)
DELETE FROM CTE
WHERE recID > 1;
در صورتی که سطرهای جدول را بر اساس
مقادیر ستون Data بخش بندی کنیم (Partitioning)
و به هر سطر شماره ای که از 1 آغاز می شود بدهیم، می توانیم سطرهایی که شماره ی
آنها بیش از 1 هست را انتخاب و حذف کنیم. شماره دادن به سطرهای جدول را می توانیم
به عهده ی تابع ROW_NUMBER بسپاریم که واقعا کار را ساده و
زیبا خواهد کرد. در اینجا حذف داده ها را هم بر اساس Derived Table
و هم بر اساس CTE به نمایش در آورده ام.
سناریوی دوم
هیچ ستونی برای تفکیک سطرهای تکراری
وجود ندارد.
و همچنین هیچ کلید یکتا و اصلی جدول ندارد.
در این روش، ابتدا نمونه های تکراری را
با کمک دو ماده ی GROUP BY و HAVING
بدست آورده و داخل جدول موقت می کنیم. سپس جدول اصلی را با جدول موقت اتصال داده با
این کار تمام سطرهای تکراری حاصل می شوند و سپس حذف می کنیم. و در مرحله ی بعد یک
سطر از هر نمونه ی تکراری در جدول اصلی درج می کنیم.
DECLARE @table TABLE(data VARCHAR(20))
INSERT INTO @table VALUES ('not duplicate row')
INSERT INTO @table VALUES ('duplicate row')
INSERT INTO @table VALUES ('duplicate row')
INSERT INTO @table VALUES ('second duplicate row')
INSERT INTO @table VALUES ('second duplicate row')
--Identifying duplicates and insert into #duplicates table
SELECT data
INTO #duplicates
FROM @table
GROUP BY data
HAVING COUNT(*) > 1
-- delete all rows that are duplicated
DELETE FROM @table
FROM @table o INNER JOIN #duplicates d
ON d.data = o.data
-- insert one row for every duplicate set
INSERT INTO @table(data)
SELECT data
FROM #duplicates
در اینجا مقادیر تکراری را با کمک کلید
واژه ی DISTINCT خلاصه سازی می کنیم و نتیجه را در یک جدول
دیگر درج می کنیم. و جدول قبلی را حذف کرده و جدول جدید را به جدول قدیمی rename می کنیم.
CREATE TABLE duplicateTable3(data VARCHAR(20))
INSERT INTO duplicateTable3 VALUES ('not duplicate row')
INSERT INTO duplicateTable3 VALUES ('duplicate row')
INSERT INTO duplicateTable3 VALUES ('duplicate row')
INSERT INTO duplicateTable3 VALUES ('second duplicate row')
INSERT INTO duplicateTable3 VALUES ('second duplicate row')
SELECT DISTINCT data
INTO tempTable
FROM duplicateTable3
GO
TRUNCATE TABLE duplicateTable3
DROP TABLE duplicateTable3
exec sp_rename 'tempTable', 'duplicateTable3'
و در خاتمه از دوست خوبم آقای صحراگرد
به خاطر معرفی سایتی برای تست کدهای T-SQL و رنگی کردن
آنها، تشکر و قدردانی می کنم. و تشکر از اینکه وقتتون را صرف مطالعه ی این مقاله
کرده اید. اگر ایده و روش خلاقانه ای برای حل این مساله بدست آورده اید می توانید
آن را با بنده در میان گذاشته و در صورت مناسب بودن آن را در همین مقاله قرار خواهم
داد.
مراجع:
Simple-talk
30sharp.com