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

مقدمه

هنگام طراحی 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

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

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

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

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

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

روش دوم: استفاده از 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] است.

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

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

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

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

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

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

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

نتیجه‌گیری

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