چگونه ردیف‌های تکراری در یک جدول را حذف کنیم؟

مقدمه

هنگام طراحی object ها در SQL Server باید بهترین روش‌ها را انتخاب کنیم. به عنوان مثال، یک جدول باید دارای کلیدهای اصلی، ستون‌های هویت، ایندکس‌های clustered ,non clustered محدودیت‎‌هایی برای اطمینان از یکپارچگی و عملکرد داده‌‎ها باشد. حتی اگر بهترین روش‌ها را دنبال کنیم، ممکن است با مشکلاتی مانند ردیف‌های تکراری مواجه شویم. می‌خواهیم ردیف‌های تکراری را قبل از درج در جداول حذف کنیم.

فرض کنید جدول SQL شما حاوی ردیف‌های تکراری است و می‌خواهید آن ردیف‌های تکراری را حذف کنید. خیلی وقت‌ها با این مسائل مواجه می‌شویم. بهترین روش استفاده از کلیدهای مربوطه محدودیت‌ها برای از بین بردن امکان تکرار ردیف‌ها است. اما اگر ردیف‌های تکراری از قبل در جدول وجود داشته باشد، برای پاکسازی داده‌های تکراری، باید روش‌های خاصی را دنبال کنیم. این مقاله روش‌های مختلف برای حذف داده‌های تکراری از جدول SQL را بررسی می‌کند.

قدم اول:

ابتدا با دستور use دیتابیس master را فراخوانی می‌کنیم.

--فراخوانی دیتابیس 
use master
go
--create employee table
create table employeetable(
ID int identity(1,1),
firstname varchar(100),
lastname varchar(100),
country varchar(100))
go
--insert values to employee table
insert into employeetable(firstname,lastname,country)
values('Raj','gupta','india'),
('Raj','gupta','india'),
('ali','ahmai','canada'),
('naser','amini','tehran'),
('James','Barry','UK'),
('James','Barry','UK')
--نمایش رکوردهای جدول
select * from employeetable

در جدول فوق، تعدادی رکورد تکراری داریم می‌خواهیم رکوردهای تکراری را حذف کنیم.

روش اول: استفاده از عبارت SQL GROUP BY

راه حل ۱: استفاده از group by ,having ,clause برای حذف ردیف‌های تکراری در یک جدول در SQL Server

در این روش از عبارت SQL GROUP BY برای شناسایی ردیف‌های تکراری استفاده می‌کنیم. عبارت Group By داده‌ها را طبق ستون‌های تعریف شده، گروه‌بندی می‌کند و می‌توانیم از تابع COUNT برای بررسی وقوع یک ردیف استفاده کنیم.

به عنوان مثال، کوئری زیر را اجرا کنید. آن رکوردها را در جدول Employee بزرگ‌تر از ۱ مشاهده می‌کنیم.

select 
firstname,
lastname,
country,
COUNT(*) as 'count of rows'
from employeetable
group by 
firstname,lastname,country
having COUNT(*)>1

همانطور که در جدول فوق مشاهده می‌کنیم، ردیف اول دیتا ۲ بار تکرار شده است و ردیف دوم دیتا ۲ بار تکرار شده است.

روش دوم: استفاده از subquery

ما باید یک ردیف را نگه داریم و ردیف‌های تکراری را حذف کنیم. ما باید فقط ردیف‌های تکراری را از جدول حذف کنیم. به عنوان مثال، EmpID 1 دو بار در جدول ظاهر می‌شود. ما می‌خواهیم فقط یک مورد از آن را حذف کنیم.

select * from employeetable
where ID not in 
(Select MAX(ID)
from employeetable
group by firstname,lastname,country
);

خروجی کوئری فوق مطابق تصویر زیر است:

در تصویر زیر می‌بینیم که عبارت Select بالا، Max id هرردیف تکراری را حذف می‌کند و فقط حداقل مقدار ID را دریافت می‌کنیم.

برای حذف این داده‌ها، مطابق عبارت زیر، عبارت Select را با عبارت delete در SQL Server جایگزین کنید.

delete from employeetable
where ID not in (select MAX(ID)'max of rows' from employeetable
group by firstname,lastname,country
)
select * from employeetable

روش سوم: حذف داده‌های تکراری با استفاده از دستور CTE

ما می‌توانیم داده‌های تکراری با استفاده از دستور CTE حذف کنیم.

WITH CTE ([firstname],[lastname],[country],duplicatecount)
as (select firstname
,lastname
,country,
 ROW_NUMBER() over (PARTITION by firstname,lastname,country order by id) as duplicatecount   from employeetable
)
delete from CTE where duplicatecount>1

روش چهارم: استفاده از rank function

ما می‌توانیم از تابع SQL RANK برای حذف ردیف‌های تکراری نیز استفاده کنیم. تابع SQL RANK برای هرردیف بدون در نظر گرفتن ردیف تکراری، شناسه ردیف منحصربه‌فرد می‌دهد.

در کوئری زیر از تابع RANK با عبارت PARTITION BY استفاده می‌کنیم. عبارت PARTITION BY زیرمجموعه‌ای از داده‌ها را برای ستون‌های مشخص شده آماده می‌کند و برای آن پارتیشن رتبه می‌دهد. برای این منظور کوئری زیر را می‌نویسیم:

select 
e.ID,
e.firstname,
e.lastname,
e.country,
t.rankid
from [employeetable] e
inner join
(select *,
RANK() over (PARTITION by firstname,lastname,country
order by id)  rankid
from [employeetable]) t 
on e.ID=t.ID;

خروجی کوئری فوق مطابق تصویر زیر است:

در تصویر بالا، باید توجه داشته باشید ردیفی را که دارای رتبه بزرگ‌تر از یک است، حذف کنیم. برای این کار از پرس‌وجو زیر استفاده کنید.

select 
e.ID,
e.firstname,
e.lastname,
e.country,
t.rankid
from [employeetable] e
inner join
(select *,
RANK() over (PARTITION by firstname,lastname,country
order by id)  rankid
from [employeetable]) t 
on e.ID=t.ID
where t.rankid>1

روش پنجم: استفاده از پکیج SSIS

برای این منظور، ابتدا یک پکیج جدید در محیط integration service ایجاد می‌کنیم.

در SQL Server Data Tools، یک بسته Integration جدید ایجاد کنید. در بسته جدید، یک OLE DB Source اضافه کنید.

ویرایشگر Source OLE DB را باز کنید و اتصال source کنید و جدول مقصد را انتخاب کنید.

یک عملگر مرتب‌سازی را از جعبه ابزار SSIS برای عملیات حذف SQL اضافه کنید و آن را با داده‌های منبع بپیوندید.

برای پیکربندی عملگر Sort، روی آن دوبار کلیک کرده و ستون‌هایی که حاوی مقادیر تکراری هستند را انتخاب کنید. در مورد ما، مقدار تکراری در ستون‌های [FirstName]، [LastName]، [Country] است.

همچنین می‌توانیم از انواع مرتب‌سازی صعودی یا نزولی برای ستون‌ها استفاده کنیم. روش مرتب‌سازی پیش‌فرض، صعودی است. در ترتیب مرتب‌سازی می‌توانیم ترتیب مرتب‌سازی ستون را انتخاب کنیم. ترتیب مرتب‌سازی ۱ ستونی را نشان می‌دهد که ابتدا مرتب می‌شود.

در سمت چپ پایین، به کادر انتخاب Remove rows with Duplicate مرتب‌سازی توجه کنید.

وظیفه حذف ردیف‌های تکراری را برای ما از داده‌های source انجام می‌دهد.

می‌توانیم مقصدهای SQL Server را برای ذخیره داده‌ها پس از حذف ردیف‌های تکراری اضافه کنیم. ما فقط می‌خواهیم بررسی کنیم که آیا عملگر مرتب‌سازی کار را برای ما انجام می‌دهد یا خیر؟

همانطور که در زیر نشان داده شده است، یک تبدیل SQL Multicast را از جعبه ابزار SSIS اضافه کنید.

برای مشاهده داده‌های متمایز، برروی رابط بین Sort و Multicast کلیک راست کنید. روی Enable Data Viewer کلیک کنید.

پکیج را برای انجام عملیات delete SQL اجرا کنید. این نمایشگر داده خروجی مرتب‌سازی را در وظیفه جریان داده باز می‌کند. در این نمایشگر داده، پس از حذف مقادیر تکراری، می‌توانید داده‌های متمایز را مشاهده کنید.

نتیجه‌گیری

در این مقاله، فرآیند حذف ردیف‌های تکراری SQL را بااستفاده از روش‌های مختلف مانند بسته T-SQL، CTE و SSIS بررسی کردیم. می‌توانید از روشی استفاده کنید که در آن احساس راحتی می‌کنید. با این حال، من پیشنهاد می‌کنم این رویه‌ها و بسته‌ها را مستقیماً روی داده‌های Production پیاده‌سازی نکنید.

برای بدست آوردن اطلاعات بیش‌تر در مورد دیگر دستورات SQL ، به مقاله زیر مراجعه کنید.